一、使用 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='uwen@163.com',@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
条件查询语句:
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