Storing The Result Into A Variable W/o Cursor

Feb 1, 2007

Hello All,

A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.

Thank you in advance

View 1 Replies


ADVERTISEMENT

Storing The Result Of A Select TOP 1 Into A Variable

Sep 7, 2007

hello

Does anybody know how to store the result of a select top 1 into a variable??

I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc


And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc

But none of them work


Any ideas??
Thanks

View 1 Replies View Related

Storing Values Into Variable From A Distributed Query Result

Mar 25, 2002

Hi.

I am trying to store the column value to a variable from a distributed query.

The query is formed on the fly.

i need to accomplish something like this

declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname


-MAK

View 2 Replies View Related

Storing The Result In A Variable Resulting From A Dynamic TSQL Query

Aug 30, 2007

Hello all:

Here is a sample query:



DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'

DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'



DECLARE @KeyCol NVARCHAR(50);

SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


DECLARE @KeyValue NVARCHAR (1000)


SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +

' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '



The value resulting from Executing @KeyValue is an integer.

I want to store this value in a new variable say @VAR2

When I do this

DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue

its giving me an error.


can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?

View 3 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould 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 datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Storing The Result Of Stored Procedure...

Jan 24, 2008

Hi All,
I have to execute one of the stored procedure within another stored procedure.
And have to store it into some table variable...
How to do that.pls give me the syntax...
Thanks and reagards
A

View 1 Replies View Related

Storing Result Set From RESTORE HEADERONLY

Dec 13, 1999

Could anyone please tell me whether and how I can store the result set from the RESTORE HEADERONLY ?

Thanks

View 2 Replies View Related

SQL 2012 :: Batch File And Storing Result

Sep 29, 2015

I have to use sql cmd and run diagnostic queries.

I need to run multiple dmvs as a batch file and storing the dmv result to some place.

View 9 Replies View Related

Storing A Stored-proc's Result Into A Temp Table

Jul 20, 2005

I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?

View 3 Replies View Related

Is It Possible To Put The Result From A Sp Into A Cursor

Dec 5, 2000

I am tring to find out wheather a SQL Agent job is running.
I was wanting to put the result from sp_help_job into a cursor and check whether @@fetch_status was successful.
if it fails then job is not running

the below code fails because when declaring a cursor expects a select statement.

is there anyway to get result from a sp into a cursor thanks

declare @sql varchar(100)
set @sql = 'sp_help_job null, ''event_167'', @job_aspect = ''job'', @execution_status = 1'
declare Job_Cursor cursor for
sp_help_job @job_name = 'some_job_name', @job_aspect = 'job', @execution_status = 1
open Job_Cursor
fetch next from Job_Cursor
if @@fetch_status <> 0
begin
print 'Job is not runnning'
end
if @@fetch_status = 0
begin
print 'Job is runnning'
end
close Job_Cursor
deallocate Job_Cursor

View 1 Replies View Related

Using The Result Of A Cursor

May 5, 2008

I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.

I have a database with two tables:
eg
1) myGameRecency which contains columns : GameId , Date, Zero, One, Two, Three
2) myGameFrequency which contains a column : weeksSinceLastDrawn
Using the myGameRecency table ---
I wish to Fetch the GameId (which is just a counter) which corresponds to the last time a 0 was entered into that column....known in the programme as @lastTimeDrawn
I also wish to Fetch the GameId which corresponds to the current date....known in the programme as @currentTimeDrawn
I then wish to subtract @lastTimeDrawn from @currentTimeDrawn and place that result in the myGameFrequency table.

I have tried a variety of code and last tried the following:

Can anyone please assist?
Thank you.
lpbcorp


if (Ball1 == true)

