Dynamic Select For CURSOR
Hi all
I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID
etc etc
Here's the cursor
DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod
OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1
-- DO SOME FUNKY STUFF
FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Dynamic Cursor/ Dynamic SQL Statement
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement. Using the examples given in Books Online returns compilation errors. See below. Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement? James -- SQL --------------- EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE author_cursor CURSOR FOR select_statement; EXEC SQL PREPARE select_statement FROM :szCommand; EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName; --Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
View Replies !
Dynamic SQL In Cursor
I need to pass a list of values into a cursor as such... DECLARE @group_SQL varchar(255) SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')' DECLARE groupContact_import_cursor CURSOR FOR EXEC(@group_SQL) OPEN groupContact_import_cursor FETCH NEXT FROM groupContact_import_cursor INTO @group_id WHILE (@@FETCH_STATUS = 0) BEGIN insert into groupContacts (group_id, contact_id) values (@group_id, @new_cid) FETCH NEXT FROM groupContact_import_cursor INTO @group_id END CLOSE groupContact_import_cursor DEALLOCATE groupContact_import_cursor But MS SQL doesn't seem to like the FOR EXEC(@group_SQL). Can someone shed some light? TIA
View Replies !
Dynamic Cursor
I am trying to use a dynamic cursor in a stored procedure: The code looks like this : /************************************************** *** set @sFormula = 'Monthlyformula' set @sStartDate = '02/01/2004' set @sEndDate = '02/01/2004' exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @sStartDate +' and '+ @sEndDate +')' ) /************************************************** *** And this is what it is interpreting select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004) My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned Thanks in advance SK
View Replies !
Dynamic Cursor
Hello ! I´m having a big problem with a dynamic cursor. There is my problem: I have two cursors. One I use to select a master table. The other I use to select a detail table. I want to fetch all rows of the master and for each row select the details. How can I pass to the detail cursor the master key ? Thanks and I´m sorry for my poor English ! :)
View Replies !
How Get The Dynamic Sql In To Cursor
Dear folks, In My Query i am using where in condition .It return multiple record .I want store it in to cursor and perform the operation. Declare @sql varchar(5000); set @sql='select * from Role where Role_id in('+ @role_ids +')'; Exec @sql; I want take this record set in to cursor .How to do it. please help me.
View Replies !
Dynamic Cursor Generation..
Hi Everybody,I have a probs with dynamic generation.I am writing the probs======================================create proc testasdeclare @query varchar(500)set @query = 'select * from table'----------------------------------------------declare mycur Cursor for Select * from table |open mycur |----------------------------------------------but instate of above block how can I dynamically generate this query?---------------------------------------declare mycur Cursor for exec (@query) |---------------------------------------Or tell me the way.RegardsArijit Chatterjee
View Replies !
Declare Cursor With Dynamic SQL?
Hello.. Can you declare a cursor with dynamic SQL? I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement. these attempts did not work: DECLARE crsCursor CURSOR FOR @vchrSQL DECLARE crsCursor CURSOR FOR (@vchrSQL) Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?
View Replies !
Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View Replies !
Dynamic Cursor - Sorting In Declaration
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas
View Replies !
Dynamic Execution Of Cursor Fetch
I'm trying to write code which will fetch records from a dynamically created cursor. Therefore the cursor name is not known at design time. Unfortunately applying an enclosing the fetch command in quotes and then calling the execute method with this string will not work. e.g. declare @A varchar(10), @B varchar(10), @sFetch varchar(255) --Open the cursor Execute ('Open ' + crAnyCursor) --create fetch string Select @sFetch = 'Fetch Next From' + crAnyCursor + 'into @A, @B' execute (@sFetch) The following error is returned... Msg 137, Level 15, State 1 Must declare variable '@A'. Is there another means of working with dynamically executed cursors? Thanks JohnG
View Replies !
Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot. CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS declare @cmdtxt as varchar(4000), @SQLString NVARCHAR(4000), @SQLString1 NVARCHAR(4000), @pid varchar(22), @lname varchar(60) begin EXEC SQL BEGIN DECLARE SECTION; char prep[] = @p_query; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE prep_stat FROM :prep; EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat; OPEN contact_crsr FETCH NEXT FROM contact_crsr INTO @pid, @lname -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt" exec master..xp_cmdshell @cmdtxt FETCH NEXT FROM contact_crsr INTO @pid, @lname END CLOSE contact_crsr DEALLOCATE contact_crsr end
View Replies !
Executing SP Having A Dynamic Cursor Fails In Calling SP
Hi, In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message: Msg 16916, Level 16, State 1, Procedure SP2, Line 114 A cursor with the name 'C1' does not exist. Yes, the cursor is of type GLOBAL. I am sure I miss something here ... Any help is highly appreciated ! Thanks: Peter
View Replies !
Declare Cursor Based On Dynamic Query
Hi, I am declaring the cursor based on a query which is generated dynamically. but it is not working Declare @tempSQL varchar(1000) --- This query will be generated based on my other conditon and will be stored in a variable set @tempsql = 'select * from orders' declare cursor test for @tempsql open test This code is not working. please suggest Nitin
View Replies !
Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure. For sample: In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds. How can I do ??? My code: Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50) SELECT @idRelat = idRelat, @cmd_Det = cmd_DET FROM Relatórios WHERE Nome = @p_Relat Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint, @Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024) Select @Tot_Col = 0 Select @Tot_Lin = 0 Select @Campos = 0 Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma" Select @Aux_Group = "GROUP BY " Select @Aux_Order = "ORDER BY " Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50) Declare cur_Aux insensitive cursor for SELECT Local, Linha, Campo From Relatórios_Margens WHERE (idRelat = @idRelat) ORDER BY Local, Linha Open cur_Aux Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo While @@FETCH_status = 0 begin If @a_Local = "C" Select @Tot_Col = @Tot_Col + 1 Else Select @Tot_Lin = @Tot_Lin + 1 Select @Campos = @Campos + 1 If @Aux_Group <> "GROUP BY " begin Select @Aux_Group = @Aux_Group + ", " If @Aux_Order <> "ORDER BY " begin Select @Aux_Order = @Aux_Order + ", " Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos Select @Aux_Group = @Aux_Group + @a_Campo Select @Aux_Order = @Aux_Order + @a_Campo Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo End Select @Aux_Select = @Aux_Select -- <<<< MONTA COMANDO SQL Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order Declare @Cursor_Aux cursor Set @Cursor_Aux = cursor for @Aux_Select Open @Cursor_Aux Not working !!!!
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 !
Cursor Vs. Select
buddies,situation: a processing must take place on every row of a table, andoutput results to another table, that can't be done via an insertinto..select query (let's assume that it's not possible for now).There're 2 solutions I have in mind:1) open a cursor and cycle through each row (The table can have up to1M rows)2) create a clustered index (i.e on an identity column) then have aloop like:declare @i int, @rows int,@col1 varchar(20), @col2 varchar(20),... @coln varchar(20),@outval1 varchar(20),... -- output valuesselect @i=1, @rows = max(xid) from tblname -- xid is clustered indexedwhile (@i<=@rows)beginselect @col1 = col1, @col2 = col2,...@coln = colnfrom tblnamewhere xid = i-- do the processing on the variables-- then insert results to another tableset @i = @i+1endI'd like to know your ideas of which one would be more efficient. Anyother solutions are much appreciatedthanks,Tamy
View Replies !
Using Select Statement Instead Of Cursor
Hi All, Can anyone please help? TableA has data as below: ssn sex dob rel_code 111111111 m 19500403 m 111111111 f 19570908 w 111111111 f 19770804 d 111111111 f 19801203 d 111111111 f 19869712 d 111111111 m 19870907 s 111111111 m 19901211 s I have to convert the rel_code into a specific manner so the data will look as below in TableB: ssn sex dob rel_code 111111111 m 19500403 01 111111111 f 19570908 02 111111111 f 19770804 20 111111111 f 19801203 21 111111111 f 19869712 22 111111111 m 19870907 30 111111111 m 19901211 31 Member's rel_code = 01 spouse's rel_code = 02 daughter's rel_code starts from 20 with the oldest and increments by 1. Son's rel_code starts from 30 and increments by 1 from oldest to the youngest. I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor. Thanks in advance. Jannat.
View Replies !
Select Statement In Cursor
Hi... I have a stored procedure that rertrieves data from an sql database and sends out a mail to each receipient who meets the criteria I am using SQL mail. I dynamically generate the where clause for my sql query based on criteria taken from other stored procedures and store it in a varchar variable called @sqlquery When i have the following code to run my cursor 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. What should i do. i have tested the variable @sqlquery and it is definately not blank. There is no bracket error or anything. Please help!!! Thanks much indeed Ramesh
View Replies !
Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem on the select statement in sql cursor My select statement is stored in 2 variables one holds the select clause and the other holds the where clause I am doing a small test as my seelct statement is very complicated lots of joins and it is built up from lots of parameters from other queries and from another stored procedure as well Hope you can help when i type the following code: declare @query varchar(100) declare @query2 varchar(100) set @query = "SELECT FROM ml_testMaillist " set @query2 = " WHERE m_Email= 'ramesh@go-events.com' " DECLARE overdue2 CURSOR LOCAL FORWARD_ONLY exec(@query + @query2) open overdue2 I get the error Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'exec'. Please please help as this is very impt to me Thanks Thanks Regards
View Replies !
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 !
Cursor Select And Variables
I have problems to place my variable into the select statement. DECLARE @DB_NAME varchar(64) DECLARE MR_ReqPro_DB_cursor CURSOR FOR select name from dbo.sysdatabases where name like '%MR_req%' OPEN MR_ReqPro_DB_cursor FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME WHILE @@FETCH_STATUS = 0 BEGIN print @DB_NAME; --works fine Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%'); FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME END CLOSE MR_ReqPro_DB_cursor DEALLOCATE MR_ReqPro_DB_cursor GO How could i use a variable like @DB_Name in my select ?
View Replies !
CURSOR Select Clause
I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error: Server: Msg 16924, Level 16, State 1, Line 78 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns. I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor? select colnum, coldesc, colname into #ae_defs from ae_adefs select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') + ', ' + (select colname from #ae_defs where coldesc = 'PATIENT NUMBER') + ', ' + (select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') + ', ' + (select colname from #ae_defs where coldesc = 'VISIT DATE') + ', ' + (select colname from #ae_defs where coldesc = 'VISIT TYPE') + ', DocID from ae_dtl1' DECLARE myCursor CURSOR FOR Select @SQL OPEN myCursor print @@Cursor_rows FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID
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 !
How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone, I have been trying to perform the following task: Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns: Database name Table name Column name from that table with a clustered index I have already created the following script which displays all the databases for a given server: declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name open testCursorForDB fetch next from testCursorForDB into @DBname while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end close testCursorForDB deallocate testCursorForDB I also have created the following query which will display all the table and column names which have a clustered index for a given database: select object_name(i.id) as TableName, i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1' However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor. Any help is greatly appreciated Thanks!
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 !
Incrementally Build Cursor Select Using CASE?
In Access/VB/DAO I can declare a string variable to contain my SELECT statement. If I do this I can build the string incrementally and finally open the cursor using OpenRecordset(strSQL). I'd like to be able to do this in SQL7. Let me illustrate: In Access97: Function Test(ByVal pstrOrder As String) As Boolean Dim strSQL As String strSQL = "SELECT * FROM tblEmployee " Select Case pstrOrder Case "Alpha" strSQL = strSQL & "ORDER BY [LastName], [FirstName]" Case "SSN" strSQL = strSQL & "ORDER BY [SSN]" End Select db.OpenRecordset(strSQL) End Function This is a very simple example....I have a lot of complex SELECT statements and I don't want to retype the same code in the same procedure that does ALMOST the same thing. I'm hoping to use something like the CASE construct: DECLARE csrEmployee CURSOR FOR SELECT * FROM tblEmployee (CASE @pstrOrder WHEN "Alpha" THEN ORDER BY LastName, FirstName WHEN "SSN" THEN ORDER BY SSN END) Can anyone out there help?
View Replies !
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 Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
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 !
Increment In Select Query Without Cursor And Temp Table
I have: Select T0.Id_Roomtype, count(T0.ID_Room)as Total, convert(smalldatetime,'20000601') as ADate --- !!!! from rmRoom T0 where T0.id_Property = 1 group by T0.Id_Roomtype How to do: declare @x int set @x = 36901 Select T0.Id_Roomtype, count(T0.ID_Room)as Total, convert(smalldatetime,(set @x = @x+1)) as ADate --- !!! from rmRoom T0 where T0.id_Property = 1 group by T0.Id_Roomtype I am trying to increment value in Column ADate so to get new date for each row Is it possible without using cursor and temp table ? ht
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
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 !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
Select For Cursor Returns 0 Same Select Highlighted Returns 2,000+
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View Replies !
Select With Dynamic SQL
Here is an example of a script I am attempting to execute... CREATE PROCEDURE test1 AS BEGIN SELECT * FROM table_a DECLARE @sqlstring varchar(300) SELECT @sqlstring = 'SELECT * FROM table_a' EXEC(@sqlstring) END GO GRANT EXECUTE ON test1 TO user1 GO user1 has no rights other than being in the public group. When you run this procedure through Query Analyzer it will give you the recordset from the first select but the second will give you an error stating that select permission is denied on table_a. Can anyone assist me with this issue? Thanks, Pete
View Replies !
Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
View Replies !
Need Dynamic SQL Select Turotrial
Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order. Thank you
View Replies !
' In A Dynamic Select String?
I'm building a select string on the fly based on criteria selected by the user. The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel. Everything is working fine, except when the Name has an ' in it. For example O'Kelly or St. John's. I know I can take all the 's out of the database, but I'd rather keep the data authentic. Is there a way to manipulate a select string built on the fly accounting for an embedded '? For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up. Any ideas? Sample Code:<code> For Each SelectedIndex In rsc.SelectedIndexes Counter=Counter + 1 dgAssociates.SelectedIndex = SelectedIndex If Counter = 1 then SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'" Else SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'" End If Next If Counter > 0 then SelectString &= ")" End If</code>
View Replies !
Select Into Using Dynamic Queries
I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion. Can someone help me out on this. EG sp_executesql N'Select top 1 * into #tmp from table1' select * from #tmp
View Replies !
Select Dynamic Issue
Hi, I have a table with 20 column 10 with the name in1,in2,in3... and 10 more with the name out1,out2,out3... what i'm trying to do is a loop that will substract all column like this sum = out1-in1 sum = sum + (out2-in2) etc etc.. what is your suggestion for the best way doing that. thanks.
View Replies !
Select @@RowCount In Dynamic Sql
hi, how to retrieve row count from an insert statement in dynamic sql? CREATE PROCEDURE [dbo].[procTest] AS BEGIN SET NOCOUNT ON; DECLARE @nsql NVARCHAR(MAX) DECLARE @i INT SET @nsql = 'select * into tbl_user_test from tbl_user; select @i = @@rowcount;' exec sp_executesql @nsql; END The code doesn't work.. declare @i int; Exec procTest, N'@i int output',@i=@i output select @i;
View Replies !
Dynamic SqlDataSource Select Command?
I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form. I have a GridView control and a SqlDataSource. The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page. I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause. I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?
View Replies !
Dynamic SELECT Command In SqlDataSource
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TESTDrivercommunication %>" SelectCommand="usp_reports_shortages" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="lblDriver" Name="date1" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="lblTODate" Name="date2" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="DDlDriver" Name="driver" PropertyName="SelectedValue" Type="Int32" /> <asp:SessionParameter Name="week" SessionField="s_week" Type="DateTime" /> </SelectParameters> </asp:SqlDataSource>
View Replies !
How To Update When Dynamic Select SQL By Using SQLDatasouce In .net 2.0?
In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB. Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand . How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit? thanks a lot.
View Replies !
Convert Variable Into Dynamic Select
I am trying to assign @sql variable to @total, where @sql is a sqlstatement stored on the database, however what I am getting is itsstring value and not its calcuation. Could anybody help?DECLARE my_cursor CURSOR FORSELECT sqlstatement from Sn_SalesReportdeclare @sql varchar(255), @total varchar(20)OPEN my_cursorFETCH NEXT FROM my_cursor INTO @sql-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGINSET @total = (@sql)print @total-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM my_cursorINTO @sqlENDCLOSE my_cursorDEALLOCATE my_cursorI tried this SET @total = EXEC (@sql), but no successRod
View Replies !
Dynamic Select/Update Statement Possible?
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter? For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following... ALTER PROCEDURE u_sp_x @semester int AS Select Semester@semester From ThisTable Just curious. Thanks, Steve Hanzelman
View Replies !
Wrapping A Dynamic Sql Call In A Select
I'm expecting to revamp some stored procs so that their selects are executed on a dynamic string that always returns the same columns but varies the sources. I'm concerned that the bread and butter of products like RS and SSIS is the ability to predict what columns, and what column types to expect from a query, but that introducing dynamic sql will complicate using them. I'm motivated not to use temp tables or table vars if possible. I'm also somewhat motivated to learn of a solution that works equally well in 2000 and 2005. I've tried wrapping dynamic calls in a select as shown below but to no avail... After€¦ declare @sqlString nvarchar(4000) set @sqlString = 'select * from [' + @dbName + '].[dbo].[activity]' I€™ve already tried things like€¦. select a.* from exec sp_executesql @sqlString a and select * from exec (@sqlString)
View Replies !
Dynamic Column Bind (Select * From..)
I'm looking for a way to use SSRS to display the contents of a returned dataset with 1-n columns. The number of columns is unknown at design time and the datasetview is using a stored procedure with 1 parameter to return the data. Does any one know if RDL supports wildcard(*) characters for field names or if there is another method. I have this working via a web based custom aspx page but it would be VERY helpful to utilize all the document conversion features the SSRS reports provide via the web. Thanks for any help. IT Publisher
View Replies !
Dynamic Create Table Statement Or SELECT INTO
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind? Example: I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query. I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database. BTW - this would be a stored procedure... Any suggestions would be greatly appreciated.
View Replies !
|
TRACKER
