i need to use string virble 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:32 UTC
Read the original article
Hit count: 212
sql-server-2005
|tsql
i have this Proc
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 i can but the virble here
*/
end GO i want to but virble put a variable under where Expression and from this function buc I have many function i want to add
if any one have answer pls send me
© Stack Overflow or respective owner