Creating dynamic sql with sp_executesql

Question

Creating dynamic sql with sp_executesql

sqlgreenhand

SSCommitted

Points: 1639
More actions
August 29, 2010 at 5:53 pm

#223133

I would like to create a dynamic sql with sp_executesql as followed, but with errors. Please help
Do all the varchar become nvarchar ?
Thanks.
DECLARE @DatabaseName nvarchar(500)
DECLARE @TableName nvarchar(500)
DECLARE @ColumnName nvarchar(500)
DECLARE @sql nvarchar(4000)
SET @DatabaseName='Northwind'
SET @TableName='dbo.Categories'
SET @ColumnName='CategoryName'
SET @sql=''
SELECT @sql = 'USE @DatabaseName2
SELECT @ColumnName2 FROM @TableName2 '
EXEC sp_executesql @sql, N'@DatabaseName2 nvarchar(500), @ColumnName2 nvarchar(500), @TableName2 nvarchar(500)', @DatabaseName, @ColumnName, @TableName

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

sqlgreenhand (8/29/2010)
I would like to create a dynamic sql with sp_executesql as followed, but with errors. Please help
Do all the varchar become nvarchar ?
Thanks.
DECLARE @DatabaseName nvarchar(500)
DECLARE @TableName nvarchar(500)
DECLARE @ColumnName nvarchar(500)
DECLARE @sql nvarchar(4000)
SET @DatabaseName='Northwind'
SET @TableName='dbo.Categories'
SET @ColumnName='CategoryName'
SET @sql=''
SELECT @sql = 'USE @DatabaseName2
SELECT @ColumnName2 FROM @TableName2 '
EXEC sp_executesql @sql, N'@DatabaseName2 nvarchar(500), @ColumnName2 nvarchar(500), @TableName2 nvarchar(500)', @DatabaseName, @ColumnName, @TableName

You can't have database names in a variable in the USE statement, or the column/table names in variables for the select statement. So, you need to build the @sql variable in this manner instead:

SET @sql = 'USE ' + QuoteName(@DatabaseName) + ';' +

'SELECT ' + QuoteName(@ColumnName) + ' FROM ' + QuoteName(@TableName) + ';';

EXEC sp_executesql @sql

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 2

sqlgreenhand

SSCommitted

Points: 1639

August 30, 2010 at 4:40 pm

#1213988

Got it! Sir.

Thanks.

WayneS SSC Guru Points: 95460 More actions · Answer 3

sqlgreenhand (8/30/2010)
Got it! Sir.
Thanks.

No problem. Glad that I could help.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 4

This is often overlooked but for all your variables that are nvarchar it is a good practice for you to be adding the pre-pended 'N' to the hardcoded string parts being assigned to them to make them explicitly unicode. So it would look like this:

SET @sql = N'USE ' + QuoteName(@DatabaseName) + N';' +

N'SELECT ' + QuoteName(@ColumnName) + N' FROM ' + QuoteName(@TableName) + N';';

EXEC sp_executesql @sql

Since QuoteName function returns an nvarchar variable you don't need to worry about casting it to the proper type.

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 5

sqlgreenhand

SSCommitted

Points: 1639

August 31, 2010 at 4:50 pm

#1214639

Good tips.

Thanks.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Remember, QUOTENAME is limited to 128 characters ; any input more than that will produce null.. So if the names should exceed 128 chars, i would forcefully CAST/CONVERT it to the NVARCHAR of desirable length.

From BOL :

Syntax

QUOTENAME ('character_string' [ ,'quote_character' ] )

Arguments

'character_string'

Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

'quote_character'

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used

MSDN Link : QUOTENAME

Hope this helps!

~Edit : Fixed link and added IFCodes..