{

sqlCmd.CommandText = "DECLARE @currentTimeDrawn int " +
"DECLARE @lastTimeDrawn CURSOR " +

"DECLARE lastrow_cursor CURSOR FOR " +

"(SELECT GameId FROM " + DBGameName.ToString() + "Recency WHERE One = 0) " +

"OPEN lastrow_cursor " +

"FETCH LAST FROM lastrow_cursor " +

"SET @lastTimeDrawn = lastrow_cursor " +

"SET @currentTimeDrawn = (SELECT GameId FROM " + DBGameName.ToString()

+ DBGameName.ToString() + "Recency WHERE Date = '" + Date + "') " +





"UPDATE " + DBGameName.ToString() + "Frequency SET WeeksSinceDrawnMainDrawBall = @currentTimeDrawn - @lastTimeDrawn WHERE Ball = 1 " +





"CLOSE lastrow_cursor " +

"DEALLOCATE lastrow_cursor";

sqlCmd.ExecuteScalar();

}

break;

View 1 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

View 10 Replies View Related

Opening Cursor On Result Set From Stored Proc

Jul 20, 2005

In SQL how can a cursor be opened to iterate the result set returnedfrom a stored proc-Rahul SoodJoin Bytes!

View 1 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste CURSOR DYNAMIC
FOR Select DISTINCT name from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .


any idea how to make a select distinct result in a dynamic Cursor?

View 7 Replies View Related

Storing Query Results In A Variable

Mar 28, 2001

Can someone tell me how to store the results of a multi-row query in a text or varchar variable so that I might do something like this:

declare @var1 text

select @var1 = {results of select date, count(*) from testtable2 group by date)


Thanks!

joe

View 2 Replies View Related

Storing Part Of An Express To A Variable

Aug 3, 2007

I know this can be done, because I saw it and tried it once. But did not use it at the time and consequently forgot how.

I am storing an expression to a variable @Var Varchar(50)

@Var = ('aa','bb','cc')

I want to:

Select * from Table WHERE Field IN @Var

Substituting my variable for the " ('aa',bb','cc') which I can build dynamically...thus the use.

Purpose:
I have an aggregate query that currently has a CASE statement based on the contents of ('aa',bb','cc'). However, those contents can change. The aggregate will not allow the subquery (SELECT Field FROM Table).

What I remember looked something like:

SELECT * FROM Table WHERE field in ' & @Var &'.

View 1 Replies View Related

Storing Variable In Command File From SQL Server

Sep 13, 2004

Hi,
i am using command files(.cmd) and osql utility to conneft to a database.
following is the requirement.
I have a query "SELECT top 1 au_fname FROM pubs..authors".

Can i store the value of au_fname in the command file?

Is there any work around for this.

Any help is appreciated.

Thanks.... :)

View 1 Replies View Related

Storing Collumn Data In A Local Variable

Aug 16, 2007

Hi All,
I need to select some collumn data , can i store it in a varable some how.

Select name from Names

how can i assign this value to a variable, I need to manipulate this column value based on few cases.
As always thanks and appreciate your help.

View 1 Replies View Related

DataType Storing Strings Of Variable Length ?

Aug 22, 2007

Hello guys!

I am looking for the best DataType that makes it possible to store strings of variable length (from 15 to 300 caracters, rarely longer).


Thanks a lot for any help !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

Storing Variable Values Whilst Using Cursors

Feb 21, 2007

