Output Parameter question for SPs

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)

Output Parameter question for SPs

Author

Topic

HumanCompiler
Starting Member

4 Posts

Posted - 2002-09-11 : 20:46:12

Please accept my apologies if this has been asked before, but I didn't see it anywhere...

Here's my SP...


ALTER PROCEDURE dbo.UserAdd
(
	@Username varchar(25),
	@Password varchar(15),
	@FirstName varchar(50) = NULL,
	@LastName varchar(50) = NULL,
	@ID int OUTPUT
)

AS

SET @ID = 0

IF NOT EXISTS(SELECT ID FROM Users WHERE Username LIKE @Username)
BEGIN
	INSERT INTO Users (Username, Password, FirstName, LastName) VALUES (@Username, @Password, @FirstName, @LastName)
	SET @ID = @@IDENTITY
END

When I run it and provide values for @Username and @Password, I get an error saying that the SP expects parameter @ID

I've run into this before, but was able to work around it, but I really need an output parameter for this...I figured it should be really simple

Can anybody spot what I'm doing wrong??? Thanx a lot!

-Erik Porter

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-11 : 22:16:32

Try ...

ALTER PROCEDURE dbo.UserAdd
(
@Username varchar(25),
@Password varchar(15),
@FirstName varchar(50) = NULL,
@LastName varchar(50) = NULL,
@ID int = NULL OUTPUT
) AS

SET @ID = 0

IF NOT EXISTS(SELECT ID FROM Users WHERE Username LIKE @Username)
BEGIN
INSERT INTO Users (Username, Password, FirstName, LastName) VALUES (@Username, @Password, @FirstName, @LastName)
SET @ID = @@IDENTITY
END

HumanCompiler
Starting Member

4 Posts

Posted - 2002-09-11 : 22:47:23

That totally worked...thanx a million!

I'm curious why I had to do that though...I've used output parameters before and I didn't have to do that...oh well, it works...thanx again!

-Erik Porter

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-09-12 : 02:04:50

To get a output parameter, you have to first declare the output parameter.
e.g.
--------------
declare @x int
exec dbo.UserAdd 'abc', 'abc', @id = @x output
print @x
--------------

In your case, you were not passing the output parameter and there is no default specified for the same. Thats why SP expects the parameter.

Ramesh Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources