Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such:
SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2'
Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as:
EXEC (@BuildDBPageUsed)
The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed?
*Thanks* for any help in this matter.
Brad
View Complete Forum Thread with Replies
Related Forum Messages:
Select Output With Cursor
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated. --CURRENT OUTPUT-- empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query --DESIRED OUTPUT-- empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet --Here is the cursor script.-- Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255) DECLARE emp_skills CURSOR For select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' DECLARE emp_skills2 CURSOR For select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' OPEN emp_skills OPEN emp_skills2 FETCH NEXT FROM emp_skills into @empID, @Rank, @skills FETCH NEXT FROM emp_skills2 into @skills2 WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = @skills2 FETCH NEXT FROM emp_skills2 into @skills2 Print @empID + ' '+ @Rank + ' ' + @message FETCH NEXT FROM emp_skills into @empID, @Rank, @skills End CLOSE emp_skills DEALLOCATE emp_skills CLOSE emp_skills2 DEALLOCATE emp_skills2 --Previous Post-- Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output. --SQL SCRIPT__ select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') --CURRENT OUTPUT-- empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query --DESIRED OUTPUT-- empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
View Replies !
Set More That One Var From Select Stament
I have the next code: declare @SaldoNuevo decimal(9,2) SET @SaldoActual = ( SELECT SALDO FROM TBSALDOS WHERE ID=@ID ) But I want to do a select that return 3 field and set the values to three variables. declare @SaldoNuevo decimal(9,2) declare @LastName varchar(30) declare @Name varchar(30) SET @SaldoActual, @LastName, @Name = ( SELECT SALDO, LASTNAME, NAME FROM TBSALDOS WHERE ID=@ID ) Hope you undestand my English
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 !
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 !
Stored Procedure In A Select Stament
Good morning! My name is Joaquim Roca Vergés, and i'm working with sql server since few time. My english is very basic as you can see,but i hope that you understand clearly. I'm tying to put a stored procedured in a select statement and i can't. Something like this: Declare @var int select field1, exec sp @var output from table1 And of course i can't. A friend of mine tolds me that it's possible to do with the OPENROWSET so i do this SELECT * FROM OPENROWSET('SQLOLEDB','myserver';'mylogin';'mypass word', 'Exec sp ' + @var + ' output') But sql server tells me that the + symbol is no correct. Other friend told me about user defined function in sql server 2000 but i have a problem: the client i'm working for, don't have sql server 2000. He have sql server 7.0 How can i do with variables in openrowset? There's another way for what i'm trying to do? Can somebody help me please? Best regards Joaquim Roca Vergés
View Replies !
Fault With Configure The Select Stament
Goodday all I,m having a problem with something that worked well just recently. maybe I changed a setting by mistake. I have a Database grid view on my page. When I configure my data sourse and get to "configure the select statment " on the wizard and press advanced to open the wizard with two check boxes " generate INSERT, UPDATE etc and the other check box "Use optimistic concurrency " this input box of the wizard is greyed out (non responsive) Does anyone know how to rectify this? Regards Rob
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
How To Assign The SELECT Statement Output To A Local Variable?
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
View Replies !
Using A Cursor Output From Stored Proc
Has anyone ever tried to use a cursor as an output variable to a stored proc ? I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc @parentId integer, @outputCursor CURSOR VARYING OUTPUT AS BEGIN TRAN T1 DECLARE parent_cursor CURSOR STATIC FOR SELECT parentTable.childId, parentTable. parentValue FROM parentTable WHERE parentTable.parentId = @parentId OPEN parent_cursor SET @outputCursor = parent_cursor DECLARE @childId int DECLARE @parentValue varchar(50) FETCH NEXT FROM parent_cursor INTO @childId, @parentValue WHILE @@FETCH_STATUS = 0 BEGIN SELECT childTable.childValue FROM childTable WHERE childTable.childId = @childId FETCH NEXT FROM parent_cursor INTO @childId, @parentValue END CLOSE parent_cursor DEALLOCATE parent_cursor COMMIT TRAN T1 GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself. My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter() da.SelectCommand = New SqlCommand("myStoredProc", conn) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int) paramParentId.Value = 1 Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor") paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas? Thanks Martin
View Replies !
Sorting Cursor Output WITHOUT Using Order By
Hi, I have a situation where I need to sort the output of a cursor. But since the sort criteria are rather complex, I am NOT able to use the Order By clause directly with the cursor definition statement. Hence, I need to have a solution where I will use a dummy (calculated) field within the CURSOR and I want the output of this cursor sorted by the dummy field that I calculated within the cursor itself. Please let me know the different possibilities in this scenario. Thank you in advance Raj
View Replies !
Exporting Cursor's Output To Excel Spreadsheet
Hi, I have a this table.... CREATE TABLE RCSAdvantage (Resident_No int , FirstName varchar(50), LastName Varchar(50), DOB datetime, Tel char(10), source varchar(10)) Records.... INSERT INTO RCSAdvantage VALUES('123','Mike','Bhatt','12/12/2003','123','RCSA') INSERT INTO RCSAdvantage VALUES('TM123','Mike','Bhatt','12/12/2003','456','TRIMICRO') INSERT INTO RCSAdvantage VALUES('INR234','Mike','Bhatt','12/12/2003','890','INSIGHT') INSERT INTO RCSAdvantage VALUES('INR234','John','Bhatt','12/12/2003','890','INSIGHT') I needed to run following cursor and get the result exported to excel file. But Cursor retrives two resultset and while exporting to excel spreadsheet , it is the only first resultset without second resultset. How can it be exported to excel as a single resultset combined of first and second one. /* Suppress counts from being displayed*/ SET NOCOUNT ON /*Declare the variables */ DECLARE @cnt int,@FirstName varchar(15),@LastName varchar(15),@DOB datetime /*Declare Cursor */ DECLARE RCSA_C CURSOR LOCAL FOR SELECT COUNT(*), FirstName, LastName, DOB FROM RCSAdvantage GROUP BY FirstName,LastName,DOB HAVING (COUNT(*)>1) ORDER BY FirstName /*Open the cursor */ OPEN RCSA_C /*Get the resultset from the first row of the cursor*/ FETCH NEXT FROM RCSA_C INTO @cnt,@FirstName,@LastName,@DOB WHILE @@fetch_status=0 BEGIN SELECT * FROM RCSAdvantage WHERE FirstName=@FirstName AND LastName=@LastName AND DOB=@DOB /*Get the next row */ FETCH NEXT FROM RCSA_C INTO @cnt,@FirstName,@LastName,@DOB END /*Close the cursor */ DEALLOCATE RCSA_C --SELECT * FROM RCSAdvantage Please 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 !
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 !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
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 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 !
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 !
MIKE - UPDATE Stament Again
Is it possible to have this in an UPDATE statement? UPDATE table SET @str1+@str2 = '001' I got the result of "1 row affected" But when I try to select that column, it wasn't updated. The result remains NULL. >> My column name is the string in str1 concate with str2. E.g @str1 = 'First' @str2 = 'Name' What I wanted to update is the column "FirstName". The initial value is NULL. After I tried updating it, it gave me one row affected. But after I select again, it actually didn't update at all. Thanks again.
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 !
In Cursor: Help To Perform Proper Comparison Using Variable
Hi All, I failed to find record when using variable in cursor in WHERE clause: ID is uniqueidentifier field in the table DECLARE @EncounterID uniqueidentifier ........ WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found Tried to CAST(@EncounterID as char(40)) but still no luck. I would greatly appreciate any advise hot to make it working. Thank you very much in advance Roman
View Replies !
Use Of Concate Strings In UPDATE Stament
Is it possible to have this in an UPDATE statement? UPDATE table SET @str1+@str2 = '001' I got the result of "1 row affected" But when I try to select that column, it wasn't updated. The result remains NULL. Thank you.
View Replies !
Question About Assigning Cursor Variable Data Types...
Hi, I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype. Is there a way to do this in SQL Server 7.0 ? many thanks :)
View Replies !
Dynamic SQL Variable With Output ??
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
View Replies !
Output Variable To Crystal From Sp
hey folks... i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how.... any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management :-) take care.....kim
View Replies !
Redirect The Output To A Variable
This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please . alter proc r (@id INT) as BEGIN DECLARE @input VARCHAR(800) DECLARE @c_input INT DECLARE @i_Input INT DECLARE @input_left VARCHAR(800) DECLARE @delimiter CHAR(1) select @delimiter = ',' DECLARE @in VARCHAR(800) DECLARE @list VARCHAR(800) declare @list2 VARCHAR(800) SET @input = 'db2,oracle,sybase' select @c_input = (select dbo.Fx_CharCount(@delimiter,@input)) set @c_input = @c_input + 1 while @c_input > 0 BEGIN select @i_input = charindex(@Delimiter,@input) if @i_input != 0 BEGIN select @input_left = left(@input, @i_input - 1) END else select @input_left = @input select @in = '''' + @input_left + '''' select @list = ISNULL(@list + ',', '') + @in select @input = right(@input ,(len(@input) - @i_input)) SET @c_input = @c_input -1 if @c_input = 0 or @input = @input_left break end Print @list EXECUTE ('SELECT Label FROM systemtype WHERE Label Not IN (' + @list + ')') END my actual task is like this My input is a list of values seperated by commas now my output should be list of values not in the table joined by comma eg : if my table consists of list of all databases like db2 oracle, sybase, mssql, mysql, myinput would be like this db2,sybase,oracle my output should be mssql,mysql how to get that ? Any suggestions
View Replies !
Redirect The Output To A Variable
create procedure usp_test (@AccountID INT)asbegin DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Account_ID FROM Accounts OPEN @getAccountID FETCH NEXT FROM @getAccountID INTO @AccountID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @AccountID FETCH NEXT FROM @getAccountID INTO @AccountID END CLOSE @getAccountID DEALLOCATE @getAccountIDend i get nearly ten rows in the print statement how can i assign the output to a out variable where i get all ten rows.
View Replies !
Store The Output In To A Variable
This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please . alter proc r (@id INT) as BEGIN DECLARE @input VARCHAR(800) DECLARE @c_input INT DECLARE @i_Input INT DECLARE @input_left VARCHAR(800) DECLARE @delimiter CHAR(1) select @delimiter = ',' DECLARE @in VARCHAR(800) DECLARE @list VARCHAR(800) declare @list2 VARCHAR(800) SET @input = 'AWCA,GCS,IHP,Aetna' select @c_input = (select dbo.Fx_CharCount(@delimiter,@input)) set @c_input = @c_input + 1 while @c_input > 0 BEGIN select @i_input = charindex(@Delimiter,@input) if @i_input != 0 BEGIN select @input_left = left(@input, @i_input - 1) END else select @input_left = @input select @in = '''' + @input_left + '''' select @list = ISNULL(@list + ',', '') + @in select @input = right(@input ,(len(@input) - @i_input)) SET @c_input = @c_input -1 if @c_input = 0 or @input = @input_left break end Print @list EXECUTE ('SELECT Label FROM repricingsystemtype WHERE Label Not IN (' + @list + ')') END
View Replies !
Output Table As Variable
Hi, I've been looking into using the ability to select the table of an OLEDB destination task based on a package variable but would like to know if this could be utilised in the following example. I have a CSV file (potentially there are many) with a number of rows. The rows do not necessarily contain the same number of elements. Each row has an EventID and this is used to determine the database table that the row is to be inserted in. Initially i thought about using a conditional split to send each row down the path relating to its event ID. There would be multiple destinations depending on the table. With the possibility of 60 to 70 different event ID's this is very quickly going to get out of hand in the designer. I'd like to know if it would be possible to set up a case statement (or similar) in a script component and specify the table name as a variable depending on the event ID. I'd then use this table variable to set the destination task's table property. Does this sound like a possibility or do i have to go down the route of multiple paths? Many thanks in advance, Grant
View Replies !
XML Task And Variable Output?
Hello, What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor? Thanks, Phil
View Replies !
Output To Be Redirected To A Variable
here's my code i want my output to be stored in a variable or out statement .Any suggestions on the below procedure alter proc r (@id INT) as BEGIN DECLARE @input VARCHAR(800) DECLARE @c_input INT DECLARE @i_Input INT DECLARE @input_left VARCHAR(800) DECLARE @delimiter CHAR(1) select @delimiter = ',' DECLARE @inVARCHAR(800) DECLARE @list VARCHAR(800) declare @list2 VARCHAR(800) SET @input = 'AWCA,GCS,IHP,Aetna' select @c_input = (select dbo.Fx_CharCount(@delimiter,@input)) set @c_input = @c_input + 1 while @c_input > 0 BEGIN select @i_input = charindex(@Delimiter,@input) if @i_input != 0 BEGIN select @input_left = left(@input, @i_input - 1) END else select @input_left = @input select @in = '''' + @input_left + '''' select @list = ISNULL(@list + ',', '') + @in select @input = right(@input ,(len(@input) - @i_input)) SET @c_input = @c_input -1 if @c_input = 0 or @input = @input_left break end PRINT @list END
View Replies !
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all. I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out. So, first things first: let me explain what I need to do. I am designing a web application that will allow users to consult info available in a SQL2000 database. The user will enter the search criterea, and hopefully the web page will show matching results. The problem is the results shown aren't available per se in the DB, I need to process the data a bit. I decided to do so on the SQL Server side, though the use of cursors. So, when a user defines his search criteria, I run a stored procedure that begins by building a dynamic sql query and creating a cursor for it. I used a global cursor in order to do so. It looked something like this: SET @sqlQuery = ... (build the dinamic sql query) SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery OPEN myCursor FETCH NEXT FROM myCursor INTO ... CLOSE myCursor DEALLOCATE myCursor This works fine, if there's only one instance of the stored procedure running at a time. Should another user connect to the site and run a search while someone's at it, it'll fail due to the atempt to create a cursor with the same name. My first thought was to make the cursor name unique, which led me to: ... SET @cursorName = 'myCursor' + @uniqueUserID SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery ... The problem with this is that I can't do a FETCH NEXT FROM @cursorName since @cursorName is a char variable holding the cursor name, and not a cursor variable. So to enforce this unique name method the only option I have is to keep creating dynamic sql queries and exucting them. And this makes the sp a bitch to develop and maintain, and I'm guessing it doesn't make it very performant. So I moved on to my second idea: local cursor variables. The problem with this is that if I create a local cursor variable by executing a dynamic query, I can't extract it from the EXEC (or sp_executesql) context, as it offers no output variable. I guess my concrete questions are: Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How? Anybody sees another way arround this?Thanks in advance, Carlos
View Replies !
Stored Procedure With Output Variable
Can someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff
View Replies !
How Can I Put The Output Of A Stored Procedure Into A Variable
hi can anyone please help me i have a stored procedure ,can i put the value of this into a variable......???? I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ... example if my stored procedure is [dbo].[GetPortfolioName](@NT_Account varchar(100) ) and ans of this are NL UK IN GN JK then how can i put this into a variable like Declare @PortfolioName as varchar(250) set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) ) ....... and my ans is @PortfolioName = 'NL','UK','IN','GN','JK'
View Replies !
Mapping Output Parameter To A Variable!
Hi there, I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header. Here is what i am trying: The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command. Code SnippetEXEC [Get_logins] ?, ?, ? OUTPUT In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables. What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows Code SnippetRIGHT("0000000000" + (DT_STR, 10, 1252)@LoginCount, 10) I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @LoginCount variable to some value, this initially set value is successfully written to the file header. Thanks Paraclete
View Replies !
ExecuteSQL Using SQLSource Variable And Output
Really stuck trying different ways to get a string variable (expression based) to parse and run correctly in an ExecuteSQL task. Would someone be able to advise why one of the following methods works and the other does not? I'd really prefer to have the second currently not working method be used if possible. WORKS: "[audit].[up_LogEtlPackageStart] @ETLAuditParentKey = ?, @Description = ?, @PackageName = ?, @PackageGuid = ?, @PackageVersionMajor = ?, @PackageVersionMinor = ?, @PackageVersionBuild = ?, @MachineName = ?, @ExecutionGuid = ?, @LogicalDate = ?, @StartTime = ?, @Operator = ?, @ETLAuditKey = ? OUTPUT" FAILS: When the variable expression is set as follows I : Code Block "[audit].[up_LogEtlPackageStart] @ETLAuditParentKey = " + (DT_WSTR, 100)@[User::ETLAuditParentKey] + ", @Description = " + (LEN( @[User::PackageDescription]) == 0 ? "' '" : @[User::PackageDescription]) + ", @PackageName = " + @[System::PackageName] + ", @PackageGuid = " + @[System::PackageID] + ", @PackageVersionMajor = " + (DT_WSTR, 100)@[System::VersionMajor] + ", @PackageVersionMinor = " + (DT_WSTR, 100)@[System::VersionMinor] + ", @PackageVersionBuild = " + (DT_WSTR, 100)@[System::VersionBuild] + ", @MachineName = " + @[System::MachineName] + ", @ExecutionGuid = " + @[System::ExecutionInstanceGUID] + ", @LogicalDate = " + (DT_WSTR, 100)@[User::LogicalDate] + ", @StartTime = " + (DT_WSTR, 100)@[System::StartTime] + ", @Operator = " + @[System::UserName] + ", @ETLAuditKey = " + (DT_WSTR, 100)@[User::ETLAuditKey] + " OUTPUT" This method generates a "Syntax error, permission violation, or other nonspecific error. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" Any ways to get the second failing expression to work? Is this a problem due to the OUTPUT parameter? THANKS!
View Replies !
Output Filename Downloaded To A Variable
Hope I can make this clear... I have to download a file via FTP from one of our vendors, the filename is MMDDYYYY.zip, this is a weekly file and is posted between Tuesday and Thursday, so basically I never know exactly what the file is going to be called, to fix that, I added a FTP Task that will download a file if the date = today (Today = Thursday, when the job kicks off), if this fails, try a file where the name = today-1 (Wednesday), if this fails, try today-2 (Tuesday), all of that inside of a Sequence Container and with MaximumErrorCount set to 3. This is fine and it works fine, good. Now my other issue, the .txt file inside the zip file (1080.txt), after being decompressed, needs to be renamed to MMDDYYYY.txt, where MMDDYYY = to the file I downloaded, but I never know what the file is going to named (MMDDYYYY, MMDD-1YYYY, MMDD-2YYYY). Is it possible to output the filename of the file that I successfully download to a variable, so I can re-use name? Any other thoughts?
View Replies !
Problem With Output Variable In The Stored Procedure
Hi I am working on 2.0, here i got a problem, i.e actually my requirement is I am updating some values to the database at that time i am creating a scope_identity value i need to get that value to my code beheind after updating. for that i did the storedprocedure like this ALTER PROCEDURE [dbo].[AddSubCategories] ( @id int, @level int, @name nvarchar(50), @description nvarchar(Max), @CreatedByUser varchar(50), @CreatedDate datetime, @LastUpDate datetime ) AS Insert into Category( LEVEL, NAME, DESCRIPTION, CREATEDBYUSER, CREATEDDATE, LASTUPDATE ) values ( @level, @name, @description, @CreatedByUser, @CreatedDate, @LastUpDate ) insert into SubCategory( PARENTCATEGORYID, CHILDCATEGORYID ) values ( @id, SCOPE_IDENTITY() ) Select SCOPE_IDENTITY() and in my code behind i did like this object value = db.ExecuteScalar ("AddSubCategories", Id, Level, Name, Description, CreatedByUser, CreatedDate, LastUpDate); but i am getting null value, and i tried by giving like this also that is in the stored procedure i coded an output variable like this also @SubCategoryId int OUTPUT @SubCategoryId = SCOPE_IDENTITY() if i tried using like this also its not working so can any one please help me in this regard.
View Replies !
|
TRACKER
