Getting Return value from a dynamic sql stored procedure SQL Server 2008

Giganews Newsgroups
Subject: Getting Return value from a dynamic sql stored procedure SQL Server 2008
Posted by:  stainless (mark.wingfie…@sky.com)
Date: Wed, 21 Aug 2013

As a result of a migration process from SQL Server 2000 to 2008. we have stored procedures that are going to be held on a linked server and thus are building dynamic sql combining the server name in the environment we are in into the call.

I know there are many other soplutions but the big picture is we have to have a variable linked server name in each environment.

e.g. set @SQLcommand = @linkedservername + '.dbo.MyStoredProcedure ' + @someparametervalue

The issue we habe is we want to know what the Return value integer is from the called stored procedure to trap any errors.

If it was local, it would be as simple as "select @RC = dbo.MystoredProcedure @someparametervalue"

However, I am unsure how we can code this to get the @RC value from the @SQLcommand stored procedure call.

Is there a way of putting @RC into the dynamic SQL and referencing this in the calling SQL?

Have tried various versions of "Select @RC = Exec(@SQLcommand)" statements but none work.

I know this must be simple but have struggled to find examples in Google. Any ideas?

Cheers

Mark

Replies