Stored procs & Parameters - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Stored procs & Parameters

Author

Topic

ScriptingStudio
Starting Member

3 Posts

Posted - 2006-08-07 : 08:17:19

Hi,

I need to create a stored procedure in which the tablename and its update values are all in parameters. I manage to let it work with the tablename or the values as variables, but the combination of both seems to be a problem.

Any suggestions please?

PROCEDURE CSS_Attributes_Update
AS
DECLARE @ClassName VARCHAR(255)
DECLARE @AttributeId BIGINT
DECLARE @AttributeDefaultValue VARCHAR(255)
DECLARE @AttributeEnabled BIT
DECLARE @CSS_Attributes VARCHAR(5000)

SET @ClassName='CSS_Attributes_Table'
SET @AttributeId=1
SET @AttributeValue='MyValue'

SET @CSS_Attributes='UPDATE '+@ClassName+' SET AttributeValue=@AttributeValue WHERE AttributeId=@AttributeId'
EXEC(@CSS_Attributes)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 08:25:44

first of all, the variable @AttributeValue is not declared

For string you will need to enclose it in single quote.

SET @CSS_Attributes='UPDATE '+@ClassName+' SET AttributeValue=''' + @AttributeValue + ''' WHERE AttributeId = ' + convert(varchar(10), @AttributeId)

Also check out sp_executesql in BOL. It allows you to pass in parameter

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-07 : 08:32:45

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

ScriptingStudio
Starting Member

3 Posts

Posted - 2006-08-07 : 08:37:02

Hi KH,

In fact I pasted my code a little to fast, so sorry for the non-declared param ;-)

Just tried your solution and it works great. I guess I will be a 'single-quote dummy" for life.

So thanks very much

ScriptingStudio
Starting Member

3 Posts

Posted - 2006-08-07 : 08:40:10

I read the http://www.sommarskog.se/dynamic_sql.html
article, but not good enough apparently

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-07 : 10:13:55

quote:
Originally posted by ScriptingStudio

I read the http://www.sommarskog.se/dynamic_sql.html
article, but not good enough apparently

You should read it fully to know about Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources