需要存储过程帮助 [英] Stored Procedure Help Required

问题描述

我想编写一个执行2个任务的存储过程
1:基于用户名获取记录的ID
2:根据步骤1中获得的ID更新记录.

我的表结构是这样的
ID名称用户名密码性别

ID是主键和自动递增

我把我的sp写成这样

Hi,
I want to write a Stored Procedure which do 2 tasks
1: Get ID of the record on base on username
2: update the record on base of the id we got in step 1

My table structure is like this
ID Name Username Password Gender

ID is primary key and autoincrement

I''ve write my sp as this

ALTER PROCEDURE [dbo].[insert_FBSignUp]
	-- Add the parameters for the stored procedure here
	@name nvarchar(200),
        @uname nvarchar(200),
        @pwd nvarchar(50),
        @gender nvarchar(10)
AS
BEGIN
	Select id from dbo.tblUsers where Username=@Email	
END

我对下一步该怎么办感到困惑，我的意思是我在哪里以及如何编写更新语句.

I''m confusing about what should i do next i mean where and how i write my update statement

推荐答案

您在列表中缺少@Email参数.声明一个变量以保存Id的值.

You are missing @Email parameter in the list. Declare a variable to hold the value of Id.

ALTER PROCEDURE [dbo].[insert_FBSignUp]
	-- Add the parameters for the stored procedure here
	@name nvarchar(200),
        @uname nvarchar(200),
        @pwd nvarchar(50),
        @gender nvarchar(10),
        @Email nvarchar(200) --missing parameter
AS
BEGIN
 
DECLARE @ID int
  Select  @ID = id from dbo.tblUsers where Username=@Email

     UPDATE dbo.tblUsers
      SET (uname = @uname,
           pwd = @pwd,
           gender = @gender)
      WHERE id = @ID

ALTER PROCEDURE [dbo].[insert_FBSignUp]
	-- Add the parameters for the stored procedure here
	@name nvarchar(200),
        @uname nvarchar(200),
        @pwd nvarchar(50),
        @gender nvarchar(10)
AS
BEGIN

        DECLARE @ID int
	SET @ID = (Select id from dbo.tblUsers where Username=@Email)

        UPDATE dbo.tblUsers
        SET (name = @name,
             uname = @uname,
             pwd = @pwd,
             gender = @gender)        
        WHERE id = @ID

END

这篇关于需要存储过程帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！