July 28, 2009 at 5:11 pm
BEGIN
SELECT @CT = MIN(ct)
FROM ironchef.dbo.war_conferenceregistrations
WHERE eventmeetingsetupid = @eventmeetingsetupid
--Will IS NULL WORK
IF @CT IS NULL
--End of question
BEGIN
INSERT INTO ironchef.dbo.petertesttable
SELECT @eventmeetingsetupid, @eventidname, @createdbyname, @registrationsetupidname, 12 as ct, @extendedamount
FROM ironchef.dbo.filteredeventregistration r
INNER JOIN ironchef.dbo.filteredinvoicedetail d
ON r.invoicedetailid=d.invoicedetailid
WHERE r.meetingsetupid=@eventmeetingsetupid
END
ELSE
BEGIN
INSERT INTO ironchef.dbo.petertesttable --(eventmeeting)
SELECT @eventmeetingsetupid, @eventidname, @createdbyname, @registrationsetupidname, @ct-1, @extendedamount
FROM ironchef.dbo.filteredeventregistration r
INNER JOIN ironchef.dbo.filteredinvoicedetail d ON r.invoicedetailid=d.invoicedetailid
WHERE r.meetingsetupid=@eventmeetingsetupid
AND r.createdon BETWEEN DATEADD(WEEK,-@ct,@meetingstartdate)
AND DATEADD(WEEK, -@ct+1,@meetingstartdate)
END
July 28, 2009 at 5:17 pm
July 28, 2009 at 5:32 pm
John Rowan (7/28/2009)
Did you try it?
DECLARE @var int
IF @Var IS NULL
SELECT 'It Works'
ELSE
SELECT 'Nope'
Doesn't work 🙁 even if I change it to <=0
July 28, 2009 at 6:53 pm
What did you get when you tried it?
When I ran this:
DECLARE @Var int
IF @Var IS NULL
SELECT 'It Works'
ELSE
SELECT 'Nope'
it returned It Works on my system at home.
July 28, 2009 at 9:36 pm
Why don't you use
IF Not Exists (
SELECT ct
FROM ironchef.dbo.war_conferenceregistrations
WHERE eventmeetingsetupid = @eventmeetingsetupid
)
This has the advantage that it will stop execution as soon as it finds any row that meets the criteria where your solution has to pull all the results to ensure that it has the min.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 29, 2009 at 9:27 am
Iron Chef SQL (7/28/2009)
John Rowan (7/28/2009)
Did you try it?
DECLARE @var int
IF @Var IS NULL
SELECT 'It Works'
ELSE
SELECT 'Nope'
Doesn't work 🙁 even if I change it to <=0
What do you mean by "Doesn't work"? Do you get an error, does it not return the expected value? What happens when you run it?
July 29, 2009 at 9:28 am
for some reason my if condition (to test if NULL then INSERT) inserts data no matter what.
I'm trying to figure out why but maybe it's something simple and another pair of eyes can see what I can't
USE ironchef
GO
DECLARE @eventmeetingsetupid NVARCHAR(38)
DECLARE @eventidname NVARCHAR(55)
DECLARE @meetingstartdate DATETIME
DECLARE @CT SMALLINT
DECLARE @createdbyname NVARCHAR(55)
DECLARE @registrationsetupidname NVARCHAR(55)
DECLARE @extendedamount SMALLMONEY
DECLARE cursor_newevents CURSOR FOR
SELECT eventmeetingsetupid,
eventidname,
meetingstartdate
FROM ironchef.dbo.EventMeetingSetup
WHERE meetingstartdate > GETDATE()
AND meetingstartdate <= dateadd(week, 12, getdate())
AND nace_broadcategory 2
ORDER BY meetingstartdate
OPEN cursor_newevents
FETCH NEXT FROM cursor_newevents INTO @eventmeetingsetupid, @eventidname, @meetingstartdate
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @eventidname + ' ' + ' - ' + CONVERT(NVARCHAR(10), @meetingstartdate, 101)
DECLARE cursor_wkOut CURSOR FOR
SELECT ct
FROM ironchef.dbo.user_countto52
WHERE ct 0
BEGIN
PRINT 'IT''S NOT NULL'
INSERT INTO ironchef.dbo.petertesttable --(eventmeeting)
SELECT @eventmeetingsetupid, @eventidname, @createdbyname, @registrationsetupidname, @ct-1, @extendedamount
FROM ironchef.dbo.eventregistration r
INNER JOIN ironchef.dbo.invoicedetail d ON r.invoicedetailid=d.invoicedetailid
WHERE r.meetingsetupid=@eventmeetingsetupid
AND r.createdon BETWEEN DATEADD(WEEK,-@ct,@meetingstartdate)
AND DATEADD(WEEK, -@ct+1,@meetingstartdate)
END
ELSE IF @CT IS NULL
BEGIN
PRINT 'IT''S NULL'
INSERT INTO ironchef.dbo.petertesttable
SELECT @eventmeetingsetupid, @eventidname, @createdbyname, @registrationsetupidname, 12 as ct, @extendedamount
FROM ironchef.dbo.eventregistration r
INNER JOIN ironchef.dbo.invoicedetail d
ON r.invoicedetailid=d.invoicedetailid
WHERE r.meetingsetupid=@eventmeetingsetupid
END
--End of Area
FETCH NEXT FROM confreghistcursor INTO @createdbyname, @registrationsetupidname, @CT, @extendedamount
END
CLOSE confreghistcursor
DEALLOCATE confreghistcursor
FETCH NEXT FROM cursor_wkout into @CT
END
CLOSE cursor_wkout
DEALLOCATE cursor_wkout
FETCH NEXT FROM cursor_newevents INTO @eventmeetingsetupid, @eventidname , @meetingstartdate
END
CLOSE cursor_newevents
DEALLOCATE cursor_newevents
July 29, 2009 at 9:29 am
John Rowan (7/29/2009)
Iron Chef SQL (7/28/2009)
John Rowan (7/28/2009)
Did you try it?
DECLARE @var int
IF @Var IS NULL
SELECT 'It Works'
ELSE
SELECT 'Nope'
Doesn't work 🙁 even if I change it to <=0
What do you mean by "Doesn't work"? Do you get an error, does it not return the expected value? What happens when you run it?
It works until I add it to my code
July 29, 2009 at 9:39 am
I can't help but comment on the other few threads that you've started regarding advice on using cursors. Specifically, you asked in one thread about the use of nested cursors and were advised not only not to, but that there's really no reason why you would ever need/want to.
The advice given was from some of the industries best so I've got to ask, what are you trying to accomplish here? If your goal is to learn how to use T-SQL and write effective, efficient, SQL code, you need to learn how to write SET based code (think about working on columns instead of rows).
So what's up? In the big picture, what are you trying to do here?
July 29, 2009 at 9:42 am
Please tell me that you ARE NOT using this cursor-bassed code in a production environment and that it is simply for your own edification.
July 29, 2009 at 9:43 am
John Rowan (7/29/2009)
I can't help but comment on the other few threads that you've started regarding advice on using cursors. Specifically, you asked in one thread about the use of nested cursors and were advised not only not to, but that there's really no reason why you would ever need/want to.The advice given was from some of the industries best so I've got to ask, what are you trying to accomplish here? If your goal is to learn how to use T-SQL and write effective, efficient, SQL code, you need to learn how to write SET based code (think about working on columns instead of rows).
So what's up? In the big picture, what are you trying to do here?
I'm just trying to master cursors just to be able to say I can write a cursor. I honestly can't see why anyone would use a cursor other than some admin tasks.
Basically there is no real thing I'm trying to accomplish. Just trying to create a puzzle and solve it. Brain teaser I guess.
July 29, 2009 at 9:46 am
Iron Chef SQL (7/29/2009)
John Rowan (7/29/2009)
I can't help but comment on the other few threads that you've started regarding advice on using cursors. Specifically, you asked in one thread about the use of nested cursors and were advised not only not to, but that there's really no reason why you would ever need/want to.The advice given was from some of the industries best so I've got to ask, what are you trying to accomplish here? If your goal is to learn how to use T-SQL and write effective, efficient, SQL code, you need to learn how to write SET based code (think about working on columns instead of rows).
So what's up? In the big picture, what are you trying to do here?
I'm just trying to master cursors just to be able to say I can write a cursor. I honestly can't see why anyone would use a cursor other than some admin tasks.
Basically there is no real thing I'm trying to accomplish. Just trying to create a puzzle and solve it. Brain teaser I guess.
I just wanted to thank yall by the way. It's an honor to be able to learn from some of the best. 😎
July 29, 2009 at 9:50 am
My professional opinion would be that you're time would be better spent mastering non-cursor solutions and any other effort is wasting your time. Why spend so much time working towards something that you admit is not of real value. If you are not a master of set based logic, focus your time on that. Here's a challenge for you, take the code that you've got here and write a set based solution, post your work, and we'll help you with that.
As for the original question for this thread, yes, an IS NULL check works. If your code does not appear to work with it, you need to check your variable values. The logic I posted works, every time. So if it appers not to work after you've inserted your code, there's something amiss with your code and most likely with your variable values.
Good advice, you don't take a step in a direction, you always move towards something. So spending time writing procedural code for 'practice' means that you'll eventually use is as a solution in the real world as soon as you can't think of a set based way to solve the problem. Just my $.02.
July 29, 2009 at 10:08 am
I have to agree with John here. Don't waste your time learning more about cursors. Take what you have written and develop a set-based solution to the problem. This will benefit you more in the future than learning to write cursor-based solutions.
Looking at what you have written, it appears to be three nested cursors. This will definately not scale well at all. A set-based solution would actually be the best option for somethng like this.
July 29, 2009 at 10:15 am
On the other side, to really know what is going on with your code, we would need the DDL for the tables, sample data for the tables, and expected results based on the sample data.
So, if you still want to know what is going on with this cursor-based solution, please read and following the instructions/guidelines detailed in the first article I have referenced below in my signature block regarding asking for assistance.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply