Damon Matt Damon Matt - 4 months ago 12
SQL Question

Appending variables inside dynamic sql in sql server

I'm having trouble building a dynamic sql string to run an openquery. When I print the query string it evaluates the variable name as string instead of the actual value. Here's what I have:

Declare @tsql varchar(1000)

Declare @book_review_start as date

Declare @book_review_end as date

set @book_review_start = convert(varchar(10),DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0), 120)

set @book_review_end = convert(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), -2), 120)

Set @tsql = 'select * from openquery(authorsdb, ''select distinct ssn as bsn
from authors.dbo.nytimes where review_date between '' + @book_review_start + 'and' + '@book_review_end' + 'and review = 'annual'
and city_name = ''
and review_hrs = 0
and review_days = 0')'


That throws all kinds of conversion errors and such.

Answer

1) You need 1 or more extra ' single quote characters in 1 or more places in your statement.

2) The DATE values need to converted to string / VARCHAR since you are concatenating string to generate the dynamic SQL statement.

The following works.

Declare @tsql varchar(1000)

Declare @book_review_start as date

Declare @book_review_end as date

set @book_review_start = convert(varchar(10),DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0), 120)

set @book_review_end = convert(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), -2), 120)

Set @tsql = 'select * from openquery(authorsdb, ''select distinct ssn as bsn from authors.dbo.nytimes where review_date between ''' 
+  CONVERT(varchar(20),@book_review_start) + ''' AND ''' + CONVERT(varchar(20),@book_review_end ) + ''''
+ ' AND review = ''annual'' AND city_name = '''' AND review_hrs = 0 and review_days = 0'

PRINT @tsql

The OUTPUT of the PRINT Statement is as follows.

select * from openquery(authorsdb, 'select distinct ssn as bsn from authors.dbo.nytimes where review_date between '2016-05-01' AND '2016-06-30' AND review = 'annual' AND city_name = '' AND review_hrs = 0 and review_days = 0