Output from sp_executesql - 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 2005 Forums

Transact-SQL (2005)

Output from sp_executesql

Author

Topic

jpkeohan
Starting Member

4 Posts

Posted - 2010-10-20 : 08:37:25

I have a function that returns an executable string. The string declares one or two variables and then returns a varchar. I see the value returned when I run the "exec sp_executesql @Query" command. However, I have tried all sorts of combinations of output parameters and I haven't been able to assign the returned value to a variable. Anyone know what I need to do?

The executable string is an XQuery command. It's too long to include here but here's the gist:
'declare @x xml
set @x = -validxmlstring-
select @x.value(-xquery lookup-)'

As mentioned, I run this and get the correct result returned. I've also tried it by assigning a variable within the string, and I still can't assign the return value to a variable after execution.

'declare @x xml
declare @result nvarchar(512)
set @x = -validxmlstring-
select @result = @x.value(-xquery lookup-)'

Thanks for any help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 08:48:17

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

No, you're never too old to Yak'n'Roll if you're too young to die.

jpkeohan
Starting Member

4 Posts

Posted - 2010-10-20 : 10:03:49

Thanks webfred. I have changed from using a function to using a stored procedure to see if I can get this to work. I'm still having the same issue with executing dynamic sql within the stored proc. How do I assign a value to what this returns?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 10:43:33

So have a look here.
If that doesn't help then maybe another mate here can give a solution...

Setting a Variable from Dynamic SQL
http://www.simple-talk.com/sql/learn-sql-server/pop-rivetts-sql-server-faq/

No, you're never too old to Yak'n'Roll if you're too young to die.

jpkeohan
Starting Member

4 Posts

Posted - 2010-10-20 : 12:52:34

Finally got it to work. I can still use a function to build the dynamic SQL and return is ias @xquery, and then I do this:

declare @vv varchar(255)
exec sp_executesql @xquery, N'@vv varchar(255) output', @vv output
select @vv

Now, my value is stored in the @vv variable.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:54:47

i didnt undersatnd the need of function to build dynamic sql. can you elaborate?

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

jpkeohan
Starting Member

4 Posts

Posted - 2010-10-20 : 15:23:35

I probabably didn't have to use a function but I started donw that road so I kept on it. I use it to pass a number of parameters to it in the form of xml elements. The function copiles these into an XQuery which is returned to a stored procedure. The stored procedure then executes the XQuery string to return a value from the XML file.

The idea is to make it as easy as possible to build XQuery statements. My function works by passing it the element names from the XML file withouth having to worry about building an XQuery statement each time.

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 06:40:48

Your solution is the right one

Note that, if you need to, you can use different local names thus:


declare @vv_LOCAL varchar(255)
exec sp_executesql @xquery, N'@vv varchar(255) output', @vv = @vv_LOCAL output
select @vv_LOCAL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources