Replace cursor with while loop / compare cursor with while loop / optimize cursor

Question

Replace cursor with while loop / compare cursor with while loop / optimize cursor

Viewing 15 posts - 31 through 44 (of 44 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Aaron Bertrand has also blogged about it here.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

clive-421796 (12/10/2009)
-------------------------
Have a question for you:
What if for a SQL report you want to use @StartDate AND @EndDate parameters, but also have the option for @StartHour AND @EndHour. How would you implement this. The default would be @StartDate AND @EndDate. If @StartHour AND @EndHour is chosen, then it will override @StartHour AND @EndHour.

Could you provide a sample of what you are describing here? Do mean to say that @StartDate and @EndDate get overridden?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Lynn Pettis (12/10/2009)
Jeff Moden (12/10/2009)
clive-421796 (12/10/2009)
Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to this URL. http://www.win-rar.com/download.html (1.4 MB)
Heh... you're the one that needs the help... upload it as a zip file and I'll be happy to take a look at it. 😉
Jeff, just curious, what package do you use?

Pro version of WinZip... BUT... I just got done rebuilding the machine from the ground up and I don't have it installed yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (12/10/2009)
Lynn Pettis (12/10/2009)
Jeff Moden (12/10/2009)
clive-421796 (12/10/2009)
Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to this URL. http://www.win-rar.com/download.html (1.4 MB)
Heh... you're the one that needs the help... upload it as a zip file and I'll be happy to take a look at it. 😉
Jeff, just curious, what package do you use?
Pro version of WinZip... BUT... I just got done rebuilding the machine from the ground up and I don't have it installed yet.

Okay. I was just wondering. Like I said, I have PKZip at home (paid for version) and I just used 7zip to extract it also (have that at work for doing command line archiving of files for ftping to other sites).

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 5

Hi Jeff, I dont use winzip, I use winrar. Tried to download winzip, guess what ?

version 14 (latest) is 37 MB. So i looked for winzip 9 (about 4MB), every site I try redirects me to winzip 14 download, one link stated winzip, when trying to download it started downloading winrar.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

clive-421796 (12/10/2009)
Hi Lynn, instead of using:
WHERE
tbl.YourDateColumn >= @StartDate
AND tbl.YourDateColumn < @EndDate
I prefer to use:
WHERE
tbl.YourDateColumn between @StartDate AND @EndDate.
-------------------------
Have a question for you:
What if for a SQL report you want to use @StartDate AND @EndDate parameters, but also have the option for @StartHour AND @EndHour. How would you implement this. The default would be @StartDate AND @EndDate. If @StartHour AND @EndHour is chosen, then it will override @StartHour AND @EndHour.

You really need to be careful using that, you may find you miss data or get some extra data. The method I just showed will always work giving you the data you request wether for a single day, a month, a quarter, a year, etc. It will return everything greater than or equal to the start of the period to everything less than the start of the next period.

For example, say that when you first wrote your query YourDateColumn contained data in the format yyyy-mm-dd 00:00:00.000, so you created your monthly query to look like this

WHERE

tbl.YourDateColumn BETWEEN @StartDate AND @EndDate --where @StartDate = '2009-11-01' @EndDate = '2009-11-30'

A few months later, the application is changed and now this column starts getting data that looks like yyyy-mm-dd HH:mm:ss.hhh.

If you don't change your query to account for the change in data, you miss everything entered after midnight on the last day.

If it had been coded:

WHERE

tbl.YourDateColumn >= @StartDate -- @StartDate = '2009-11-01'

AND tbl.YourDateColumn < @EndDate -- @EndDate = ''2009-12-01'

You wouldn't have that problem. A change in how data was captured wouldn't force a change in how you select data.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 7

Hi Circue

Usually you have @StartDate AND @EndDate parameters.

Now I want to also add @StartHour AND @EndHour.

The default would still be @StartDate AND @EndDate.

But if @StartHour AND @EndHour is chosen on the SQL report (the values populated by lets say 18 (18 PM) for @StartHour and say 5 (5 AM) for @EndHour,

then it should override both @StartDate AND @EndDate.

This way the either the user can select after hours for report or if you schedule the report you have the option for work hours or after hours.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 8

Hi Lynn,

the procs we have use the following variations:

@StartDate = '2009-11-01'

@StartDate = '2009-11-01 12:00:00'

@EndDate = '2009-12-01'

@EndDate = '2009-12-01 23:59:59'

2 months ago I had to use your method below:

WHERE

tbl.YourDateColumn >= @StartDate -- @StartDate = '2009-11-01'

AND tbl.YourDateColumn < @EndDate -- @EndDate = ''2009-12-01'

to get the correct data, luckily I always test procs/views for correct data returned, so I picked up the problem. Another issue I found is that some DBA's or developers used

WHERE table1.SUBSACC = isnull(@SUBSACC, table1.SUBSACC),

this is used to allow null values in SQL report. The catch is that it returns data for some customers, so if you test on one customer and you're not sure what data to get back you might think i works, whereas its not. By changing it back to WHERE table1.SUBSACC = @SUBSACC, it worked.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

clive-421796 (12/10/2009)
Hi Lynn,
the procs we have use the following variations:
@StartDate = '2009-11-01'
@StartDate = '2009-11-01 12:00:00'
@EndDate = '2009-12-01'
@EndDate = '2009-12-01 23:59:59'
2 months ago I had to use your method below:
WHERE
tbl.YourDateColumn >= @StartDate -- @StartDate = '2009-11-01'
AND tbl.YourDateColumn < @EndDate -- @EndDate = ''2009-12-01'
to get the correct data, luckily I always test procs/views for correct data returned, so I picked up the problem. Another issue I found is that some DBA's or developers used
WHERE table1.SUBSACC = isnull(@SUBSACC, table1.SUBSACC),
this is used to allow null values in SQL report. The catch is that it returns data for some customers, so if you test on one customer and you're not sure what data to get back you might think i works, whereas its not. By changing it back to WHERE table1.SUBSACC = @SUBSACC, it worked.

This one, @EndDate = '2009-12-01 23:59:59' will work on a smalldatetime but it will potentially fail on datetime and datetime2 (new date/time data type in SQL Server 2008).

One thing I have learned, code defensively. What you are told will never change has a funny way of changing.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

clive-421796 (12/10/2009)
Hi Jeff, I dont use winzip, I use winrar. Tried to download winzip, guess what ?
version 14 (latest) is 37 MB. So i looked for winzip 9 (about 4MB), every site I try redirects me to winzip 14 download, one link stated winzip, when trying to download it started downloading winrar.

BWAA-HAA!!! Now I'll play the same fiddle you played for me... :w00t:

Can't create a .zip (zip file) file? Ah, come on Clive, never heard of anyone working in IT for years that does not have a .zip file maker installed. :-P:hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

BETWEEN includes the endpoints, so you might wind up with an extra day's worth of data using that if your columns don't contain time values.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 12

clive-421796 (12/10/2009)
Hi Lynn, instead of using:
WHERE
tbl.YourDateColumn >= @StartDate
AND tbl.YourDateColumn < @EndDate
I prefer to use:
WHERE
tbl.YourDateColumn between @StartDate AND @EndDate.
-------------------------
Have a question for you:
What if for a SQL report you want to use @StartDate AND @EndDate parameters, but also have the option for @StartHour AND @EndHour. How would you implement this. The default would be @StartDate AND @EndDate. If @StartHour AND @EndHour is chosen, then it will override @StartHour AND @EndHour.

Use DATETIME data types for both, add an hour to the EndDate using DATEADD and use the >= and < thingy the same way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 13

Lynn Pettis (12/10/2009)
What you are told will never change has a funny way of changing.

Never Say Never!

(Hehe, sorry, couldn't resist, but hopefully both the people that get that joke will share my amusement =))

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 14

Hi Lynn, can you shed some light !!!

DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

@liTimeOffset INT

SET @liTimeOffset = 120

SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

FROM mytable

WHERE

LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

AND

LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

LocationDateTime = '2009-11-01 08:13:02' (this is an example).

@StartHour and @EndHour is input parameters for stored procedure. When entering

2009-11-29 (@StartDate), 2009-11-30 (@EndDate), 22:00 (@StartHour), 03:00 (@EndHour)

the above works perfectly but I have one problem, if I want to use it as input parameters for SQL reporting services report it gives no result when I specify @StartHour and @EndHour as datetime or Integer. If I specify as string it does return results, but more rows are returned that it should.

How do I resolve issue ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

How about some sample data and what it should look like? In this case, you can show me what the parameters to the WHERE clause should be based on the sample data.

Just from your post, not a clue.