在存储过程中如何拼接字符串参数

示例一、
declare @strWhere varchar(1000)
set @strWhere = 'cat2.CategoryName = '''+'手机'+''' and Quantity > 10' --cat2为表名

--exec dbo.UP_GetInventoryByPage 2,3, @strWhere
print(@strWhere)

 

 

 

示例二、
declare @Sql varchar(2000)
set @Sql = 'select * from dbo.Category'
declare @wh varchar(1000)
set @wh = '1'
if @wh = '1'

--当@wh变量值为字条串时用引号''将其括起来。
 set @Sql = @Sql + ' where CategoryId =''' + @wh + ''''  --结尾是四个引号
print(@Sql)
exec(@sql)

 

--other:

declare @Sql varchar(2000)
set @Sql = 'select * from dbo.Category'
declare @wh varchar(1000)
set @wh = '1'

当--@wh 变量值为 数字时，直接用
 set @Sql = @Sql + ' where CategoryId =' + @wh
print (@Sql)

 

示例三:

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[UP_GetInventoryByPage]
@pageSize int,
@pageNumber int,
@strWhere varchar(1000) = '' --varchar(1000)= ''
AS
Declare @rowStart int
Declare @rowEnd int
Declare @strSql varchar(1000)

set @strSql = 
 'With Temp AS  --用with申明一张Temp表
 ( SELECT InventoryID,inv.ProductID,inv.Quantity,pro.ProductName,pro.Price,cat.CategoryName,cat2.categoryName as ProductType,
 ROW_NUMBER() OVER (order by InventoryID) as rowNumber
 From Inventory inv inner join Product pro
 on inv.productid = pro.productid
 inner join category cat
 on pro.categoryid = cat.categoryid
 inner join category cat2
 on cat.parentid = cat2.categoryid'

if @pageNumber > 0
Begin
if @strWhere <>''
 begin 
 SET @pageNumber = @pageNumber -1
 SET @rowStart = @pageSize * @pageNumber + 1;
 SET @rowEnd = @rowStart + @pageSize - 1 ;
 SET @strSql = @strSql +' Where '+ @strWhere + ')'+
 ' select *
 from Temp
 Where rowNumber >='+ str(@rowStart) +' and rowNumber <= '+ str(@rowEnd)
 exec(@strSql) 
 end
else
Begin
 SET @pageNumber = @pageNumber -1
 SET @rowStart = @pageSize * @pageNumber + 1;
 SET @rowEnd = @rowStart + @pageSize - 1 ;
 Set @strSql = @strSql + ' ) '+
 ' select *
 from Temp
 Where rowNumber >= '+ str(@rowStart) +' and rowNumber <= '+str(@rowEnd) --整数的参数与字符串拼接时要转化为字符串
 exec(@strSql)
END

end

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/vihone/archive/2010/01/18/1650757.html