try:CREATE PROCEDURE SP_Data_test1
@dateto smalldatetime=null,
@datefrom smalldatetime=null,
@partNo varchar(20)as
DECLARE @str1 varchar(1000)
DECLARE @str2 nvarchar(500)if @partNo is not null
BEGIN
select @str1= @str1 + ' and (inventory_item_id like ''%'+@partNo+'%'') '
END
ELSE
BEGIN
select @str1= @str1 + ' '
ENDselect @str2= N'SELECT inventory_item_id, SUM(in_qty) AS quantity FROM V_sum_In
WHERE (change_date >= @datefrom ) AND (change_date <= @dateto ) ' +@str1
select @str2=@str2 + N' group by inventory_item_id'EXEC sp_executesql @str2,N'@dateto smalldatetime,@datefrom smalldatetime',@dateto,@datefrom
GO
@dateto smalldatetime=null,
@datefrom smalldatetime=null,
@partNo varchar(20)as
DECLARE @str1 varchar(1000)
DECLARE @str2 nvarchar(500)if @partNo is not null
BEGIN
select @str1= @str1 + ' and (inventory_item_id like ''%'+@partNo+'%'') '
END
ELSE
BEGIN
select @str1= @str1 + ' '
ENDselect @str2= N'SELECT inventory_item_id, SUM(in_qty) AS quantity FROM V_sum_In
WHERE (change_date >= @datefrom ) AND (change_date <= @dateto ) ' +@str1
select @str2=@str2 + N' group by inventory_item_id'EXEC sp_executesql @str2,N'@dateto smalldatetime,@datefrom smalldatetime',@dateto,@datefrom
GO
CREATE PROCEDURE SP_Data_test1
@dateto smalldatetime=null,
@datefrom smalldatetime=null,
@partNo varchar(20)as
DECLARE @str1 varchar(1000)
DECLARE @str2 nvarchar(500)if @partNo !=""
SELECT inventory_item_id, SUM(in_qty) AS quantity FROM V_sum_In
WHERE (change_date >= @datefrom ) AND (change_date <= @dateto ) and
inventory_item_id=@partNo group by inventory_item_id
ELSE
SELECT inventory_item_id, SUM(in_qty) AS quantity FROM V_sum_In
WHERE (change_date >= @datefrom ) AND (change_date <= @dateto )
group by inventory_item_id
GO