Creating dynamic sql with sp_executesql

  • 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

  • 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

  • Got it! Sir.

    Thanks.

  • 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

  • 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.

  • Good tips.

    Thanks.

  • 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..

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

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