Parameter problem in a stored procedure

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)

Parameter problem in a stored procedure

Author

Topic

Jozef Selesi
Starting Member

5 Posts

Posted - 2003-07-23 : 12:13:29

Hello,
I wrote a simple stored procedure. Why does the Enterprise Manager syntax checker say "Must declare the variable '@TableName'"?


CREATE PROCEDURE myapp_GetNextID
(
	@TableName	varchar(50),
	@ColumnName	varchar(50),
	@ID		int OUTPUT
)
AS

SET @ID = SELECT TOP 1 @ColumnName FROM @TableName ORDER BY @ColumnName DESC

SET @ID = @ID + 1

RETURN

I am new to stored procedures.

Thanks,
Jozef

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 12:24:29

You can't do that. What you are trying to do is called dynamic sql. You should do a forum search for dynamic sql if this is what you really need to do (although it is not recommended) to see how to write dynamic sql.

Tara

Edited by - tduggan on 07/23/2003 12:24:47

Jozef Selesi
Starting Member

5 Posts

Posted - 2003-07-23 : 13:03:41

So, to get the same functionality, I should either create separate stored procedures for each table, or generate a select query in the application. Is one of these solution better, and/or is there another alternative? I know that sps are preferred for a number of reasons, but creating many small almost identical ones doesn't seem very efficient.

tkizer
Almighty SQL Goddess

38200 Posts

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

Creating small stored procedures is WAY MORE efficient than dynamic sql. What is not efficient about having multiple stored procedures that do the same thing? You will gain performance by not using dynamic sql. And why are you getting the next ID anyway. Isn't your column an IDENTITY? If it isn't, I would suggest changing it to an IDENTITY so that you don't have to manage it. With IDENTITY, SQL Server will handle the values for you.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 13:07:12

Which brings up the question...

Why are you using an ID column? Why not use the natural key instead?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 15:47:59

quote:

Which brings up the question...

Why are you using an ID column? Why not use the natural key instead?

Tara

Ahh Tara..it warms the heart....

Brett

8-)

Jozef Selesi
Starting Member

5 Posts

Posted - 2003-07-23 : 18:56:06

Yes, you are right, setting it as an IDENTITY is the way to go.

quote:

Why are you using an ID column? Why not use the natural key instead?

In this case, I believe that a surrogate key is a better option.

Thank you very much for your help!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 10:24:12

quote:

In this case, I believe that a surrogate key is a better option.

Hope you like joins....

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-24 : 18:48:16

...and duplicate rows.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 09:59:51

Data Integrity? What's that?

I read about it somewhere once...

Brett

8-)

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-25 : 10:04:36

What is this "keys" I keep hearing people talk about? :p

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources