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

Filed under:
|

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

Related posts about sql-server-2005

Related posts about tsql