By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,723 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,723 IT Pros & Developers. It's quick & easy.

How to put result from EXEC into a variable

P: n/a
Hi!

Can anybody give me a hint how to put sa resut from EXEC into a
variable.
EXEC is called:

EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)
....)

Thanks!

Mario.
Jul 20 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Oh, yes, I forgot: EXEC is called inside a stored procedure.

Mario.
Jul 20 '05 #2

P: n/a
You need to use a temp table or a table variable, something like:

CREATE TABLE #Data (var int)
SELECT @TmpQuery = whatever
INSERT #Data exec (@TmpQuery)
SELECT @Out3 = var from #Data
DROP TABLE #Data

g.
--
www.sqlskunkworks.com

"Mario Pranjic" <ke****@fly.srk.fer.hr> wrote in message
news:tt********************************@4ax.com...
Hi!

Can anybody give me a hint how to put sa resut from EXEC into a
variable.
EXEC is called:

EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)
...)

Thanks!

Mario.

Jul 20 '05 #3

P: n/a
Mario,

If you want to execute a script and use the result value, probably the
solution presented by Guy is the only way to do it. Insert the result into a
temporary table and read it into a variable. But if you use a stored
procedure instead of the scrip, you can use EXEC to transfer result directly
into the variable.

EXEC @Result = YourSP(Parameters list)

Shervin

"Mario Pranjic" <ke****@fly.srk.fer.hr> wrote in message
news:tt********************************@4ax.com...
Hi!

Can anybody give me a hint how to put sa resut from EXEC into a
variable.
EXEC is called:

EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)
...)

Thanks!

Mario.

Jul 20 '05 #4

P: n/a
On Wed, 15 Oct 2003 22:58:37 +1000, "Guy van den Berg"
<gu*@anonymous.com> wrote:
You need to use a temp table or a table variable, something like:

CREATE TABLE #Data (var int)
SELECT @TmpQuery = whatever
INSERT #Data exec (@TmpQuery)
SELECT @Out3 = var from #Data
DROP TABLE #Data


There is no way to do it without creating tmp table?

Mario.
Jul 20 '05 #5

P: n/a
On Wed, 15 Oct 2003 22:58:37 +1000, "Guy van den Berg"
<gu*@anonymous.com> wrote:
You need to use a temp table or a table variable, something like:

CREATE TABLE #Data (var int)
SELECT @TmpQuery = whatever
INSERT #Data exec (@TmpQuery)
SELECT @Out3 = var from #Data
DROP TABLE #Data


One more issue: what happens when two users execute this code (sam SP)
at the same time? Woludn't one of them get the error creating #Data
table because it exists?

Mario.
Jul 20 '05 #6

P: n/a
Nope, this is a local temporary table. Even if 100 users run this procedure
all at the same time, SQL Server will create 100 copies of the temporary
table, all with the same logical name.

Shervin

"Mario Pranjic" <ke****@fly.srk.fer.hr> wrote in message
news:6a********************************@4ax.com...
On Wed, 15 Oct 2003 22:58:37 +1000, "Guy van den Berg"
<gu*@anonymous.com> wrote:
You need to use a temp table or a table variable, something like:

CREATE TABLE #Data (var int)
SELECT @TmpQuery = whatever
INSERT #Data exec (@TmpQuery)
SELECT @Out3 = var from #Data
DROP TABLE #Data


One more issue: what happens when two users execute this code (sam SP)
at the same time? Woludn't one of them get the error creating #Data
table because it exists?

Mario.

Jul 20 '05 #7

P: n/a
Mario Pranjic (ke****@fly.srk.fer.hr) writes:
There is no way to do it without creating tmp table?


There is. Guy must be using SQL 6.5, where this is the only option.

Use sp_executesql instead. See http://support.microsoft.com/?id=262499.

I also have an article on dynamic SQL on my web site,
http://www.algonet.se/~sommar/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8

P: n/a
On Wed, 15 Oct 2003 21:35:53 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:
There is. Guy must be using SQL 6.5, where this is the only option.

Use sp_executesql instead. See http://support.microsoft.com/?id=262499.

I also have an article on dynamic SQL on my web site,
http://www.algonet.se/~sommar/dynamic_sql.html.

Thanks a lot. I will check it out.

Mario.
Jul 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.