Stored Procedure Parameter Causes Error

Question

I created a stored procedure that takes 3 arguments. I am having an issue with the 2nd argument ([HQ-211-STOCK-DBO]). I've been trying to figure it out for quite some time.

Here is the call to the SP:

EXEC [dbo].[spColumnsChanged] '[HQ-193-STOCK-DBO]','[HQ-211-STOCK-DBO]','arch_conv_col_diffs'

In the SP I have the following code:

CREATE PROCEDURE [dbo].[spColumnsChanged] (@sr_database nvarchar(128),@tr_database nvarchar(128),@diff_table nvarchar(128))

...

SET @DFCursor = CURSOR FAST_FORWARD FOR 
SELECT o.name table_name, col.name column_name, t.name data_type, col.max_length, col.is_nullable
 FROM @tr_database.sys.columns col
 INNER JOIN @tr_database.dbo.sysobjects o on o.id = col.object_id
 INNER JOIN @tr_database.sys.types t ON t.user_type_id = col.user_type_id
 WHERE o.xtype = 'U'   -- user tables
 ORDER BY o.name, col.column_id

When I run the SP I receive the error: "(Msg 102, Line 63) Incorrect syntax near '.'."

Line 63 is the FROM statement that uses the 3rd argument to the SP. (@tr_database)

The interesting thing is if I substitue the actual parameter value for the parameter like this:

    SELECT o.name table_name, col.name column_name, t.name data_type, col.max_length, col.is_nullable
    FROM [HQ-211-STOCK-DBO].sys.columns col
     INNER JOIN [HQ-211-STOCK-DBO].dbo.sysobjects o on o.id = col.object_id
     INNER JOIN [HQ-211-STOCK-DBO].sys.types t ON t.user_type_id = col.user_type_id
     WHERE o.xtype = 'U'   -- user tables
     ORDER BY o.name, col.column_id

It works fine.

Can anyone spot the error?

You can't use parameters for database names like that: `@tr_database.sys.types`. You need to use EXEC instead and build a string from that parameter. See [this answer](http://stackoverflow.com/questions/2073544/set-database-name-dynamically-in-sql-server-stored-procedure) — CodingIntrigue, Oct 04 '13 at 14:55

SWeko · Accepted Answer · 2013-10-04T15:08:21.203

What you need is dynamic sql.

You cannot set parameters that are the names of the tables / views / functions or other database objects. What you need is along these lines:

declare @sql nvarchar(max)
SET @SQL = 'SET @DFCursor = CURSOR FAST_FORWARD FOR 
            SELECT o.name table_name, col.name column_name, t.name data_type,
                   col.max_length, col.is_nullable
             FROM '+@tr_database+'.sys.columns col
             INNER JOIN '+@tr_databasee+'.dbo.sysobjects o 
                     ON o.id = col.object_id
             INNER JOIN '+@tr_databasee+'.sys.types t 
                     ON t.user_type_id = col.user_type_id
             WHERE o.xtype = ''U''
             ORDER BY o.name, col.column_id'
EXECUTE(@SQL)

IMHO, mixing cursors with dynamic SQL will be nightmarish, both from a maintenance and performance perspective, but, for a once-off piece of code, it might work.

Stored Procedure Parameter Causes Error

1 Answers1