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
===================================