浅解 SQL存储过程 向表插入数据

（插入数据的存储过程）

ALTER PROCEDURE [dbo].[Category_create]
 -- Add the parameters for the stored procedure here
 @name nvarchar(30),
 @explain nvarchar(50),
 @parentname nvarchar(30),
 @intReturn INT OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 declare @strid char(32)
 select @strid=cateid from category where name=@parentname
 
 insert into category(cateid,parent,[name],explain) values
 (replace(newid(),'-',''),isnull(@strid,''),@name,@explain)

 IF @@ROWCOUNT>0
  SET @intReturn=1
 ELSE
  SET @intReturn=0
END

1.写存储过程时，最好跟一个返回值，程序判断存储过程是否执行成功。如@intReturn

2.存储过程变量，最好是 类型+变量名。

3.newid()是32位字符，用newid做ID是确保唯一性

4.isnull(),isnull(@A,B)如果@A的值是空则返回B，否则返回@A

5.@@ROWCOUNT存储过程受影响的行数

（用户登录的程序过程）

 

ALTER PROCEDURE [dbo].[User_login]
 -- Add the parameters for the stored procedure here
 @nvarcharName nvarchar(40),
 @nvarcharPassword nvarchar(50),
 @intReturn int OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    SELECT 1 FROM [USER] WHERE [NAME]=@nvarcharName AND PASSWORD=@nvarcharPassword

    IF @@ROWCOUNT>0
  SET @intReturn=1
 ELSE
  SET @intReturn=0
END

（用户登录的程序传参数）

 

        public bool Bl_userLogin(string name, string pwd)
        {
            SqlParameter[] para ={
                                new SqlParameter("@nvarcharName",SqlDbType.NVarChar,40),
                                new SqlParameter("@nvarcharPassword",SqlDbType.NVarChar,50),
                                new SqlParameter("@intReturn",SqlDbType.Int){Direction=ParameterDirection.Output},//定义direction属性，表明它是返回值                   

                                };

            para[0].Value = name;
            para[1].Value = pwd;

            Bl_userlogin("User_login", para);//执行存储过程

            int i = Convert.ToInt32(para[2].Value);//取返回值
          if (i == 1)
                return true;
            else
                return false;
        }