存储过程

　一、使用 sp_executesql 系统存储过程执行 Unicode 字符串
　　1、直接组合 SQL 语句执行
　　CREATE PROCEDURE p_Test1
　　@TableName varchar(20)
　　AS
　　declare @SQLString nvarchar(200)
　　set @SQLString = N'select * from ' + @TableName
　　EXECUTE sp_executesql @SQLString
　　2、SQL 语句里包含嵌入参数
　　CREATE PROCEDURE p_Test2
　　@TableName varchar(20),
　　@UserID int,
　　@UserName varchar(50)
　　AS
　　declare @SQLString nvarchar(200)
　　
　　set @SQLString = N'select * from ' +
　　@TableName +
　　N' where UserID=@UserID or UserName=@UserName'
　　
　　EXECUTE sp_executesql @SQLString,
　　N'@UserID int, @UserName varchar(50)',
　　@UserID, @UserName
　　
　　这也是 Microsoft SQL Server 的推荐做法。
　　
　　二、使用EXECUTE语句执行字符串
　　CREATE PROCEDURE p_Test3
　　@TableName varchar(20)
　　AS
　　declare @SQLString nvarchar(200)
　　set @SQLString = N'select * from ' + @TableName
　　EXEC(@SQLString)
　　
　　以上两种方法支持在运行时生成 Transact-SQL 脚本、存储过程和触发器中的SQL语句。有个遗憾之处则是包含 sp_executesql 或 EXECUTE 语句的批处理不能访问执行的字符串中定义的变量或局部游标，例如，这两种方法就没法用来做传统的 ADO 分页。
 

        三、执行存储过程，获得return值

           declare @num int
           exec @num=eb_productType_Exists 'asdf',12
           select @num

        四、存储过程参数

             declare @UserId_tmp int
             execute Users_Insert @UserType='2',@UserName='xxw',@UserPassWord='1232',@UserEmail='[e-mail]',@UserId=@UserId_tmp output
              print @UserId_tmp

判断插入值是否重复：

事务和存储过程优化
CREATE PROCEDURE  IR_rb_User_UpdateUser
(
    @UserID        int,
    @Name    nvarchar(50),
    @Email           nvarchar(100),
    @Password    nvarchar(20),
    @RoleID        int,
    @iSuccess    int output
)
AS

set @iSuccess = -1    --判断是否成功有错误应为插入重复键

IF EXISTS (SELECT Email  FROM rb_Users WHERE Email = @Email and UserID<>@UserID)
BEGIN
  set @iSuccess = -2
  return
END

BEGIN TRAN

    UPDATE    rb_Users  SET    Name   = @Name,    Email    = @Email,    Password = @Password  WHERE    UserID   = @UserID

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN
        return
    END
    set @iSuccess = -3

    update rb_UserRoles set RoleID=@RoleID  where UserID=@UserID

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN
        return
    END

COMMIT TRAN

set @iSuccess = 1
GO

条件查询语句：

条件查询直接放在SQL中判断
CREATE PROCEDURE IR_SysAuthorization_GetAllSysAuthorization
(
    @Type int,
    @UserID varchar(20)
)
 AS

declare     @strsql    varchar(1500)        --临时sql语句
declare     @strwhere    varchar(1000)        --临时sql的条件语句

set @strsql = ''
set @strsql = @strsql + 'select UserID,Role,(select typename from SysAuthorizationType where type=t.type) as typeTmp, '
set @strsql = @strsql + '(CASE WHEN State = 1 THEN ' + '''有效''' + ' ELSE ' + '''无效''' + ' END) AS StateTmp '
set @strsql = @strsql + 'FROM SysAuthorization t '

set @strwhere = ''


IF  @UserID <> '' 
BEGIN
    set @strwhere = @strwhere + ' and (UserID LIKE ' + '''' + '%' + @UserID + '%' + '''' + ')'
END

IF  @Type >0
BEGIN
    set @strwhere = @strwhere + ' and Type =  ' + CAST(@Type AS varchar(12))
END

if @strwhere <>''
BEGIN
    set @strsql = @strsql + ' where ' + right(@strwhere, len(@strwhere)-4)
END

--按用户名排序
set  @strsql = @strsql + ' ORDER BY Type,UserID'

exec (@strsql)
GO

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[addCustomer]
   @CustomerID  nchar(5),
   @CompanyName nvarchar(40),
   @ContactName nvarchar(30),
   @ContactTitle nvarchar(30),
   @Address nvarchar(60),
   @City nvarchar(15),
   @Region nvarchar(15),
   @PostalCode nvarchar(10),
   @Country nvarchar(15),
   @Phone nvarchar(24),
   @Fax nvarchar(24)
AS
 /* SET NOCOUNT ON */
 insert into customers (CustomerId,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax)values(''+@CustomerId+'',''+@CompanyName+'',''+@ContactName+'',''+@ContactTitle+'',''+@Address+'',''+@City+'',''+@Region+'',''+@PostalCode+'',''+@Country+'',''+@Phone+'',''+@Fax)
 RETURN

Create PROCEDURE [dbo].[delCustomer]
 @CustomerID nchar(15)
AS
 delete from customers where  customerId =@CustomerId
 RETURN

Create PROCEDURE [dbo].[updateCustomer]
   @CustomerID  nchar(5),
   @CompanyName nvarchar(40),
   @ContactName nvarchar(30),
   @ContactTitle nvarchar(30),
   @Address nvarchar(60),
   @City nvarchar(15),
   @Region nvarchar(15),
   @PostalCode nvarchar(10),
   @Country nvarchar(15),
   @Phone nvarchar(24),
   @Fax nvarchar(24)
AS
 update customer set CompanyName=''+@CompanyName+'',ContactName=''+@ContactName+'',ContactTitle=''+@ContactTitle+'',Address=''+@Address+'', City= ''+@City+'',Region=''+@Region+'',PostalCode=''+@PostalCode+'',Country=''+@Country+'',Phone=''+@Phone+'',Fax=''+@Fax+'' where CustomerID=''+@CustomerID+''
 RETURN

Create PROCEDURE [dbo].[getCustomerList]
 @CustomerID nVARchar(15)='',
 @CompanyName nvarchar(40) ='',
 @ContactName nvarchar(30) =''
AS
 /* SET NOCOUNT ON */
 declare @sqlStr nvarchar(400)
 set @sqlStr=N'select * from customers where 1=1 '
 IF @CustomerID <> ''
  BEGIN
  set  @sqlStr=@sqlStr+ ' and CustomerId LIKE ''%' + @CustomerID + '%'''
  END
 IF @CompanyName <> ''
  BEGIN
  set  @sqlStr=@sqlStr+ ' and CompanyName like ''%' + @CompanyName + '%'''
  END
 IF @ContactName <> ''
  BEGIN
  set  @sqlStr=@sqlStr+ ' and ContactName like ''%' + @ContactName + '%'''
  END
 --PRINT @sqlStr
 EXECUTE sp_executesql @sqlStr
 --RETURN