January 9, 2008 at 9:55 am
Hi all,
I'm kinda new to the SP scene and was wondering if someone could give me advice on the following piece of code or critique if you will:
IF ( @tlCount = 0 AND @status >= 1400 ) BEGIN
SET @tlCount = 1
SELECT @bp2TLFL = TLFL
FROM t_BonusPackageAmounts
WHERE Package = @VarPackage AND PackageOwned = @package
INSERT INTO @bonus VALUES (8500, @tvID, @bp2TLFL, 0, 2)
END
Is there a restriction on the statements within a if statement?
Thanks!
January 9, 2008 at 10:02 am
and what is the error message? what is wrong?
...and your only reply is slàinte mhath
January 9, 2008 at 10:09 am
If sounds like you're setting up something I refer to as "cursor hell" since this seems to be a snippet from something I can only imagine is being called from within a cursor. If you are - try posting what you're trying to do, so that we can remove that for you. Cursors have tendency to kill performance, so should be avoided when at all possible. Inserting 10,000 records one at a time will take a long time compared to inserting 10,000 records all at the same time.
That being said - what's the purpose of setting the @bp2TLFL variable, only to insert it into another table?
Give us a rundown of what you're trying to do, concerns, etc... - it will be much easier to comment.
----------------------------------------------------------------------------------
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?
January 9, 2008 at 10:14 am
1. No, there are not restrictions to the number of statements for an IF block. Provided you use BEGIN and END if more than one statement is used.
2. I don't see you using the @tlCount other than a condition, if you're not using it elsewhere, then why not put this into one SQL statement.
!! This assumes that @bonus is a table variable and that @tvID is a variable both declared previously in your code.
INSERT INTO @bonus
SELECT TOP 1
8500
,@tvID
,TLFL
,0
,2
FROM
t_BonusPackageAmounts
WHERE
Package = @VarPackage
AND PackageOwned = @package
AND @tlCount = 0
AND @status >= 1400
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 9, 2008 at 11:39 am
Thanks you guys for all the answers let me clarify a bit more.
I have a table variable where this information goes into but I need some conditional information, yes this is in a cursor. I needed to add the select statement to this code and I'm not getting any errors it just runs, I stop it somewhere after 6 minutes and it doesn't do anything, almost like an infinite loop.
The @tlCount is obviously used to check how many times I have gone through it. Oh and the @bp2TLFL variable holds an amount which I later on have to put into another table after I gathered ALL the amounts for different users into the table variable.
Thanks
January 9, 2008 at 11:48 am
Try posting what you have so far, and what you're trying to accomplish (specifically). Someone should be able to point you in the right direction, or point out why it's not finishing.
It could unfortunately be just "regular cursor behavior" you're seeing, which is one of the reasons they get such a bad name. SQL Server "likes" big chunks of data fed in all at once, not being spoon-fed one record at a time, so doing lots of inserts/updates that way is like buying a Maserati for the radio, and not putting any gas in it (i.e. expensive and no performance to boot).
----------------------------------------------------------------------------------
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?
January 9, 2008 at 11:52 am
Hi Matt,
Thanks for your help, if it was up to me I wouldn't even do this in the DB, I would do it in the app, but alas.
I PMed you the entire SP.
Thanks,
Xander
January 9, 2008 at 11:58 am
Xander - I'm not getting any PM's (I actually never have, so I don't know if I'm set up to receive them).
It's probably better to just post it here. I will take a look at it as I have time, but posting it will elicit help from a LOT of highly competent folks (a lot more than me). I do okay - but there are quite a few more gifted than I on here.
It also helps with keeping the spirit of the board going. find the issue, fix the issue, and leave the solution available for others to reference.
----------------------------------------------------------------------------------
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?
January 9, 2008 at 12:28 pm
>>if it was up to me I wouldn't even do this in the DB, I would do it in the app
And that will likely lead to an even worse situation than a server based cursor. I've seen it too many times - pull all the data client side, loop throiugh it performing joins in app logic, send results back to server. Doesn't scale, performs horribly.
1. Good - Set-based sproc code
2. Bad - Cursor-based sproc code
3. Really bad - client app-based data joining
January 9, 2008 at 12:39 pm
I found the problem, it was an infinite loop I forgot to take the @ away from a variable, which was actually a field name.
Thanks for all the help you guys!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply