Storing The Result Into A Variable W/o Cursor
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 Complete Forum Thread with Replies
Related Forum Messages:
Storing Result Of EXEC Into A Variable
Thanks for everyone's help. I used sp_executesql and that worked atreat.I used the following code inside a cursor to go round all the usertables and to store the counts in a separate table.SET @tmp_select = N'SELECT @mycount = count(*) from [' + @tbl_owner+ '].[' + @tbl_name + ']'EXEC sp_executesql @tmp_select, N'@mycount int OUTPUT', @mycountOUTPUTupdate user_tables set numrows=@mycount where [name]=@tbl_name andowner=@tbl_ownerfetch next from tbl_cursor into @tbl_name, @tbl_owner
View Replies !
Storing Values Into Variable From A Distributed Query Result
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 Replies !
Storing The Result In A Variable Resulting From A Dynamic TSQL Query
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 Replies !
Storing The Result Of A &&"Select TOP 1&&" Into A Variable
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 Replies !
Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result
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 Replies !
Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection
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 Replies !
Is It Possible To Put The Result From A Sp Into A Cursor
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 Replies !
Using The Result Of A Cursor
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 Replies !
SELECT DISTINCT Will Always Result In A Static Cursor
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 Replies !
SELECT DISTINCT Will Always Result In A Static Cursor
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 Replies !
Storing Part Of An Express To A Variable
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 Replies !
Storing Query Results In A Variable
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 Replies !
Storing Variable Values Whilst Using Cursors
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 Replies !
Storing Variable In Command File From SQL Server
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 Replies !
Storing Results Of Select Statement In @variable
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 Replies !
DataType Storing Strings Of Variable Length ?
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 Replies !
Storing Collumn Data In A Local Variable
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 Replies !
Cursor/variable Help
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 Replies !
How To Use Variable In A Cursor
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 Replies !
Storing A Money Data Item (in SQL Server) In A Double SSIS Variable
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 Replies !
Getting SQL Result In A Variable
Hi there I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt. how do we achieve that? thanks and regards Rahul Kuamr
View Replies !
Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!
View Replies !
Set Value For Variable In A Declared Cursor
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 Replies !
Using Cursor Variable As A Tablename
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 Replies !
Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!! Thanks a lot in advance Here some sample code: DECLARE @Destination char(50) DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR FOR SELECT destination from table1 OPEN AVGCallsToDest FETCH First FROM AVGCallsToDest INTO @Destination WHILE (@@FETCH_STATUS = 0 ) BEGIN exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination) select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls', avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination' from table1 (nolock) whereDATEPART(mm,adetdate) = 5 and DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1) group by DATEPART(mm,adetdate),bdur order by Month") FETCHNext FROM AVGCallsToDest INTO@Destination End CLOSE AVGCallsToDest DEALLOCATE AVGCallsToDest
View Replies !
How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor. Here is my query sample for yours to understand. SET NOCOUNT ON DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000) Set @qstring = 'select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm' PRINT '-------- ITEM Products Report --------' DECLARE ITEM_cursor CURSOR FOR execute (@qstring) OPEN ITEM_cursor FETCH NEXT FROM ITEM_cursor INTO @ITEMCODE WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' SELECT @message = '----- Products From ITEM: ' + @ITEMNAME PRINT @message -- Get the next ITEM. FETCH NEXT FROM ITEM_cursor INTO @ITEMcode END CLOSE ITEM_cursor DEALLOCATE ITEM_cursor Why i use @qstring? It is because the query will be changed by different critiera. Regards Edmund
View Replies !
Set Variable To Sp Result, Inside Sp
I need to get the result of one stored procedure, and set it as the value of a variable in another. Would this work? (Currently not in a location I can test this!) Code: create proc spAddEntry @pkid int, @calcTotal numeric(10,2) = exec spCalcTotal '@pkid' as ...
View Replies !
Result From Sql Task In Variable
i need to know how many rows are in a certain table and store this value in a variable in order to process this variable later in the package. of course i can build a data flow task within a row count component but as far as i understand it's necessary to read all data from a data source in order to use the row count component. now the question is if it's possible to use a sql task in the control flow and put a select count(*) statement within the task and then write the result of this select statement into a variable. shouldn't that be much more faster than using a whole dataflow for this problem?
View Replies !
Query Result In A Variable
Hello Again, did you miss me? ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that: DECLARE @A VARCHAR(50) @A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something' There is a form to do it? Thanks Friends
View Replies !
Setting Result To A Variable
Hi, below is the sql statements for my web service using C#. Code Block string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC" SqlCommand comm = new SqlCommand(sql, conn); Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?
View Replies !
How To Iterate Through Table Variable Without Using Cursor
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
View Replies !
Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example: declare @col varchar(50) set @col = 'Temperature' declare notifycurs cursor scroll for select @col from Table Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC (ie. exec('select '+@col+' from Table' ) but that gave me an error. Is there a way to pass in a variable for a column name for a curor select statement????
View Replies !
Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like: UPDATE tbl SET @varFieldName = @varValue The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd. I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then. To get the field name, I : SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars' Thanks, Carl
View Replies !
Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse: Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name' Open cursor1 That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
Possible To Fetch Next From Cursor Into Table Variable?
Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx
View Replies !
How To Read The Rows In A Cursor Variable
Hi, I have a dynamic query that returns its values in a cursor variable. How do I read each row from this cursor in a loop ? Eg.: use AdventureWorks go DECLARE @sqlnvarchar(4000), @paramsnvarchar(4000), @tables_cursorcursor, @db_namenvarchar(50), @table_namenvarchar(4000), @schema_namenvarchar(50); set @db_name = 'AdventureWorks'; set @schema_name = 'Production'; set @table_name = 'BillOfMaterials, Product'; set @sql = ' select a.name table_name ' + ' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' + ' on (a.schema_id = b.schema_id) ' + ' where b.name= @schema_name1 ' + ' and @table_name1 is null ' + ' order by 1; ' SELECT @params = N' @table_name1 nvarchar(3000) ,' + N' @schema_name1 nvarchar(100) ,' + N' @cursor cursor output' EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT
View Replies !
Variable Assignment In Cursor Declaration
Hi, here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL) but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help. thanks
View Replies !
Simplest Way To Get A Query Result Into A Variable
Hello, I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way? What do you think? A simple example would be nice. Cheers J
View Replies !
SQL Task, Result To Variable Errors Out
I have looked thru several similar threads with errors like this, but have not found a resolution. I have a SQL Task that runs this query: Code Snippet select NetRevenue = cast(sum(Base_Price + AL + MI + PO) as dec(10,2)) from Lancelot.DataWhse.dbo.GrossMarginDetail where tran_date_key <= ? + ' 23:59:59' and label_group <> 'X' and not (ar_ship_key in ('S999991','S998101''S998102')) and Document <> 'Cust Bal Debit Memo' There is a parameter mapping of "User::LastSaturday" of type date. I also have a result set with a result set name of "0" with a variable name of "User::GrossMargin," which is defined as a double in the package. The task has a resultset type of Single row, and at the moment the answer that is returned is 66228637.10. If I change the package variable to a type of string it of course works, but then I cannot do comparisons against it. I have step by step manually copied this from an existing DTSX, so I am baffled why this isn't working, and I'm ready to throw myself under a truck! I also tried to bring it in as a string, then convert it in a script task, but I cannot figure out how to reference the input and output variables. I can't seem to find any relevant docs on how to do that... If anyone has any ideas, I and my sanity would greatly appreciate it.
View Replies !
Assign SELECT Result To Variable
Hi this is probably a very stupid question, but I still need to know. How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
View Replies !
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim
View Replies !
|
TRACKER
