468,275 Members | 1,832 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,275 developers. It's quick & easy.

How to put result from EXEC into a variable

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
8 95626
Oh, yes, I forgot: EXEC is called inside a stored procedure.

Mario.
Jul 20 '05 #2
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
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
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
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
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
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
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.

Similar topics

1 post views Thread by Aleksi Kallio | last post: by
4 posts views Thread by Jim via DotNetMonster.com | last post: by
2 posts views Thread by Gigsman | last post: by
7 posts views Thread by JahMic | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
1 post views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.