Hi below is the code I am using.------------------------------------SET NOCOUNT ONDECLARE @emailid varchar(50), @rastype varchar(50),@message varchar(80)declare @allrastypes varchar(200)DECLARE email_cursor CURSOR FORSELECT distinct EmailFROM dbo.tblMaintCustomerORDER BY EmailOPEN email_cursorFETCH NEXT FROM email_cursorINTO @emailidWHILE @@FETCH_STATUS = 0BEGINPRINT ' 'SELECT @message = 'Email Address ' +@emailidPRINT @message-- Declare an inner cursor based-- on vendor_id from the outer cursor.DECLARE rastype_cursor CURSOR FORSELECT distinct [RasType]FROM dbo.tblMaintCase x, dbo.tblMaintCustomer yWHERE x.caseid = y.caseid ANDy.Email = @emailidand RasType is not nullOPEN rastype_cursorFETCH NEXT FROM rastype_cursor INTO @rastypeselect @allrastypes = @allrastypes + ',' + @rastypeIF @@FETCH_STATUS <0PRINT ' <<None>>'WHILE @@FETCH_STATUS = 0BEGINSELECT @message = @rastypePRINT @messageselect @allrastypes = @allrastypes + ',' + @rastypeFETCH NEXT FROM rastype_cursor INTO @rastypeENDCLOSE rastype_cursorDEALLOCATE rastype_cursorinsert into dbo.tblTest values(@emailid,@allrastypes)select @allrastypes = ''FETCH NEXT FROM email_cursorINTO @emailidENDCLOSE email_cursorDEALLOCATE email_cursor--------------------------------------I basically want the value of @allrastypes to accumulate each time itloops through, which is is not doing.The result I get is :Email Address Join Bytes!G5R(for here i want @allrastypes to be 'G5R,')Email Address Join Bytes!G1G3G5O(for here i want @allrastypes to be 'G1,G3,G5O')Can someone helpThanksArchana

View 1 Replies View Related

Storing Results Of Select Statement In @variable

Feb 11, 2008



I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:

SET @value = select max(price) from product

but this does not work, can someone tell me how I would go
about in storing the results in a variable.

@value is declared as int

Thanks in advance,
Sharp_At_C

View 1 Replies View Related

SQL Server 2012 :: Storing Data From Temp Table Into A Variable

Aug 4, 2015

I have created procedure that creates a temptable with the users from a certain business unit.

I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.

CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)

[Code] ....

View 6 Replies View Related

Cursor/variable Help

Dec 7, 2006

I'm not sure about this one so if someone could help I'd appreciate this.

As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a)
and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.

View 2 Replies View Related

How To Use Variable In A Cursor

Dec 11, 2006

if i have the cursor

cursor:

cursor for
select name
from sysobjects
where type='P' AND category='0'

and wants a variable before sysobjects which equals a table name, so itll be:

cursor for
select name
from @variable..sysobjects
where type='P' AND category='0'

how do i include a variable within a cursor statement

View 16 Replies View Related

T-SQL Cursor Variable..

Jul 23, 2005

Hello All,Is there a T-SQL equivalent of the PL/SQL cursor rowtype datatype ?Thanks in advance

View 2 Replies View Related

SQL Server 2012 :: SELECT Query - Cursor To Display Result In Single Transaction

May 25, 2015

Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.

CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Asynchronous Cursor Population Slow For Large Result Sets

Jul 2, 2015

so async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.

Example:
--enable async cursor
exec dbo.sp_configure 'cursor threshold', 0; reconfigure;
declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int;
--example of giant result set
set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';

[code]...

Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.

View 7 Replies View Related

Integration Services :: Storing Column Value In SSIS Variable When Skipping Header Row?

Apr 28, 2015

I have a file with a header row which contains the date of the file and under that all the columns without a header.

In SSIS package I skip row and manually name the different columns.

However, I want to use the column in the header row to store the date value in an SSIS variable and use that variable to write it to our staging table. How can I do this when I skip the header row?

View 5 Replies View Related

Storing A Money Data Item (in SQL Server) In A Double SSIS Variable

Nov 5, 2007

Hi

I have a table in SQL Server with following spec
Table1(Grossamount(money))

I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS

Select Sum(Grossamount) from Table1

I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.

it gives me the error :
[Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".



I tried the following sql to no avail

Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1


Your help very much appreciated

View 7 Replies View Related

Using Cursor Variable As A Tablename

Oct 12, 2005

I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks

View 4 Replies View Related

Variable In CURSOR Sql Statement (was Please Help Me)

Dec 24, 2004

Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

Thanks !

View 3 Replies View Related

Set Value For Variable In A Declared Cursor

Feb 16, 2004

Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

----------------------------------------------------------------------

Thanks a lots,
Vincent

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved