Stored Procedure ... - 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 Procedure ...

Author

Topic

kash
Starting Member

1 Post

Posted - 2003-06-10 : 07:23:09

Hi all,

I would like to execute this procedure But I want to pass fieldname as parameter.

e.g @fieldname=name

how can I do this, Below is the code but its giving me error.

CREATE PROCEDURE [test]
@fieldname VARCHAR (50),

AS
Begin

UPDATE tblMain SET @fieldname='tim'
WHERE (ID = '20')

end

GO

Thanks in advance:
Kashif

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-10 : 07:27:22

Sounds like you might have some database design issues. But you can use dynamic sql for this.

http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic+sql

Damian

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-06-10 : 07:36:34

Kashif
Is the error a syntax error
If so remove the comma from

quote:
CREATE PROCEDURE [test]
@fieldname VARCHAR (50),

AS
Begin

CREATE PROCEDURE [test]
@fieldname VARCHAR (50)

AS
Begin

HTH

Andy

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-10 : 13:15:07

no, no, I can't do it....

drat:

CREATE PROCEDURE [test]
@fieldname VARCHAR (50),

AS
Begin

DECALE @SQL varchar(8000)

SELECT @SQL = 'UPDATE tblMain SET @fieldname = '
+ ''''
'tim'
+ ''''
+' WHERE (ID = '
+ ''''
+'20'
+ ''''
+')'

EXEC (@SQL)

RETURN
GO

I feel dirty....

Brett

8-)

Edited by - x002548 on 06/10/2003 13:15:41

Edited by - x002548 on 06/10/2003 13:16:31

mtomeo
Starting Member

30 Posts

Posted - 2003-06-10 : 14:48:43

(those are all single quotes)

 
CREATE PROCEDURE [test] 
@fieldname VARCHAR (50)

AS

BEGIN

DECLARE @SQL VarChar(8000)

SELECT @SQL = 'UPDATE tblMain SET ' + @fieldname + ' = ''tim'' WHERE ID = ''20'''

Exec (@SQL)

END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-10 : 17:06:20

also keep in mind if fieldname has spaces in it or is a reserved word, you will need brackets. probably a good idea to always include them anyway.

CREATE PROCEDURE [test]
@fieldname VARCHAR (50)

AS

BEGIN

DECLARE @SQL VarChar(8000)

SELECT @SQL = 'UPDATE tblMain SET [' + @fieldname + '] = ''tim'' WHERE ID = ''20'''

Exec (@SQL)

END

Overall, though, I strongly object to this type of stored procedure .... a stored proc should hide physical database objects from the developers, NOT require them as arguments !

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-11 : 09:06:45

EXEC dbo.test garbageInput

BOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOM

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources