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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
DECLARE CURSOR
Is there any way to create a cursor, based on a dynamically created select_statement? Something like: DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect where @strSelect is previously declared as let's say varchar. I don't want to create a stored procedure for this. Thanks!
View Replies !
Help With Declare And Cursor
I keep getting the message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'declare'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'declare'. What am I doing wrong? declare @dbname varchar(8000), declare @countyname varchar (200) , declare @sql varchar(8000) declare county_name cursor for select distinct county from Zipcodes open county_name fetch next from county_name into @countyname declare dbname_name cursor for select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter' open dbname_name fetch next from dbname_name into @dbname WHILE @@FETCH_STATUS = 0 BEGIN set @sql = ' select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+' from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip' exec (@sql) end set @sql = '' fetch next from dbname_name into @dbname fetch next from county_name into @countyname CLOSE county_name DEALLOCATE county_name CLOSE dbname_name DEALLOCATE dbname_name
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 !
Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway? Is Declare Cursor same as Create Cursor and if not what is the major difference?
View Replies !
Building Dynamic Query Based On Dropdownlist Contents
Thanks in advance for taking the tiemt o read this post: I am workingon an application in vb.net 2008 and I have 5 drop down lists on my page. I have code that worked in .net 2005 for my databind but would like to use new features in 08 to do this same thing. Here is my 05 code how would I do this same things in 08? Dim db As New DataIDataContext Dim GlobalSQLstr As String GlobalSQLstr = "select Orig_City, ecckt, typeflag, StrippedEcckt, CleanEcckt, ManualEcckt, Switch, Vendor, FP_ID, order_class, Line_type, id from goode2 where 1=1" If (ddlOrigCity.SelectedValue <> "") Then GlobalSQLstr &= "and Orig_City = '" & ddlOrigCity.SelectedValue & "'" End If If (ddlSwitch.SelectedValue <> "") Then GlobalSQLstr &= "and switch = '" & ddlSwitch.SelectedValue & "'" End If If (ddlType.SelectedValue <> "") Then GlobalSQLstr &= "and Order_Class = '" & ddlType.SelectedValue & "'" End If If (ddlFormatType.SelectedValue <> "9") Then GlobalSQLstr &= "and typeflag = '" & ddlFormatType.SelectedValue & "'" End If If (ddlVendor.SelectedValue <> "") Then GlobalSQLstr &= "and Vendor = '" & ddlVendor.SelectedValue & "'" End IfDim AllSearch = From A In db.GoodEcckts2s If (ddlErrorType.SelectedValue <> "0") Then GlobalSQLstr &= "and ErrorType = '" & ddlErrorType.SelectedValue & "'" End IfDim cmd As New SqlClient.SqlCommand Dim rdr As SqlClient.SqlDataReaderWith cmd.Connection = New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString) .CommandType = Data.CommandType.Text .CommandText = GlobalSQLstr .Connection.Open() rdr = .ExecuteReaderMe.gvResults.DataSource = rdrMe.gvResults.DataBind() .Connection.Close() .Dispose()End With
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
Declare Cursor For Execute Stored_procedure
Hello, I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @Variable). Something like this: DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @Variable i get an error saying "incorrect syntax near the keyword 'EXECUTE'." cannot get rid of the error. what am i doing wrong? (i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl) Not sure if i am doing this right all together. any help would be greatly appreciate.
View Replies !
How To Declare Cursor In Stored Procedure?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure. CREATE PROCEDURE STP_EMPSAL @empno int, @Employee_Cursor CURSOR VARYING OUTPUT FOR SELECT empno FROM AdventureworksDW.dbo.emp AS OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor into @empno; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN UPDATE emp set sal= sal+ 2000 where empno = @empno and comm is null mgr='Scott'; FETCH NEXT FROM Employee_Cursor into @empno; COMMIT; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor;
View Replies !
Setting Dynamic Default Parameter In Dropdown Based On MDX Query
I have a parameter list that is built using an MDX query. I would like to set the default value to the current month. Here is the MDX used. Code Snippet WITH MEMBER [Measures].[ParameterCaption] AS '[Time].[Month].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Time].[Month].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Time].[Month].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Time].[Month].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@TimeYear, CONSTRAINED) ) ON COLUMNS FROM [Pink Flag]) Any help would be appreciated. I have tried to set an expression to build a string that matches the result. Thanks Jason
View Replies !
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 !
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 !
SQLServer Declare New Variable Based On Another Variables Value
Hi, I'm a complete newbie to SQLServer and t-sql generally. What I want to do is create a new variable in a stored procedure based upon the value of another variable. eg in the loop below I want to create 10 new variables, called @var0,@var1,@var2 ...@var9 declare @varname nvarchar(10) declare @i integer select @i=0 while @i<10 begin set @varname = cast(('@var'+cast(@i as char)) as nvarchar(10)) set @i=@i+1 end Does anyone know of a way to do this?
View Replies !
Declare Variables In A SP Dynamic?
Hello there, i have been asked about a thing that, i think, is not possible. But maybe i am wrong. question: Is it possible to have a Stored Procedure in that the declaration of the variables is dynamic? This means, can i get the variable name and the type of it from a database to create a dynamic stored procedure that changes itself by firing a trigger. Thanks for all oppinions and answers. everWantedLINUX
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration. There error generated is: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor Here is the cursor declaration: declare some_cursor CURSOR for select * from part_view FOR UPDATE Any ideas, guys? Thanks in advance for knocking your head against this one. PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
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 !
Cursor Based SQL?
Does anyone have any good references they could recommend on Cursorbased SQL writing? I have to create SQL that can loop though recordssimular to VB loops and I have been told that this is the way to go.Any recommendations would be helpful.
View Replies !
How To Write Set-based SQL Instead Of Cursor
Guys Here's the scenario create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT) insert data1 (dealid , datex , Tn ) values ('12345' , '31-12-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '30-11-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '31-10-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('98765' , '31-12-2005' , 2) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 1) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 0) select * from data1 I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way Any ideas
View Replies !
Cursor, Set Based Approach
I am replacing cursor logic in a SP to a setbased approach to scale better. My setbased approach seems to be better but it runs very fractionaly faster (execution time) than the cursor approach for a single run in test environment. I think resource cost wise, my set based approach should be better. Number of rows iterated thru this cursor is small (0-150). This particular SP is called over 2000 times in production everyday. Is it worth the trouble changing this if we get only marginally benefits, will my set-based approach work better on a server that has lot of activity (lot of connections etc). Our db server runs at about 75-85% cpu usage daily and this particular SP accounts to 13% CPU usage for 2000+ executions. If the data set involved in cursors is small, is it worth the trouble changing them to set based approaches? Am I doing right to change this SP to setbased approach.
View Replies !
Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7 What would be wise to do. I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered) This results in the following sql statement: Code: sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")" I think I have a few options now. 1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber. 2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport. 3. Set the recordsource of the report to constructed sqlStr on Report_open() --------------------- Option 1, I got this working but when the database grows (and it will) this might get awfully slow. Option 2 I had this working before i switched to using MSSQL server 7.00 After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db. Option 3. Fast and easy??? Option 4 Stored procedures???
View Replies !
Avoiding Cursor - Want Set Based Solution
Hi there! Here is my situation: table 'ReceiptHeader' IDCustomerIDDateCreated 1225102/06/2002 1332102/09/2002 1444002/15/2002 table 'ReceiptDiscount' IDDiscountIDReceiptHeaderIDAmount 111210.00 241250.00 311325.00 **a receipt can have multiple discounts** Table 'ReceiptDetail' ReceiptHeaderID LineItemIDTotal 121155.33 131145.33 141241.66 **for this example there is only one line item per receipt** Without using a cursor, i would like to return a result set like this one below using a set based solution... ReceiptIDCustomerIDDiscountTotal 1225160.00155.33 1332125.00145.33 144400.00241.66 Thanks, SF
View Replies !
How To Replace Cursor Based Statements With Set Ba
Hey All, I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. Thanks in advance. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [SampleStoredProc] @Var1varchar(20), @Var2varchar(3), @Var3 varchar(2) = 'Dummy' As declare @selectlist varchar(5000) declare @tableBuild varchar(1000) declare @FieldName varchar(50) declare @FieldSelect varchar(500) declare @FieldTitle varchar(50) declare @TableName varchar(50) declare @holdTable varchar(50) declare @title varchar(50) declare @holdTitle varchar(50) declare @PageName varchar(50) declare @sequence varchar(100) declare @extraCriteria varchar(200) declare @holdCriteria varchar(200) declare @insertSQL varchar(5000) declare @ConvertRoutine varchar(500) declare @loopCtrl1 bit declare @loopCtrl2 bit declare @ConvertSQL varchar(5000) declare @PrevValue varchar(50) declare @NewValue varchar(50) declare @ActionTxtvarchar(1) declare @Descriptionvarchar(20) declare @effDatevarchar(10) declare @transEffDatevarchar(10) declare @expDatevarchar(10) declare @lastTransDatevarchar(10) declare @policyStatusvarchar(2) declare @reasAmendDescvarchar(50) declare @policyNumbervarchar(20) declare @riskStatevarchar(20) declare @PriorPremmoney declare @AmendPremmoney declare @PremDiffmoney declare mtcursor cursor for select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1 where Column1 = @Var2 order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName open mtcursor fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' set @loopCtrl1 = 0 set @loopCtrl2 = 0 WHILE (@loopCtrl1 = 0) begin set @holdTable = @TableName set @holdCriteria = @extraCriteria set @holdTitle = @title if @FieldSelect = '' set @selectlist = @selectlist + ',' + @FieldName else set @selectlist = @selectlist + ',' + @FieldSelect set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)' fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine if @@fetch_status <> 0 set @loopCtrl2 = 1 if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1) begin set @tableBuild = @tableBuild + ')' set @insertSQL = ' declare mtcursor2 cursor for select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1 where TableName = ''' + @holdTable + ''' and ExtraCriteriaTxt = ''' + @holdCriteria + ''' and PageTitleTxt = ''' + @holdTitle + ''' and Column1 = ''' + @Var2 + ''' order by FieldDisplaySequenceNbr declare @FieldName varchar(50) declare @FieldTitle varchar(50) declare @ExtraUpdateMatch varchar(500) declare @PullUpdate bit declare @PullAdd bit declare @PullDelete bit declare @PullAnyUpdate bit open mtcursor2 fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate WHILE (@@fetch_status = 0) begin if substring(@FieldTitle,1,1) = ''#'' set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1) else set @FieldTitle = '''''''' + @FieldTitle + '''''''' if @PullAnyUpdate = 1 begin exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''') end else begin if @PullUpdate = 1 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '') end if @PullAdd = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''') if @PullDelete = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' from #tempTable A where Val1 = ''''D'''''') fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate end close mtcursor2 deallocate mtcursor2' exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL) set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' end if @loopCtrl2 = 1 set @loopCtrl1 = 1 end close mtcursor deallocate mtcursor Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 declare deletecursor cursor for select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U' open deletecursor fetch next from deletecursor into @PageName while @@fetch_status = 0 begin if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' fetch next from deletecursor into @PageName end close deletecursor deallocate deletecursor declare convertcursor cursor for select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a inner join MyTable1 b on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> '' where a.Id = @Var1 open convertcursor fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine while @@fetch_status = 0 begin set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)' set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) ' set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' if @ActionTxt = 'A' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + '''' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' end if @ActionTxt = 'D' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + '''' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' end set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted where EntrySequenceNbr = ''' + @Sequence + '''' exec (@ConvertSQL) fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine end close convertcursor deallocate convertcursor if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 Create table #pageSeqTable (PageTitle varchar(50), PageSeq int) insert into #pageSeqTable select distinct PageTitleTxt, PageDisplaySequenceNbr from MyTable1 where Column1 = @Var2 select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt from MyTable2, #pageSeqTable b where Id = @Var1 and PageNm = b.PageTitle order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101), @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes, @policyNumber = PolicyNumber, @riskState = StateName, @AmendPrem = convert(money,PremiumAmount) from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C where Id = @Var1 AND Val2 = (select max(Val2) from SHPlanInfo where Id = @Var1) AND B.ReasonAmendedCd = A.ReasonAmendedCd AND C.StateCode = A.RiskState Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' Set @PremDiff = @AmendPrem - @PriorPrem select EffectiveDate = @effDate select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate select AmendXPolStat = @policyStatus select ReasonAmendedDes = @reasAmendDesc select PolicyNumber = @policyNumber select RiskState = @riskState select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiffSelect ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
View Replies !
Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.
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 !
Breakout Records Based On UNIT Total Without Cursor
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge: Ex: Units REVCHG FIELD_A FIELD_B ..... 3 3.00 ABCD EFGH Needs to be converted to: Units REVCHG FIELD_A FIELD_B ..... 1 1.00 ABCD EFGH 1 1.00 ABCD EFGH 1 1.00 ABCD EFGH The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea? Thanks.
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 !
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 !
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 Replies !
How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
Hey All, I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. Thanks in advance. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [SampleStoredProc] @Var1 varchar(20), @Var2 varchar(3), @Var3 varchar(2) = 'Dummy' As declare @selectlist varchar(5000) declare @tableBuild varchar(1000) declare @FieldName varchar(50) declare @FieldSelect varchar(500) declare @FieldTitle varchar(50) declare @TableName varchar(50) declare @holdTable varchar(50) declare @title varchar(50) declare @holdTitle varchar(50) declare @PageName varchar(50) declare @sequence varchar(100) declare @extraCriteria varchar(200) declare @holdCriteria varchar(200) declare @insertSQL varchar(5000) declare @ConvertRoutine varchar(500) declare @loopCtrl1 bit declare @loopCtrl2 bit declare @ConvertSQL varchar(5000) declare @PrevValue varchar(50) declare @NewValue varchar(50) declare @ActionTxt varchar(1) declare @Description varchar(20) declare @effDate varchar(10) declare @transEffDate varchar(10) declare @expDate varchar(10) declare @lastTransDate varchar(10) declare @policyStatus varchar(2) declare @reasAmendDesc varchar(50) declare @policyNumber varchar(20) declare @riskState varchar(20) declare @PriorPrem money declare @AmendPrem money declare @PremDiff money declare mtcursor cursor for select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1 where Column1 = @Var2 order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName open mtcursor fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' set @loopCtrl1 = 0 set @loopCtrl2 = 0 WHILE (@loopCtrl1 = 0) begin set @holdTable = @TableName set @holdCriteria = @extraCriteria set @holdTitle = @title if @FieldSelect = '' set @selectlist = @selectlist + ',' + @FieldName else set @selectlist = @selectlist + ',' + @FieldSelect set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)' fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine if @@fetch_status <> 0 set @loopCtrl2 = 1 if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1) begin set @tableBuild = @tableBuild + ')' set @insertSQL = ' declare mtcursor2 cursor for select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1 where TableName = ''' + @holdTable + ''' and ExtraCriteriaTxt = ''' + @holdCriteria + ''' and PageTitleTxt = ''' + @holdTitle + ''' and Column1 = ''' + @Var2 + ''' order by FieldDisplaySequenceNbr declare @FieldName varchar(50) declare @FieldTitle varchar(50) declare @ExtraUpdateMatch varchar(500) declare @PullUpdate bit declare @PullAdd bit declare @PullDelete bit declare @PullAnyUpdate bit open mtcursor2 fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate WHILE (@@fetch_status = 0) begin if substring(@FieldTitle,1,1) = ''#'' set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1) else set @FieldTitle = '''''''' + @FieldTitle + '''''''' if @PullAnyUpdate = 1 begin exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''') end else begin if @PullUpdate = 1 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '') end if @PullAdd = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''') if @PullDelete = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' from #tempTable A where Val1 = ''''D'''''') fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate end close mtcursor2 deallocate mtcursor2' exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL) set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' end if @loopCtrl2 = 1 set @loopCtrl1 = 1 end close mtcursor deallocate mtcursor Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 declare deletecursor cursor for select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U' open deletecursor fetch next from deletecursor into @PageName while @@fetch_status = 0 begin if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' fetch next from deletecursor into @PageName end close deletecursor deallocate deletecursor declare convertcursor cursor for select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a inner join MyTable1 b on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> '' where a.Id = @Var1 open convertcursor fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine while @@fetch_status = 0 begin set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)' set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) ' -- start 33385 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' --end 33385 if @ActionTxt = 'A' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + '''' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' end if @ActionTxt = 'D' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + '''' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' end set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted where EntrySequenceNbr = ''' + @Sequence + '''' exec (@ConvertSQL) fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine end close convertcursor deallocate convertcursor /* LOB-specific data conversions */ if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 -- End issue 33385 Create table #pageSeqTable (PageTitle varchar(50), PageSeq int) insert into #pageSeqTable select distinct PageTitleTxt, PageDisplaySequenceNbr from MyTable1 where Column1 = @Var2 select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt from MyTable2, #pageSeqTable b where Id = @Var1 and PageNm = b.PageTitle order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101), @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes, @policyNumber = PolicyNumber, @riskState = StateName, @AmendPrem = convert(money,PremiumAmount) /* Case 33385 */ from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C where Id = @Var1 AND Val2 = (select max(Val2) from SHPlanInfo where Id = @Var1) AND B.ReasonAmendedCd = A.ReasonAmendedCd AND C.StateCode = A.RiskState Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' Set @PremDiff = @AmendPrem - @PriorPrem select EffectiveDate = @effDate select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate select AmendXPolStat = @policyStatus select ReasonAmendedDes = @reasAmendDesc select PolicyNumber = @policyNumber select RiskState = @riskState select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiff Select ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
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 !
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 Join Based On Column Value??
Hi, I have three tables. The "Master table has a recordid, a masterID, a "IsSubField" and other stuff. I need to do a join to a second table based on the MasterID... However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B. Make sense? Anyone got any pointers? Thanks in advance James
View Replies !
Using A Date Function To Declare A Variable Used In A SQL Query
Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far <script runat="server""> Sub Page_Load dim --all the variables for my sql connections-- dim ff1 As Date then my sql connection and queries sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table) dim ff1 As string ff1="30/01/2006" but I need ff1 to be the current date and is I use ff1 As date it returns time and date Is there any way to set ff1 to the current date in this format "dd/mm/yyyy" Any help is greatly appreciated
View Replies !
Dynamic WHERE Operator Based On User Input
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements: DropDownList1: This drop down list contains 3 choices- "=", ">", and "<". Age: This text box is where someone would enter a number. Button1: This is the form's submit button. I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1. The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error: "The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure." Any help would be appreciated. Here is what I'm using in my code behind: protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }
View Replies !
XML With Dynamic Attributes Based On Column Values
I have been banging my head against the wall for TWO days. I have gone back and forth with a very patient guy on thescripts.com. You can see the ridiculous thread here http://www.thescripts.com/forum/threadnav628777-1-10.html If you have time, at least peruse that so we don't go in circles. Anyway, if you guys can help me solve this, I will be forever grateful!! Here is the "basic" problem: Here is an example for TWO different entities in the database. EntityID XmlFieldName Value 1 City Austin 1 State TX 1 Country US 2 CityName Los Angeles 2 StateCode CA 2 CountryCode US 2 Zip 111111 Here is how the two different results should be where EntityID = 1 <Address City="Austin" State="TX" Country="US"/> where EntityID = 2 <Address CityName="Los Angeles" StateCode="TX" CountryCode="US" Zip="111111"/> Notice how the attribute names (City or CityName, State or StateCode, etc) are based off the XmlFieldName and I don't know in advance what the possible values will be? I also don't know how many attributes there will be, but they can be different per entity, depending on how they have set up an address in our application. Another thing to note, is that I kind of have this working in an sproc using PIVOT and generating a table with the values that have the correct dynamic column names (you can see this on my other thread I posted above) but I REALLY need this to not use dynamic SQL (so can use it in a function) if possible and be able to be used in a select statement, whether it be a temp table as I would like to get a result set back that I can do a FOR XML RAW on. If this is confusing, it is because I am delerious. OR is there a way to return a table from an SPROC that has dynamic columns built? Please help!! Thanks so much!!! Brian
View Replies !
Is Possible To Create Dynamic Reports Based On Cubes?
Hi, I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria. Thanks.
View Replies !
Is Possible To Create Dynamic Reports Based On Cubes?
Hi, I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria. Thanks.
View Replies !
Dynamic Data Set Based On Calculated Fields
Hi, I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following: =iif(Parameters!BorC.Value, First(Fields!MyField.Value, "dataset_B"), First(Fields!MyField.Value, "dataset_C")) Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work: =First(Fields!MyField.Value, "dataset_B") OR =First(Fields!MyField.Value, "dataset_C") This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong. Any advice you have for me would be greatly appreciated! Thanks.
View Replies !
Dynamic CASE Statement Based On List Of Dates
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton Dates Table Date 4/1/2003 1/1/2006 4/2/2007 Fee Table Date Period Class Fee 1 Daily True 329 1 Half Day True 178 1 OT True 49 1 Hourly True 41 1 Daily False 156 1 Half Day False 86 1 OT False 27 1 Hourly False 19 2 Daily True 355 2 Half Day True 192 2 OT True 50 2 Hourly True 44 2 Daily False 171 2 Half Day False 92 2 OT False 28 2 Hourly False 21 3 Daily True 364 3 Half Day True 197 3 OT True 51 3 Hourly True 45 3 Daily False 175 3 Half Day False 94 3 OT False 29 3 Hourly False 21
View Replies !
Dynamic Build SQL In Store Procedure Based On Select
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
View Replies !
How To Design Dynamic Reports Based On User's Choice
Hi all, I'm a beginner to Report Services, and have tons of questions. Here's the first one: if the reports are created based on the condition that the user selects, how can I create the reports with Report Services? For example, the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select. Could someone tell me how to do it (create and deploy the reports)? Thanks a million! Jonee
View Replies !
Dynamic Coloring Of Cells Based On The Multi-value Selction.
Hello All, I am working on a report where in I need coloring of cells based on a Multivalue drop down selection. Scenario: 1.) I have a exception flag as a column/field in the database which list the postion of the Exception and also the Value of the exception. Example: ExcpFlag: 012024504213 Each digit in this ExcpFlag represents two values 0 being the first column in the table and 0 is Good data. 1 being the 2nd column and 1 is null data. 2 is 3rd column and Missing SSN...etc. We have separate list table which list all the exceptions with key and value pairs. ExcpTable has 0 | GoodData 1 | NullData 2 | Missing SSN's and etc. 2.) The Stored Procedure brings me the records based on the exception flag and file ID. Example: Two Parameters to SP. Exec sprcFileException fileID:'1025' and ExcpFlag: '0,1,3,7,9' gives me all the records for that FileID and also the ExcpFlag. 3.) Now what I need is: Dynamic Coloring of the cells which has only '0,1,3,7,9' in the report for the fileID: 1025. I have about 24 fields/columns in the report and data displays for only '0,1,3,7,9' exception types from the selction. Problem: I want to color all the cells which have the '0,1,3,7,9' exceptions on the report. Please help me figuring out this issue. Thanks in Advance. Deepak
View Replies !
Help Cursor Based Stored Procedure Is Getting Slower And Slower!
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View Replies !
|
TRACKER
