Stored Procedure update variable tablename

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure update variable tablename

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-02-07 : 08:59:04

Hi

I want to run a stored procedure to update a column in a table whereby I will pass the tablename to the sp update statement as a variable and the column name as a variable. I have been using dynamic sql, not so safe, but as I can't think of a better way..
here is my code. but I am getting 'Invalid column name 'column1'.
Something I am doing wrong somewhere.

Thanks



CREATE PROCEDURE usp_updatetable

@Tablename varchar(100), 
 @ProjectRef varchar(20)

AS

BEGIN 
 DECLARE  @SQL VARCHAR(2000)


SET  @SQL = 'UPDATE "' + @Tablename + '" 
SET AISref = "' + @ProjectRef + '"'
EXEC (@SQL)

END



EXEC usp_updatetable @Tablename = 'table1', @ProjectRef = 'column1'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-07 : 09:08:08

it gives:

UPDATE "table1" 
SET AISref = "column1"

so there is no column named column1 in the table named table1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-07 : 09:09:25

and you don't need the double quotes in T-SQL

No, you're never too old to Yak'n'Roll if you're too young to die.

spinningtop
Starting Member

29 Posts

Posted - 2012-02-07 : 09:54:21

Thanks for your help.
I made a slight mistake before, the column name is not a variable but fixed as AISref. It is the update value which is a variable.

So basically I want my update statement to read


UPDATE Tablename 
SET AISref = 'value'

but when I print the @sql variable its reading without the single quote.


UPDATE Tablename 
SET AISref = value

here is my code




CREATE PROCEDURE usp_updatetable

@Tablename varchar(100), 
 @ProjectRef varchar(20)

AS

BEGIN 
 DECLARE  @SQL VARCHAR(2000)


SET  @SQL = 'UPDATE ' + @Tablename + '
SET AISref =  ' + @ProjectRef + ''
EXEC (@SQL)

PRINT @SQL

END




EXEC usp_updatetable @Tablename = 'table1', @ProjectRef = 'value'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-07 : 10:01:41

That should work. Note that there are only single quotes in my solution even if they look like double quotes.

quote:
SET @SQL = 'UPDATE ' + @Tablename + ' SET AISref = ''' + @ProjectRef + ''''

No, you're never too old to Yak'n'Roll if you're too young to die.

spinningtop
Starting Member

29 Posts

Posted - 2012-02-07 : 10:08:21

Yes, this works!

I thought it would be about the right number of quotes. How annoying.

Thanks very much

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources