Why doesn't this simple example work? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

Why doesn't this simple example work?

Author

Topic

rogdawg
Starting Member

23 Posts

Posted - 2010-03-16 : 12:24:07

I am trying to place the results of an "exec usp_someprocedure" statement into a variable. But it isn't working as it should.
The syntax should be "exec @myVar = exec usp_someprocedure", right?
Use this example code:


ALTER PROCEDURE [dbo].[usp_TESTING] 
	@Parameter int
AS
BEGIN
	SET NOCOUNT ON;
	select @Parameter + 1;
END

and then call the stored procedure with this:


declare @result int;
exec @result = usp_TESTING 4;
select @result;

When I run the second query, I get two result sets. One is a single column/single row that has the value "5" in it. The second result set is a single column/single row that has "0" in it. It seems that the returned value is not being placed in the @result variable.

What is happening here?
Thanks for any advice you can give.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:45:57

nope. you cant return a value from sp like this. see various ways of returning values below

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rogdawg
Starting Member

23 Posts

Posted - 2010-03-16 : 12:52:19

Thanks very much for the link, and for your quick response. I actually had that same page up in a different tab of my browser...looking for solutions.

This is amazing.
Check out this code:


DECLARE @retstat int;
EXECUTE @retstat = SQLSERVER1.AdventureWorks.dbo.uspGetEmployeeManagers @EmployeeID = 6;

as an example of getting a value from a stored procedure. The example is from this MSDN article.

http://msdn.microsoft.com/en-us/library/ms188332.aspx

and I have seen several other examples of the syntax I was incorrectly trying to use. There must be something I am missing.

Anyway, I will make changes to reflect the techniques shown in the article you provided.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:56:08

you're missing a return


CREATE PROCEDURE [usp_TESTING] 
	@Parameter int
AS
BEGIN
	SET NOCOUNT ON;
	select @Parameter=@Parameter + 1;
	return @Parameter
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-16 : 12:57:31

I would suggest that you return a result as either a record set (SELECT) or as an output parameter. But, in order to get your sample to work, change the SELECT to a RETURN:

ALTER PROCEDURE [dbo].[usp_TESTING] 
	@Parameter int
AS
BEGIN
	SET NOCOUNT ON;
	RETURN @Parameter + 1;
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources