Sql语句用变量 用exec sp_executesql 执行之

CREATE Procedure sp_Select_Selling_Test
@StkList_Id varchar(30),
@Bill_Sta tinyint
as
declare @StrSql nvarchar(250)
Set @StrSql=''
if  len(ltrim(rtrim(@StkList_Id)))>0
Begin
Set @StrSql=@StrSql + ' And StkList_Id Like''%' + @StkList_Id +'%'''
end

if  @Bill_Sta>0
Begin
Set  @StrSql=@StrSql + ' And Bill_Sta='  + rtrim(ltrim(str(@Bill_Sta) ))

end
Set  @StrSql='Select * From Stocks_Sell_Data Where StkList_Id<>''''' + @StrSql
exec sp_executesql  @StrSql
GO

//
调用
exec sp_Select_Selling_Test '050128XS-003',1

=======================================================================

CREATE PROCEDURE sp_Product_Search
@Product_Code nvarchar(20),
@Sale_Price real
 AS
declare @StrSql  nvarchar(200)
Set @StrSql=''

if len(ltrim(rtrim(@Product_Code)))>0
Begin
set @StrSql=@Strsql + ' and Product_Code Like "%' + ltrim(rtrim(@Product_Code)) +'%"'
end

if isnumeric(@Sale_Price)=1
Begin
set @StrSql=@Strsql + ' and Sale_Price<' + Cast(@Sale_Price as nvarchar(10))  //数字型要转为字符型
end

 Set @StrSql='Select * From Products Where (ProId<>0) ' + @StrSql
exec sp_executesql  @StrSql
GO

//调用

exec  sp_Product_Search 'l',22

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

//带output参数

CREATE PROCEDURE sp_Product_Search
@Product_Code nvarchar(20),
@Sale_Price real,
@aa nvarchar(200) output
 AS
declare @StrSql  nvarchar(200)
Set @StrSql=''

if len(ltrim(rtrim(@Product_Code)))>0
Begin
set @StrSql=@Strsql + ' and Product_Code Like "%' + ltrim(rtrim(@Product_Code)) +'%"'
end

if isnumeric(@Sale_Price)=1
Begin
set @StrSql=@Strsql + ' and Sale_Price<' + Cast(@Sale_Price as nvarchar(10))
end

 Set @StrSql='Select * From Products Where (ProId<>0) ' + @StrSql
set @aa = @StrSql
exec sp_executesql  @StrSql
GO

///调用

declare @aa as nvarchar(200)
exec sp_Product_Search  'l',22,@aa output
print @aa

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