Calling a SProc with an OUTPUT param from SSMS

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 2005 Forums

Transact-SQL (2005)

Calling a SProc with an OUTPUT param from SSMS

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-06-27 : 11:02:02

I have a SProc that takes several inputs, inserts a record, and then returns (via an OUTPUT parameter) the record id (int) of the record it inserted. Easy peasy usual insert statement.

I want to call this SProc from the SSMS query analyzer. So I typed something like this (simplified for example):

exec MySprocName @ParamName1='somevalue' @ParamName2='anothervalue'

But this throws an error, and says:
Must declare the scalar variable "@RecordId"
(@RecordId is the OUTPUT param)

How do I pass in an Output Param from the command line in SSMS?

-Todd Davis

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-06-27 : 11:05:40

declare @RecordID int

exec MySprocName @ParamName1='somevalue' @ParamName2='anothervalue', @RecordID out

select @RecordID

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-06-27 : 11:10:35

more info: http://msdn.microsoft.com/en-us/library/ms187004.aspx

Nathan Skerl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 11:11:35

quote:
Originally posted by nathans

declare @RecordID int

exec MySprocName @ParamName1='somevalue', @ParamName2='anothervalue', @RecordID out

select @RecordID

Nathan Skerl

missed a comma

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-06-27 : 11:16:35

ah, the danger of copy/pasting the OP code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 11:20:30

quote:
Originally posted by nathans

ah, the danger of copy/pasting the OP code

yeah...

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-06-27 : 11:41:48

quote:
Originally posted by nathans

more info: http://msdn.microsoft.com/en-us/library/ms187004.aspx

Nathan Skerl

Thanks, this info helped immensely. At the end, I have to write it like this for some reason, but it worked...

exec MySprocName @ParamName1='somevalue', @ParamName2='anothervalue', @RecordID = @RecordID out

-Todd Davis

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-06-27 : 11:51:16

correct, sorry. When setting values to params you need to be consistent

so either: exec dbo.SomeProc @Param1 = 'Value1', @Param2 = @Param2 out
or exec dbo.SomeProc 'Value1', @Param2 out

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources