472,956 Members | 2,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,956 software developers and data experts.

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 97259
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Aleksi Kallio | last post by:
I'm passing a result tree fragment that holds multiple strings (fieldnames, in this case) and in the called template I wan't to test if a certain string contains any of those names. I have this:...
4
by: Jim via DotNetMonster.com | last post by:
Hi, How can I assign the result of a function to a variable. I need to get the result so that I can query the database again. What I'm trying is:...
2
by: Gigsman | last post by:
public void Foo(string one) { one = "two"; } main() { string one = "one"; Foo(one);
5
by: Karl Irvin | last post by:
In VBA, how do you see the results of an sql count statement like the following ? Select Count (*) as Total from tblCustomer Result = DoCmd.RunSql("Select Count (*) as Total from...
6
by: Busbait | last post by:
I need to know how can I store SQL result into variable, I have used the below code but it dose not work. Is there an easier way to do this? ...
7
by: JahMic | last post by:
I'm having a problem with exec on my hosting server. Unfortunately, the hosting support seems to be anything but helpful. The following works fine on my localhost: <?php $MaskData =...
2
by: mguy27 | last post by:
I am working with VBA in ExcelUltimately I am trying to take a huge amount of data (9500-10000 rows) and do a search for text including the words "Violated Door". I then want to take the rows (which...
0
by: FLANDERS | last post by:
Hi all, Is it possible to declare a SQL type of result set or similar? I want to do use the IN predicate like you can in a non-procedural SQL like this: UPDATE TABLE1 SET COL1 = 123 WHERE COL2 IN...
2
by: pankaj17 | last post by:
hello everyone, MYSQL query ............ how to set prepare stmt result in variable i have written prepare stmt in store procedure and i want to set result of prepare stmt in variable ...
8
by: Jollywg | last post by:
I'm trying to make a page where the user can view his/her user info. If they have not entered any info they should be able to click the button and their info is stored in an access table named...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.