i need to use string variable in the Proc in sql server database 2005

Question

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.

Answer 1

I don't think what you are doing will work. What you need to do is turn the statement that constructs the variable into an appropriate condition and add that to your WHERE clause.

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) 
        )   
    AND (NOT(@All5=0 AND @All4=0 AND @All3=0 AND @All2=0 AND @All1=0 AND @All=1)
         OR Sector_id = @Sector)

Answer 2

You can build all your query in the variable and use EXECUTE to get the result. Is orrible but work