Use CONVERT in dynamic SQL 1

[robertfah]

I've got some SQL that I can get to run just fine but as soon as I convert it to dynamic SQL, it errors out. I know why it errors out, I just don't know how to get around it.

Here's my SQL when it works:

SELECT LocationID, 11, 70, 'TSS-1', NULL, 'TBD-' + CONVERT(varchar, LocationID)
FROM Locations
WHERE LocationID NOT IN (Select r.LocationID 
				FROM Rooms r)
AND CustomerID = 40

Now if I make it dynamic, it fails:

DECLARE @SQL varchar(8000)

SET @SQL = '
SELECT LocationID, 11, 70, ''TSS-1'', NULL, ''TBD-' + CONVERT(varchar, LocationID) + '''
FROM Locations
WHERE LocationID NOT IN (Select r.LocationID 
				FROM Rooms r)
AND CustomerID = 40'

EXEC(@SQL)

It give me:

Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.

Which I understand, it can't find that column because it hasn't compiled the dynamic part yet....but how can I get around this? I am basically trying to concatinate text with an ID field.

Any ideas?

 

[bborissov]

DECLARE @SQL varchar(8000)

SET @SQL = '
SELECT LocationID, 11, 70, ''TSS-1'', NULL, ''TBD-'' + CONVERT(varchar, LocationID)
FROM Locations
WHERE LocationID NOT IN (Select r.LocationID
                FROM Rooms r)
AND CustomerID = 40'

EXEC(@SQL)

Do not extract CONVERT() from the whole string.



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[robertfah]

Do not extract CONVERT() from the whole string.

Ah, I knew I was missing something simple. And just for anyone else who may read this, I had to change the variable to nvarchar(MAX) and use EXEC sp_executesql to get it to run, otherwise it throws the following error:

Server: Msg 203, Level 16, State 2, Line 10
The name '
SELECT LocationID, 11, 70, 'TSS-1', NULL, 'TBD-' + CONVERT(varchar, LocationID)
FROM Locations
WHERE LocationID NOT IN (Select r.LocationID
                FROM Rooms r)
AND CustomerID = 40' is not a valid identifier.

So the new SQL looks like this:

DECLARE @SQL nvarchar(MAX)

SET @SQL = '
SELECT LocationID, 11, 70, ''TSS-1'', NULL, ''TBD-'' + CONVERT(varchar, LocationID)
FROM Locations
WHERE LocationID NOT IN (Select r.LocationID
                FROM Rooms r)
AND CustomerID = 40'

EXEC sp_executesql @SQL

Thanks again bborissov for the help!