0

I have something strange:

Declare @SQLQuery As nvarchar(Max)
Declare @maxdat date
Declare @dateColumn nvarchar(10)
Set @maxdat = GETDATE()
Set @dateColumn = 'ERDAT'
Set @SQLQuery = 'SELECT * FROM myTable WHERE @dateColumn <= @maxdat'

Execute sp_executesql @SQLQuery, N'@maxdat date, @dateColumn nvarchar(10)', @maxdat, @dateColumn

This will fail with Conversion failed when converting date and/or time from character string.

But the following will work just fine:

Set @SQLQuery = 'SELECT * FROM myTable WHERE ERDAT <= @maxdat'
Kiechlus
  • 1,167
  • 12
  • 21
  • 1
    because your comparison between two different data type so it is obvious – wiretext Jul 28 '15 at 12:19
  • 2
    You cannot parameterize column *names* - parameters are for *values* - and you don't want to supply a string *value* on the left of that comparison, you want to supply a column *name*. – Damien_The_Unbeliever Jul 28 '15 at 12:24
  • 1
    E.g. you say that `SELECT * FROM myTable WHERE ERDAT <= @maxdat` works fine but, of course, what you're attempting to do is closer to `SELECT * FROM myTable WHERE 'ERDAT' <= @maxdat` - note the extra quote marks. – Damien_The_Unbeliever Jul 28 '15 at 12:26
  • Ok, I understand. So the only possibility to achieve this if I still need this feature is `Set @SQLQuery = 'SELECT * FROM myTable WHERE ' + @dateColumn + ' <= @maxdat'`? – Kiechlus Jul 28 '15 at 12:29
  • `Declare @SQLQuery As nvarchar(Max) Declare @maxdat date Declare @dateColumn date Set @maxdat = GETDATE() Set @dateColumn = getdate() Set @SQLQuery = 'SELECT 1 WHERE @dateColumn <= @maxdat' Execute sp_executesql @SQLQuery, N'@maxdat date, @dateColumn date', @maxdat, @dateColumn` – wiretext Jul 28 '15 at 12:35
  • @tinka your comment should probably have be an answer. However it is wrong. Can't use *@datecolumn* as a parameter – t-clausen.dk Jul 28 '15 at 12:38

2 Answers2

2

Try the updated code like this. Just put the @dateColumn parameter outside the query string

Declare @SQLQuery As nvarchar(Max)
Declare @maxdat date
Declare @dateColumn nvarchar(10)
Set @maxdat = GETDATE()
Set @dateColumn = 'ERDAT'
Set @SQLQuery = 'SELECT * FROM myTable WHERE ' + @dateColumn + ' <= @maxdat'

Execute sp_executesql @SQLQuery, N'@maxdat date', @maxdat
Ugur Altay
  • 81
  • 3
  • Hi, yes that works, actually I come from that solution, but some guys explained me earlier why these string concats are not that good: http://stackoverflow.com/questions/31670688/what-is-the-advantage-of-using-parmdefinition-in-sp-executesql – Kiechlus Jul 28 '15 at 12:39
  • But since it's impossible with parameters I'll do it like that – Kiechlus Jul 28 '15 at 12:40
  • The only thing I would add here is to wrap @dateColumn with QUOTENAME to help prevent sql injection. – Sean Lange Jul 28 '15 at 13:21
1

Problem is with your dynamic SQL. the way you are doing it the variables will go as strings. You have to use the "+" symbol and do it like example below so that SQL can parse it and use 'variable value' instead of 'variable name'.

SELECT GETDATE() AS A
INTO 
#Temp

Declare @SQLQuery As nvarchar(Max)
Declare @maxdat date
Declare @dateColumn nvarchar(10)
Set @maxdat = DATEADD(Day,1,GETDATE())
Set @dateColumn = 'A'
--Set @SQLQuery = 'SELECT * FROM #Temp WHERE @dateColumn >= @maxdat'
Set @SQLQuery = 'SELECT * FROM #Temp WHERE ' + @dateColumn + ' >= '+ CAST(@maxdat AS nvarchar(10))
--Print @SQLQuery
Execute sp_executesql @SQLQuery, N'@maxdat date, @dateColumn nvarchar(10)', @maxdat, @dateColumn
Deep Kalra
  • 1,418
  • 9
  • 27