473,854 Members | 1,478 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 97429
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.c om...
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(Paramete rs list)

Shervin

"Mario Pranjic" <ke****@fly.srk .fer.hr> wrote in message
news:tt******** *************** *********@4ax.c om...
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.c om...
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
4309
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: <xsl:apply-templates select="field"> <xsl:with-param name="element-names"> <names> <name>foo</name>
4
2077
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: LessonID=GetPage(IntCourseNumber,IntLessonNumber,IntPageNumber) I get the error: Value of type 'System.Data.IDataReader' cannot be converted to 'Integer'
2
1405
by: Gigsman | last post by:
public void Foo(string one) { one = "two"; } main() { string one = "one"; Foo(one);
5
76389
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 tblCustomer") gives an error I need to get the count into a variable.
6
43761
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? --------------------------------------------------------- Dim dbsCurrent As Database Dim count As Recordset Dim result As String Set dbsCurrent = CurrentDb
7
2219
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 = "mask.exe -e \"TestString\""; $Result = exec($MaskData, $Output, $ReturnValue); echo("Result: ".$Result);
2
2215
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 contain Date in one cell, time in another, building name, room, etc) which contain that text in the description, copy and paste them into another workbook in Excel. I have created a program to do a simple "find, copy, paste, return, find next, copy,...
0
2803
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 (SELECT COL3 FROM TABLE2) I want to do this in procedural SQL, but I only want to use one cursor. So to do this I have declared a cursor for selecting COL2 from TABLE1 into a variable v_COL2 and am using a loop to iterate over the values....
2
4485
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 CREATE PROCEDURE `sp_countrows`(in_table_name varchar(30)) READS SQL DATA
8
9920
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 Users. If they already have data in the table then it should update with what is in the text boxes. For some reason usersRemaining always stays equal to 0 and never moves to 1 even if there is a user in the database. Attached is the code that I am...
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11031
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10684
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9517
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7918
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7082
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5744
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4159
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.