November 23, 2009 at 10:43 am
any one no where to place these 3 below statements in a CURSOR to find get info on the cursor
seems ive placed them everywhere and never seems to give me info
I know never a cursor , but I was asked to!
SELECT @@CURSOR_ROWS AS '@@CURSOR_ROWS- Returns the numbers of rows in the last open cursor'
SELECT @@FETCH_STATUS AS '@@FETCH_STATUS- Rts the Status of the last fecth operation against the cursor'
SELECT @@ROWCOUNT AS '@@ROWCOUNT- Returns # of rows that were affected by the last statement executed'
the meat of the cursor has been taken out:
DECLARE
@ProgramCode VARCHAR(7),
@StateCode VARCHAR(2),
@Ins_CompanyCode VARCHAR(2),
@ContractCode VARCHAR(3),
@EffectiveDate_NB SMALLDATETIME,
@ExpirationDate_NB SMALLDATETIME,
@EffectiveDate_RN SMALLDATETIME,
@ExpirationDate_RN SMALLDATETIME,
@EN_AddInsured VARCHAR(75),
@EN_Alarm VARCHAR(75),
@EN_AnimalLiability VARCHAR(75) ,
@EN_CovA VARCHAR(75),
@EN_CovB VARCHAR(75),
@EN_CovC VARCHAR(75),
@EN_CovLU VARCHAR(75),
@EN_CovLA VARCHAR(75),
@EN_Cart VARCHAR(75),
@EN_EQ VARCHAR(75),
@EN_Flood VARCHAR(75),
@EN_InflationGuard VARCHAR(75),
@EN_OrdinanceLaw VARCHAR(75),
@EN_PersonalInjury VARCHAR(75),
@EN_PersonalLiabilty VARCHAR(250),
@EN_Rental VARCHAR(75),
@EN_ReplacementCost_CovA VARCHAR(75),
@EN_ReplacementCost_CovC VARCHAR(75),
@EN_Theft VARCHAR(75),
@EN_UnderConstruction VARCHAR(75),
@EN_WaterDamage VARCHAR(75),
@EN_WaterBackUp VARCHAR(75),
@EN_WDR VARCHAR(75),
@VersionDescription VARCHAR(75),
@VersionComments VARCHAR(100)
DECLARE Crs_SysWordings CURSOR
FOR
SELECT
ProgramCode,
StateCode,
Ins_CompanyCode,
ContractCode,
EffectiveDate_NB,
ExpirationDate_NB,
EffectiveDate_RN,
ExpirationDate_RN,
EN_AddInsured,
EN_Alarm,
EN_AnimalLiability,
EN_CovA,
EN_CovB,
EN_CovC,
EN_CovLU,
EN_CovLA,
EN_Cart,
EN_EQ,
EN_Flood,
EN_InflationGuard,
EN_OrdinanceLaw,
EN_PersonalInjury,
EN_PersonalLiabilty,
EN_Rental,
EN_ReplacementCost_CovA,
EN_ReplacementCost_CovC,
EN_Theft,
EN_UnderConstruction,
EN_WaterDamage,
EN_WaterBackUp,
EN_WDR,
VersionDescription,
VersionComments
FROM SeaSys.dbo.SysWordings
--WHERE
--ORDER BY EN_Description
OPEN Crs_SysWordings
FETCH NEXT FROM Crs_SysWordings INTO
@ProgramCode,
@StateCode,
@Ins_CompanyCode,
@ContractCode,
@EffectiveDate_NB,
@ExpirationDate_NB,
@EffectiveDate_RN,
@ExpirationDate_RN,
@EN_AddInsured,
@EN_Alarm,
@EN_AnimalLiability,
@EN_CovA,
@EN_CovB,
@EN_CovC,
@EN_CovLU,
@EN_CovLA,
@EN_Cart,
@EN_EQ,
@EN_Flood,
@EN_InflationGuard,
@EN_OrdinanceLaw,
@EN_PersonalInjury,
@EN_PersonalLiabilty,
@EN_Rental,
@EN_ReplacementCost_CovA,
@EN_ReplacementCost_CovC,
@EN_Theft,
@EN_UnderConstruction,
@EN_WaterDamage,
@EN_WaterBackUp,
@EN_WDR,
@VersionDescription,
@VersionComments
WHILE @@FETCH_STATUS = 0 BEGIN
If @EN_AddInsured <> '' AND @EN_AddInsured IS NOT NULL BEGIN
Insert Into SeaSys_20090713.dbo.SysWordings_Optional_EN
SELECT
@ProgramCode AS ProgramCode,
@StateCode AS StateCode,
@Ins_CompanyCode AS Ins_CompanyCode,
@ContractCode AS ContractCode,
@EffectiveDate_NB AS EffectiveDate_NB,
@ExpirationDate_NB AS ExpirationDate_NB,
@EffectiveDate_RN AS EffectiveDate_RN,
@ExpirationDate_RN AS ExpirationDate_RN,
'' AS DependentParameter_Name,
'' AS DependentParameter_Operator,
'' AS DependentParameter_Value
FETCH NEXT FROM Crs_SysWordings INTO
@ProgramCode,
@StateCode,
@Ins_CompanyCode,
@ContractCode,
@EffectiveDate_NB,
@ExpirationDate_NB,
@EffectiveDate_RN,
@ExpirationDate_RN,
@EN_AddInsured,
@EN_Alarm,
@EN_AnimalLiability,
@EN_CovA,
@EN_CovB,
@EN_CovC,
@EN_CovLU,
@EN_CovLA,
@EN_Cart,
@EN_EQ,
@EN_Flood,
@EN_InflationGuard,
@EN_OrdinanceLaw,
@EN_PersonalInjury,
@EN_PersonalLiabilty,
@EN_Rental,
@EN_ReplacementCost_CovA,
@EN_ReplacementCost_CovC,
@EN_Theft,
@EN_UnderConstruction,
@EN_WaterDamage,
@EN_WaterBackUp,
@EN_WDR,
@VersionDescription,
@VersionComments
END --while
CLOSE Crs_SysWordings
DEALLOCATE Crs_SysWordings
November 23, 2009 at 10:47 am
Well, I do need to ask a question. Why are using a cursor? Depending on what you are doing, it is probably 99.9% possible to replace your cursor with a set-based process that would perform better and more scalable as well.
November 23, 2009 at 11:19 am
Lynn, when I got into this company 5 months back , and it is and insurance brokerage doing well but small (30 employees), they had 2 IT personnel, I suggested on my first CURSOR assignment that CURSORS where not at all popular, but that is the way they have written so many
sp's already ,they do not want to change nor would they want me to proove to them how using a default set base I think you called it was much better. I would want know how to do it as you have offered to show me know a while back, but it would only be for my skillset. they ,my boss and the VP of IT, do not want to change the way the write T-SQL code.
If I did go ahead and show them a comparison written your way and a their Cursor way, what would be the biggest advantage? less code? runs faster?
I cant show them till I learn how to do it your way, and it would have to be compelling enough for them to let me write it that way in the future.
much thanks adam
To learn what cursors where doing I made a couple test tables and made the most basic Cursor one I could think of.
here it is, it is just taking the values in a record with covA,CovB,and CovC and puting a new record in a temp table
based on if their is a value in any of those 3 columns, if so write a record in the temp table for it. I noticed they were doing them almost to Pivot kind of the data to another table.
USE CursorCastle
SET @CURSORsWhileCounter = 0
DECLARE @TempTable table (
Program_ID varCHAR(4) NULL,
Premium varchar(22)NULL,
PolicyEffectiveDate datetime,
CovA varchar(50)NULL,
CovB varchar(50)NULL,
CovC varchar(50)NULL
)
-----above temp table creation----------------------------------------
DECLARE
@Program_ID varchar(4),
@Premium Money,
@PolicyEffectiveDate datetime,
@CovA Money,
@CovB Money,
@CovC Money
DECLARE Crs_Castles_Insurance CURSOR
FOR
SELECT
Program_ID,
Premium,
PolicyEffectiveDate,
CovA,
CovB,
CovC
FROM dbo.Castles_Insurance
WHERE PolicyEffectiveDate >= '1.1.2008'
ORDER BY Program_ID
OPEN Crs_Castles_Insurance
FETCH NEXT FROM Crs_Castles_Insurance INTO
@Program_ID,
@Premium,
@PolicyEffectiveDate,
@CovA,
@CovB,
@CovC
WHILE @@FETCH_STATUS = 0 BEGIN
If (@CovA <> 0) BEGIN
Insert Into @TempTable
SELECT
@Program_ID,
CONVERT(varchar(10),@PolicyEffectiveDate,101) AS PolicyEffectiveDate,
@Premium,
@CovA,
@CovB,
@CovC
.
END --the if/begin block
If (@CovB <> 0) BEGIN
Insert Into @TempTable
SELECT
@Program_ID,
CONVERT(varchar(10),@PolicyEffectiveDate,101) AS PolicyEffectiveDate,
@Premium,
@CovA,
@CovB,
@CovC
END --the if/begin block
If (@CovC <> 0) BEGIN
Insert Into @TempTable
SELECT
@Program_ID,
CONVERT(varchar(10),@PolicyEffectiveDate,101) AS PolicyEffectiveDate,
@Premium,
@CovA,
@CovB,
@CovC
END --the if/begin block
FETCH NEXT FROM Crs_Castles_Insurance INTO
@Program_ID,
@Premium,
@PolicyEffectiveDate,
@CovA,
@CovB,
@CovC
END --while
CLOSE Crs_Castles_Insurance
DEALLOCATE Crs_Castles_Insurance
--to see results and compare the 2 tables
SELECT Program_ID,
Premium,
PolicyEffectiveDate,
CovA,
CovB,
CovC AS CovCTemp
From @TempTable
SELECT * FROM dbo.Castles_Insurance
November 23, 2009 at 11:32 am
adam spencer (11/23/2009)
(smipped...)If I did go ahead and show them a comparison written your way and a their Cursor way, what would be the biggest advantage? less code? runs faster?
I cant show them till I learn how to do it your way, and it would have to be compelling enough for them to let me write it that way in the future.
much thanks adam
Adam, the quick answer is that you could accomplish the same thing in a fraction of the time, while using fewer resources AND writing less code. As a quick estimate - several hundred times faster.
Also for what it's worth - your code is not pivoting at all. It's just putting in duplicate records if several of A, B and C are <>0.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 23, 2009 at 11:40 am
here is my basic table structure I used to pull data out using the above cursor if you get a chance to show hoe to do it your way.
As you can see at the top of the cursor script in my this post is a CREATE @temptable to dump it into.
USE [CursorCastle]
GO
/****** Object: Table [dbo].[Castles_Insurance] Script Date: 11/23/2009 13:29:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Castles_Insurance](
[Static] [varchar](50) NULL,
[Program_ID] [char](4) NULL,
[PolicyEffectiveDate] [datetime] NULL,
[Premium] [money] NULL,
[Total] [money] NULL,
[Limit] [money] NULL,
[covA] [money] NULL,
[covB] [money] NULL,
[covC] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
---------------------------------------------------------------------------------------
Lynn, below is the insert structure, my boss is kinda watching so I cannot
give you the values write now but basically in 1 insert I put:
CovA=500,CovB=200,CovC=100
next insert I put CovA=500,CovB=0,CovC=100
next insert I put CovA=500,CovB=200,CovC=0
------------------------------------------------------------------------------
INSERT INTO [CursorCastle].[dbo].[Castles_Insurance]
([Static]
,[Program_ID]
,[PolicyEffectiveDate]
,[Premium]
,[Total]
,[Limit]
,[covA]
,[covB]
,[covC])
VALUES
(<Static, varchar(50),>
,<Program_ID, char(4),>
,<PolicyEffectiveDate, datetime,>
,<Premium, money,>
,<Total, money,>
,<Limit, money,>
,<covA, money,>
,<covB, money,>
,<covC, money,>)
INSERT INTO [CursorCastle].[dbo].[Castles_Insurance]
([Static]
,[Program_ID]
,[PolicyEffectiveDate]
,[Premium]
,[Total]
,[Limit]
,[covA]
,[covB]
,[covC])
VALUES
(<Static, varchar(50),>
,<Program_ID, char(4),>
,<PolicyEffectiveDate, datetime,>
,<Premium, money,>
,<Total, money,>
,<Limit, money,>
,<covA, money,>
,<covB, money,>
,<covC, money,>)
INSERT INTO [CursorCastle].[dbo].[Castles_Insurance]
([Static]
,[Program_ID]
,[PolicyEffectiveDate]
,[Premium]
,[Total]
,[Limit]
,[covA]
,[covB]
,[covC])
VALUES
(<Static, varchar(50),>
,<Program_ID, char(4),>
,<PolicyEffectiveDate, datetime,>
,<Premium, money,>
,<Total, money,>
,<Limit, money,>
,<covA, money,>
,<covB, money,>
,<covC, money,>)
Adam
November 23, 2009 at 12:46 pm
I don't have time right now, as I am at work, but if others will let me I'll take what you have given here and work up a set-based solution as well as put together a test suite for your tables. Everything I write I'll give to you here on ssc. This way you will have something to work with as well as to show your supervisor, should you think it worthwhile.
November 23, 2009 at 12:49 pm
I do have one request, please put together a small dataseet for each of your tables along with expected results based on the sample data so that when I put together a larger test suite I can be sure that I am doing things correctly. I'd say between 10 to 20 rows of data depending on the tables and the relationships between them. Parent tables could have fewer and child tables more.
November 23, 2009 at 12:50 pm
Something like this ?
with cteIns
(
Select Program_ID,
Premium,
PolicyEffectiveDate,
CovA,
CovB,
CovC,
Case when n = 1 and CovA <>0 then 'Y' else 'N' end as A,
case when n = 2 and CovB <>0 then 'Y' else 'N' end as B,
case when n = 3 and CovC <>0 then 'Y' else 'N' end as C
FROM dbo.Castles_Insurance cross apply (select 1 as n union select 2 union select 3) as n
WHERE PolicyEffectiveDate >= '1.1.2008'
)
Insert Into @TempTable
SELECT Program_ID,
Premium,
CONVERT(varchar(10),PolicyEffectiveDate,101) AS PolicyEffectiveDate,
CovA,
CovB,
CovC
from cteIns
where A='Y' or B ='Y' or C='Y'
November 23, 2009 at 11:00 pm
Dave Ballantyne (11/23/2009)
Something like this ?
Nicely done Dave. 🙂 I'll just bet that the OP's bosses will remain unswayed, though, because they probably can't figure out how it equates to the cursor. :hehe: They probably don't know how to test for performance, either. I don't envy Adam.
Hey Adam! Would a simple test of counting from 1 to a million sway your bosses away from cursors? If not, it's pretty much a waste of time trying to convince them to convert especially since you don't know how to do the conversions, either. Even if you did the conversions, they probably wouldn't see a speed advantage... you did say that although it was a successful shop, it was small. Cursors are probably hacking the load with no problem on a small amount of data...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2010 at 6:04 pm
Lynn and All, thank you for your help. I quit my job, moved my wife and I to Austin Texas (Texas is ranked high as having IT/IS jobs and one of the lowest unemployement rates.) I am working on my development skills to sql server because I am getting so many calls asking about my C#,vb.net,asp.net skills to go along with my T-sql and ssis experience. I just go further and further away from getting my dream sql dba job. But, I really enjoy development too, it is just that my last 6 years I was on the admin side of things and I feel I am starting over again developing. There are so many more jobs for SQL server back end and front end devopment than DBAs. Well I have no question here for you all, I just wanted to say thanks for helping me in that last cruddy position in Hilton Head. I am sure to work for small companies (40 employess) can be great sometimes, but when the leadership make you dread going to work, its not. I got a side gig to build a web program here in Austin. I will of course be using SQL server for the db!
January 28, 2010 at 8:16 pm
adam spencer (1/28/2010)
Lynn and All, thank you for your help. I quit my job, moved my wife and I to Austin Texas (Texas is ranked high as having IT/IS jobs and one of the lowest unemployement rates.) I am working on my development skills to sql server because I am getting so many calls asking about my C#,vb.net,asp.net skills to go along with my T-sql and ssis experience. I just go further and further away from getting my dream sql dba job. But, I really enjoy development too, it is just that my last 6 years I was on the admin side of things and I feel I am starting over again developing. There are so many more jobs for SQL server back end and front end devopment than DBAs. Well I have no question here for you all, I just wanted to say thanks for helping me in that last cruddy position in Hilton Head. I am sure to work for small companies (40 employess) can be great sometimes, but when the leadership make you dread going to work, its not. I got a side gig to build a web program here in Austin. I will of course be using SQL server for the db!
Heh... I wondered what the heck happened to you. I have to say that you've certainly got the right attitude!
Good luck to you and your wife in this major change in your life. You're welcome here anytime.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply