Need help on escaping characters for dynamic sql

Question

The query should return a result where table = 'alrt'
but it have an error on escaping the quotation

declare @dbname nvarchar(max)  
declare @query nvarchar(max)  
set @dbname = 'alrt'  
set @query ='SELECT OBJECT_NAME(object_id) AS DatabaseName, last_user_update,convert(nvarchar(max),last_user_seek),last_user_scan,last_user_lookup,last_system_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID ('+'''Frontoffrob'''+') AND OBJECT_ID=OBJECT_ID('+@dbname+')'
exec (@query)

Error Result

Msg 207, Level 16, State 1, Line 1 Invalid column name 'alrt'.

.
.
.
The following query is working
SELECT OBJECT_NAME(object_id) AS DatabaseName,convert(nvarchar(max),last_user_scan)as SCAN FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID ('Frontoffrob') AND OBJECT_ID=OBJECT_ID ('ALRT')

Output:

+--DatabaseName--+----Last_user_SCAN----+
|__ ALRT ________|_ Aug 30 2013 1:47PM_|

I would recommend that instead you [parameterize the variables](http://stackoverflow.com/questions/11329823/add-where-clauses-to-sql-dynamically-programmatically). This will avoid the escaping issue. — StuartLC, Aug 30 '13 at 06:00

score 1 · Accepted Answer · answered Aug 30 '13 at 05:59

In your WHERE clause use this:

'SELECT ... AND OBJECT_ID=OBJECT_ID('''+@dbname+''')'

Actually, your query should look like this:

set @query =
'SELECT 
    OBJECT_NAME(object_id) AS DatabaseName, 
    last_user_update,
    convert(nvarchar(max),last_user_seek),
    last_user_scan,
    last_user_lookup,
    last_system_update 
FROM sys.dm_db_index_usage_stats 
WHERE database_id = DB_ID (''Frontoffrob'') 
AND 
OBJECT_ID=OBJECT_ID(''' + @dbname+''')'

Need help on escaping characters for dynamic sql

1 Answers1