Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)
declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_selec t)
set @mycount=exec(@ tmp_select) 6 51884
Try to use:
set @tmp_select = (select count(*) from dptest)
Yury Jhol
Chief Systems Architect
Manager of Database Department
Database Migration Tool www.ispirer.com
"Dipak Patel" <di*****@hotmai l.com> wrote in message
news:30******** *************** ***@posting.goo gle.com... Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as described below. This does not work, it fails on the last line with syntax error. Is there a way to do it? (The reason I want to do this is to store the count into a table, therefore eventually do an update table with the variable.)
declare @tmp_select char(500) declare @mycount int set @tmp_select = 'select count(*) from dptest' exec(@tmp_selec t) set @mycount=exec(@ tmp_select)
On 10 Jan 2005 04:39:44 -0800, Dipak Patel wrote: Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as described below. This does not work, it fails on the last line with syntax error. Is there a way to do it? (The reason I want to do this is to store the count into a table, therefore eventually do an update table with the variable.)
declare @tmp_select char(500) declare @mycount int set @tmp_select = 'select count(*) from dptest' exec(@tmp_selec t) set @mycount=exec(@ tmp_select)
The resultset from an exec can be put into a table directly:
declare @tmp_select char(500)
set @tmp_select = 'select count(*) from dptest'
CREATE TABLE ResultSet (mycount int)
INSERT INTO ResultSet
EXEC @tmp_select
To put it into a variable from there, you'd have to add another select.
For this specific example, though, it would be far easier to do
declare @tmp_select char(500)
declare @mycount int
select @mycount=count( *) from dptest
But I suspect you knew that.
"Dipak Patel" <di*****@hotmai l.com> wrote in message
news:30******** *************** ***@posting.goo gle.com... Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as described below. This does not work, it fails on the last line with syntax error. Is there a way to do it? (The reason I want to do this is to store the count into a table, therefore eventually do an update table with the variable.)
declare @tmp_select char(500) declare @mycount int set @tmp_select = 'select count(*) from dptest' exec(@tmp_selec t) set @mycount=exec(@ tmp_select)
Is there some reason you can't do this?
select @tmp_select = count(*)
from dptest
If you really need dynamic SQL (and normally you should avoid it), then this
is one way:
create table #t (num int)
exec('insert into #t select count(*) from dbo.sysobjects' )
select num from #t
But before using this solution, see here for all the reasons why dynamic SQL
is a bad idea: http://www.sommarskog.se/dynamic_sql.html
Simon
Dipak Patel (di*****@hotmai l.com) writes: Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as described below. This does not work, it fails on the last line with syntax error. Is there a way to do it? (The reason I want to do this is to store the count into a table, therefore eventually do an update table with the variable.)
declare @tmp_select char(500) declare @mycount int set @tmp_select = 'select count(*) from dptest' exec(@tmp_selec t) set @mycount=exec(@ tmp_select)
Use sp_executesql. For details http://www.sommarskog.se/dynamic_sql.html#sp_executesql
Don't use the method with a temp table suggested by others. That's
an extreme kludge.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog wrote: Dipak Patel (di*****@hotmai l.com) writes: Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as described below. This does not work, it fails on the last line with syntax error. Is there a way to do it? (The reason I want to do this is to store the count into a table, therefore eventually do an update table with the variable.)
declare @tmp_select char(500) declare @mycount int set @tmp_select = 'select count(*) from dptest' exec(@tmp_selec t) set @mycount=exec(@ tmp_select)
Use sp_executesql. For details http://www.sommarskog.se/dynamic_sql.html#sp_executesql
Don't use the method with a temp table suggested by others. That's an extreme kludge.
-- Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks for the help guys, i got it to work with sp_executesql.
[quote=Yury Jhol]Try to use:
set @tmp_select = (select count(*) from dptest)
=============== =
That worked for me, thanks Yury!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jonathan |
last post by:
I'm puzzled by Python's behavior when binding local variables which
are introduced within exec() or execfile() statements. First, consider
this simple Python program:
# main.py
def f() :
x = 1
print "x:", x
f()
|
by: Jan |
last post by:
I store sql-commands in a database table. In the first step I get the
sql command out of the database table with embedded sql. In the second
step I try to execute the command, which i got from the database
table, using dynamic sql.
Executing 'EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;' the
error code -2149 is returned That means "Specified partition does not
exist".
Does anybody know if it is a database problem or a problem of
|
by: Mario Pranjic |
last post by:
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!
|
by: Greg |
last post by:
I need to send the result of a procedure to an update statement.
Basically updating the column of one table with the result of a
query in a stored procedure. It only returns one value, if it didnt I
could see why it would not work, but it only returns a count.
Lets say I have a sproc like so:
create proc sp_countclients
@datecreated datetime
as
|
by: Karl.Auer |
last post by:
Hello group!
i have a table "group_code" wich relates the names of
nt-(domain)-groups to codes. now i want use the stored procedure
xp_logininfo (asking for the group-membership of the current user) to
join the result to "group_code". then i must use the new result (the
code) to join against other tables.
i know now, that i cant join results of SPs against tables. may be that
a UDF with a table result is the correct approach. but i...
| |
by: Dipak Patel |
last post by:
Thanks for everyone's help. I used sp_executesql and that worked a
treat.
I used the following code inside a cursor to go round all the user
tables and to store the counts in a separate table.
SET @tmp_select = N'SELECT @mycount = count(*) from .'
EXEC sp_executesql @tmp_select, N'@mycount int OUTPUT', @mycount
OUTPUT
update user_tables set numrows=@mycount where =@tbl_name and
owner=@tbl_owner
|
by: Mark Miller |
last post by:
I have an sp that outputs multiple xml fragments w/ no root. The sp calls
individual sp's to output the correct set of data and each "type" has
different fields.
ex.:
<LEADERBOARD @Type='Rushing'>
<LEADER @Carries=''/>
</LEADERBOARD>
<LEADERBOARD @Type='QBRating'>
<LEADER @Rating='' @CompletionPct=''/>
|
by: TPJ |
last post by:
I have the following code:
-----------------------------------
def f():
def g():
a = 'a' # marked line 1
exec 'a = "b"' in globals(), locals()
print "g: a =", a
|
by: serge |
last post by:
/*
Subject: How to build a procedure that returns different
numbers of columns as a result based on a parameter.
You can copy/paste this whole post in SQL Query Analyzer
or Management Studio and run it once you've made sure
there is no harmful code.
Currently we have several stored procedures which final
result is a select with several joins that returns many
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
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...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |