How to put result from EXEC into a variable

[Mario Pranjic]

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.

[Mario Pranjic]

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

Mario.

[Guy van den Berg]

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.

[Shervin Shapourian]

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.

[Mario Pranjic]

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.

[Mario Pranjic]

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.

[Shervin Shapourian]

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.

[Erland Sommarskog]

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

[Mario Pranjic]

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.