Stored procedure and variables

[kevinforbes]

Hi there,
I have the following stored procedure that I can't get to work, I think it's in how I declare the variables, any help would be appreciated.

CREATE PROCEDURE [dbo].[kf_sp_update_field] AS
DECLARE @up_tbl VARCHAR(50)
DECLARE @tbl2 VARCHAR(50)
DECLARE @set_field VARCHAR(50)
DECLARE @set_field_from VARCHAR(50)
DECLARE @src_tbl VARCHAR(50)
DECLARE @join_tbl VARCHAR(50)
DECLARE @join_val1 VARCHAR(50)
DECLARE @join_val2 VARCHAR(50)

SET @up_tbl = 'my_up_table_name'
SET @set_field = 'my_set_field'
SET @set_field_from = 'my_set_field_from'
SET @src_tbl = 'my_src_tbl_name'
SET @join_tbl = 'my_join_tbl_name'
SET @join_val1 = 'my_join_val1'
SET @join_val2 = 'my_join_val2'

Update @up_tbl
Set @set_field = @set_field_from
FROM @src_tbl INNER JOIN
@join_tbl ON @join_val1 = @join_val2
GO


Thanks,
Kev

 

[tlbroadbent]

The problem is not in the variable declaration. SQL simply does not allow the use of variables for object or column names. You'll have to use dynamic SQL to accomplish what you want.

Declare @sql nvarchar(2000)

Set @sql=
'Update ' + @up_tbl +
'Set ' + @set_field + ' = ' + @set_field_from +
'FROM ' + @src_tbl + ' INNER JOIN' +
@join_tbl + ' ON ' + @join_val1 + ' = ' + @join_val2

exec sp_executesql @sql

I always wonder why anyone would want to write code like this. I've been working with SQL Server for 8 years and have never seen the need for dynamic table and column names. Is this a work problem or theoretical question? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[kevinforbes]

Yup, it's a work problem, basically I'm transferring data from one table to another several times so I'm using the same query over and over again and changing the table and column names.

To me, it's a little cleaner to change all the column and table names at the top of the procedure.

thanks,
Kev