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]

Re: How to put result from EXEC into a variable

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

Mario.

[Guy van den Berg]

Re: How to put result from EXEC into a variable

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.
--

https://www.sqlskunkworks.com

"Mario Pranjic" <keeper@fly.srk .fer.hr> wrote in message
news:ttfqovg3rv d6uafuf19b3tgf2 12d0l4tu7@4ax.c om...[color=blue]
> 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.
>
>[/color]

[Shervin Shapourian]

Re: How to put result from EXEC into a variable

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(Paramete rs list)

Shervin

"Mario Pranjic" <keeper@fly.srk .fer.hr> wrote in message
news:ttfqovg3rv d6uafuf19b3tgf2 12d0l4tu7@4ax.c om...[color=blue]
> 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.
>
>[/color]

[Mario Pranjic]

Re: How to put result from EXEC into a variable

On Wed, 15 Oct 2003 22:58:37 +1000, "Guy van den Berg"
<guy@anonymous. com> wrote:
[color=blue]
>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[/color]

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

Mario.

[Mario Pranjic]

Re: How to put result from EXEC into a variable

On Wed, 15 Oct 2003 22:58:37 +1000, "Guy van den Berg"
<guy@anonymous. com> wrote:
[color=blue]
>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[/color]

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]

Re: How to put result from EXEC into a variable

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" <keeper@fly.srk .fer.hr> wrote in message
news:6adrovgtul 0ebevu3q0rqcbdh o0q2pub9s@4ax.c om...[color=blue]
> On Wed, 15 Oct 2003 22:58:37 +1000, "Guy van den Berg"
> <guy@anonymous. com> wrote:
>[color=green]
> >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[/color]
>
> 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.[/color]

[Erland Sommarskog]

Re: How to put result from EXEC into a variable

Mario Pranjic (keeper@fly.srk .fer.hr) writes:[color=blue]
> There is no way to do it without creating tmp table?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mario Pranjic]

Re: How to put result from EXEC into a variable

On Wed, 15 Oct 2003 21:35:53 +0000 (UTC), Erland Sommarskog
<sommar@algonet .se> wrote:
[color=blue]
>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.[/color]


Thanks a lot. I will check it out.

Mario.