sp giving error - 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

General SQL Server Forums

New to SQL Server Programming

sp giving error

Author

Topic

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-13 : 06:47:30

i have to get the maximum into a output parameter. its giving error. whats the problem with this code

SET @supplier_code as EXECUTE (SELECT MAX(supplier_code)+1 AS Supp_Id FROM supplier)

suji

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 06:51:52

SELECT @Supplier_Code = MAX(Supplier_Code) + 1
FROM Supplier

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 06:52:20

Why are using Dynamic SQL at all?

This should work for you:

Select @Supplier_code = MAX(supplier_code)+1 FROM supplier

However, if you have any compelling reason to use dynamic sql, then use sp_executesql to assign MAX() value to variable @supplier_code

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 06:53:12

Ouch !!

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-13 : 07:28:50

or if you want to use Set then


Set @Supplier_code = (Select MAX(supplier_code)+1 FROM supplier)

Chirag

LazyDragon
Starting Member

30 Posts

Posted - 2006-09-13 : 08:32:50

SET is the ANSI Standard for variable assignment. Variable assignment using SELECT is T-SQL Proprietory.

LazyDragon
T-SQL Programmer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources