Variables in Dynamic SQL

Transact-SQL
1

I am new to dynamic SQL and can't figure out what I am doing wrong here. Sorry about he alignment but i am not sure how to correct this in the forum's editor. My variables in the function are causing a conversion failure. Any ideas?

--Msg 241, Level 16, State 1, Line 7
--Conversion failed when converting date and/or time from character string.

DECLARE @FROM DATETIME
DECLARE @TO DATETIME
SET @FROM = '2014/01/01'
SET @TO = '2014/10/01'

DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM (SELECT
CASE
WHEN CURR_EQUIPMENT_CODE = ''_TRSP''
THEN ''TRANSPORT''
ELSE ''HIRE''
END AS Type , LINE_AMOUNT AS InvoiceAmount,DATENAME(M,INVOICE_DATE) AS INVOICEDATE

        FROM dbo.fnTHT31Invoices (' + @FROM + ',' + @TO + ',NULL, NULL, NULL, NULL, NULL, NULL, NULL)) INV'

SELECT @SQL
EXECUTE (@SQL)

2

You're trying to "add" a datetime (@FROM, @TO) to a varchar(@sql). Try converting the datatime variables to varchars then add them to the string.

1 Like
3

Would you believe me if i told you i started going down that route, stopped myself and, instead, decided to put it to the forum? :smile:

That's sorted. Thanks very much for your time.

Nev.

4

Highlight the code block and press Control-K, or press the [</>] button, or start and end it with triple-backticks like this

    ```
    your code here
    ```
1 Like
5

Nice one Kristen, just had a little play- below.. :slight_smile:

SELECT
   CASE
     WHEN ThisField = 'THAT'
     THEN 'THE OTHER' 
     ELSE 'WHATEVER' AS SomeName
   END
6

I missed a rather important point :slight_smile: ... if you use the triple-backtick markup, rather than Control-K or the [</>] button, your SQL will be colour-coded :smile:

SELECT
   CASE
     WHEN ThisField = 'THAT'
     THEN 'THE OTHER' 
     ELSE 'WHATEVER' AS SomeName
   END

like that :slight_smile:

You can also press the Pencil ixon at the bottom of one of your own posts and edit it - if you wanted to go back and fix-up something.

1 Like
7

Thank you Kristen, sorry @nevzab for high jacking your thread

This is a test

Okay how do you do the color?

8

Thanks Kristen. It's all useful info for tarting up my posts :smile:

9

Put some SQL in there :smile:

like "Pimp my ride" eh? :smile:

1 Like