question

sql_n00b avatar image
sql_n00b asked

Issue with creating dynamic SQL statement

Hi, I'm having trouble creating a dynamic SQL statement to execute. I have to truncate tables with '_history' in the name, and of a certain size or more. For that, I've created a temp table called #ConvertedSizes and I insert table names in it. Next, I have to execute 'Truncate [table];' statement. I've managed to get that statement as output but struggling to create a dynamic SQL out of it **within the same script** and execute it. I've tried different number of single quotes, but haven't had success. Here's the script: SELECT 'TRUNCATE TABLE '+TABLE_NAME+ ';' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_history%' and TABLE_NAME in (SELECT Name from #ConvertedSizes) 1. I'll appreciate some help in generating the 'Truncate [table];' script above - possibly have a @sqlScript of varchar(500) and assign the statement to it. 2. A brief primer/rule-of-thumb on how to approach such problems using single quotes, so as to reduce trial-and-error. Thanks.
sql-server-2012dynamic-sql
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
I've seen a lot of people quote the following as the seminal work on dynamic t-sql: http://www.sommarskog.se/dynamic_sql.html
2 Likes 2 ·
sql_n00b avatar image sql_n00b commented ·
Thanks! That's a handy guide for dymanic t-sql.
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Something like this might be a good starting point: DECLARE @SQL varchar(MAX) = '' SELECT @SQL = @SQL + 'TRUNCATE TABLE ' + QUOTENAME(TABLE_NAME) + ';' -- corrected as per comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_history%' AND TABLE_NAME IN (SELECT Name from #ConvertedSizes); SELECT @SQL; --EXEC (@SQL); Comment out the last line when you're sure that the statement held in @SQL is being generated properly Code not tested for anything, including actually being right, let alone working in SQL 2012. Seriously, you're going to run untested code that you don't understand on your database?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sql_n00b avatar image sql_n00b commented ·
Hi Thomas, Thanks for your response. I tried the code, and it gave the following error message: 'QUOTE_NAME' is not a recognized built-in function name. I changed that to QUOTENAME, and it worked. Thanks!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Like I said, not tested... :-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.