How can I add a variable at the end of a query?

Question

I am trying to SELECT a particular record from SQL Server 2008, this is the code I am using

ALTER PROCEDURE [dbo].[usp_GetTotalSalesReport]
   (@FROM_DATE nchar(20),
    @TO_DATE nchar(20),
    @SALESMANCODE nchar(8)
   )
AS
BEGIN
   DECLARE @CONDITION TEXT

   IF (@FROM_DATE IS NULL AND @TO_DATE IS NULL AND @SALESMANCODE = 'ALL') BEGIN
    SET @CONDITION = NULL    
   END
   IF (@FROM_DATE IS NULL AND @TO_DATE IS NULL AND @SALESMANCODE != 'ALL') BEGIN
   SET @CONDITION = ' WHERE  SalesManCode= @SALESMANCODE'
   END
   IF @FROM_DATE IS NOT NULL  AND @SALESMANCODE = 'ALL' BEGIN
  SET @CONDITION = '  WHERE TransactionDateTime >= @FROM_DATE  AND TransactionDateTime <= @TO_DATE'
   END
   IF @FROM_DATE IS NOT NULL  AND @SALESMANCODE != 'ALL' BEGIN
  SET @CONDITION = '  WHERE TransactionDateTime >= @FROM_DATE  AND TransactionDateTime <= @TO_DATE AND SalesManCode = @SALESMANCODE'
   END
    IF @FROM_DATE IS NOT NULL  AND @TO_DATE IS NOT NULL AND @SALESMANCODE = 'ALL' BEGIN
  SET @CONDITION = '  WHERE TransactionDateTime >= @FROM_DATE  AND TransactionDateTime <= @TO_DATE'
   END
   IF @FROM_DATE IS NOT NULL  AND @TO_DATE IS NOT NULL AND @SALESMANCODE != 'ALL' BEGIN
   SET @CONDITION =' WHERE TransactionDateTime >= @FROM_DATE  AND TransactionDateTime <= @TO_DATE AND SalesManCode = @SALESMANCODE'
   END
   SELECT RouteName
        ,SalesManCode
        ,SalesManName
        ,CustomerCode
        ,CustomerNameEng
        ,TransactionDateTime
        ,DocumentPrefix
        ,DocumentNumber
        ,BalanceDueAmount
        ,RouteID
        ,PaymentType  FROM v_DCS_Rpt_Total_Sales @CONDITION
  END

If I try to add @CONDITION variable at the end of the query like

PaymentType FROM v_DCS_Rpt_Total_Sales @CONDITION

I am getting this error

Incorrect syntax near '@CONDITION'

What I have tried so far is:

PaymentType FROM v_DCS_Rpt_Total_Sales +" "+ @CONDITION`

but its giving me this error

Incorrect syntax near '+'.

@Vijesh Poor code, poor naming convention, not much instruction manual reading, only the comma before column is good in this code. — Tomasito, Jul 23 '13 at 22:54

score 3 · Accepted Answer · answered Jul 23 '13 at 15:07

For your code to work as it is, you'll need dynamic SQL:

DECLARE @Query NVARCHAR(MAX)
SET @Query = 'SELECT RouteName
        ,SalesManCode
        ,SalesManName
        ,CustomerCode
        ,CustomerNameEng
        ,TransactionDateTime
        ,DocumentPrefix
        ,DocumentNumber
        ,BalanceDueAmount
        ,RouteID
        ,PaymentType  FROM v_DCS_Rpt_Total_Sales '+@CONDITION
EXEC(@SQL)

Now, you really don't need to use dynamic SQL here, you could use this directly without all those IFs:

SELECT   RouteName
        ,SalesManCode
        ,SalesManName
        ,CustomerCode
        ,CustomerNameEng
        ,TransactionDateTime
        ,DocumentPrefix
        ,DocumentNumber
        ,BalanceDueAmount
        ,RouteID
        ,PaymentType
FROM v_DCS_Rpt_Total_Sales
WHERE (@SALESMANCODE = 'ALL' OR SalesManCode = @SALESMANCODE)
AND (@FROM_DATE IS NULL OR TransactionDateTime >= @FROM_DATE)
AND (@TO_DATE IS NULL OR TransactionDateTime <= @TO_DATE)

score 0 · Answer 2 · edited May 23 '17 at 12:03

You can use dynamic SQL to execute a string command with the EXECUTE statement or with sp_executesql. The entire command needs to be a string though; in your case you are just trying to tack a string onto the end of a normal command, which doesn't work.

Here's an example...

DECLARE @someBool;
SET @someBool = true;
DECLARE @query VARCHAR(MAX);
SET @query = 'SELECT * FROM table1';
SET @condition1 = ' WHERE column1 = 1';
SET @condition2 = ' WHERE column2 = 2';

IF(@someBool)
  SET @query = @query + @condition1;
ELSE
  SET @query = @query + @condition2;

EXECUTE(@query);

This example, while rudimentary, shows how you can build a command string and execute it using EXECUTE. You can also use just the sp_executesql.

See this post for more details on the difference between EXECUTE and sp_executesql.

Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

score 0 · Answer 3 · answered Jul 23 '13 at 15:12

No need of dynamic SQL in a simple case like this..

SELECT RouteName
        ,SalesManCode
        ,SalesManName
        ,CustomerCode
        ,CustomerNameEng
        ,TransactionDateTime
        ,DocumentPrefix
        ,DocumentNumber
        ,BalanceDueAmount
        ,RouteID
        ,PaymentType  
FROM v_DCS_Rpt_Total_Sales
WHERE SalesManCode = CASE WHEN @SALESMANCODE = 'ALL' THEN SalesManCode ELSE @SALESMANCODE AND
      TransactionDateTime >= ISNULL(@FROM_DATE,TransactionDateTime) AND
      TransactionDateTime <= ISNULL(@TO_DATE,TransactionDateTime)

How can I add a variable at the end of a query?

3 Answers3