i need to use string variable in the Proc in sql server database 2005
Posted
by bassam
on Stack Overflow
See other posts from Stack Overflow
or by bassam
Published on 2010-06-13T12:26:27Z
Indexed on
2010/06/13
12:52 UTC
Read the original article
Hit count: 226
sql-server-2005
|tsql
I have this procedure
CREATE Proc [dbo].Salse_Ditail
-- Add the parameters for the stored procedure here
@Report_Form varchar(1) ,
@DateFrom datetime ,
@DateTo datetime ,
@COMPANYID varchar(3),
@All varchar(1) ,
@All1 varchar(1) ,
@All2 varchar(1) ,
@All3 varchar(1) ,
@All4 varchar(1) ,
@All5 varchar(1) ,
@Sector varchar(10),
@Report_Parameter nvarchar(max)
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @STRWhere nvarchar(max)
IF @All5=0 AND @All4=0 AND @All3=0 AND @All2=0 AND @All1=0 and @All=1
set @STRWhere= N'and Sector_id = @Sector'
if @Report_Form =1 or @Report_Form =3 or @Report_Form =4
SELECT RETURNREASONCODEID, SITE,SITE_NAME,Factory_id,Factory_Name,Sector_id,sector_name,Customer_name,
Customer_id,ITEMID,ITEMNAME,SALESMANID,SALESMAN_NAME,Net_Qty,Net_Salse,Gross_Sales,Gross_Qty,
NETWEIGHT_Gross,NETWEIGHT_salse_Gross,NETWEIGHT_NET,NETWEIGHT_salse_NET,Return_Sales,Free_Good,
CollectionAmount
FROM hal_bas_new_rep
WHERE DATAAREAID =@COMPANYID AND INVOICEDATE >= @DateFrom
AND INVOICEDATE <= @DateTo and Report_Activti = @Report_Form
if @Report_Form =2
SELECT RETURNREASONCODEID , RETURNREASONDESC, SITE , SITE_NAME , Factory_id ,
Factory_Name , Sector_id , sector_name , Customer_name , Customer_id ,
ITEMID , ITEMNAME , SALESMANID , SALESMAN_NAME , Return_Sales
FROM dbo.hal_bas_new_rep
WHERE DATAAREAID =@COMPANYID AND INVOICEDATE >= @DateFrom
AND INVOICEDATE <= @DateTo and Report_Activti = @Report_Form
and RETURNREASONCODEID in
(
SELECT Val
FROM dbo.fn_String_To_Table(@Report_Parameter,',',1)
)
/*
@STRWhere // question: how can I use the variable here?
*/
end
GO
As you see I'm constructing a condition for the WHERE clause in a variable, but I don't know how to use it.
© Stack Overflow or respective owner