Help with Output Parameter - 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)

Help with Output Parameter

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2006-05-04 : 12:47:36

Hi - I would like my @AddSubscriber output parameter to return the value 2 if the SELECT COUNT(Email) statement finds an existing email address. So something like this:

IF (SELECT COUNT(Email) > 0 'return 2
record successfully added 'return 1
anything else 'return 0

Thanks.
*******************************************

CREATE Procedure sp_AddSubscriber

@Email as varchar(50),
@AddSubscriber Int OUTPUT

AS
IF (SELECT COUNT(Email)
FROM TSubscribers
WHERE Email = @Email) = 0

BEGIN
INSERT TSubscribers(Email) Values (@Email)
SET @AddSubscriber = 1
END
ELSE
SET @AddSubscriber = 0
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-04 : 13:02:03

And the question is? Are you asking how to retrieve the output value?

declare @out int
exec sp_AddSubscriber 'myEmail@myDomain.com', @out output
select @out

You can also use a RETURN code for this type of thing.
see RETURN in Books Online

EDIT:
btw, its not a good idea to name your procedures with "sp_" prefix. That is reserved for system stored procedures. You will take a (slight) performance hit because sql server will first look in the master database for this SP if you don't fully qualify the name.

And since that particular SP already exists in Master you probably be envoking the wrong SP.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources