January 14, 2011 at 3:43 pm
BaldingLoopMan (1/14/2011)
sorry. i do agree w the indexing just not the loop haters out there
Never mind whether you like loops or not, this has crash and burn written all over it.
Let's review:
- clause not written perfectly well = Crash boom bang
- not knowing where the vallookup gets its clauses from, "SQL injection" is a very real concern
- why put things into the table you're going to loop on if you're going to immediately delete them
- no order by = random order (like has been mentioned before)
- if the dynamic select returns more than one row, you run the chance of missing the "first rank that works" completely.
At very least:
- check your inputs. be sure you @clause contents are valid and safe. Look up SQL injection to figure out how to check the "safe" part. "valid" is going to be tougher to do.
- Look up BEGIN TRY....BEGIN CATCH.... Use it here.
- combine your first IF into the cursor definition.
- combine your 2nd IF into the dynamic SQL (if you find this is the ONLY way) so that you can sure you return something IF there's a match.
- put an ORDER BY statement in the cursor def.
- deal with the possbility that none of the options gets a "hit" from this process.
----------------------------------------------------------------------------------
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 14, 2011 at 3:46 pm
Random note:
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?
BWAHAHAHAHAHA, nice Matt, very nice. 🙂 :w00t:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 15, 2011 at 11:04 am
Craig Farrell (1/14/2011)
Random note: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?
BWAHAHAHAHAHA, nice Matt, very nice. 🙂 :w00t:
Thanks! It used to be my modus operandi in a previous position. Thankfully that's changed now.
Cheers!
----------------------------------------------------------------------------------
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 17, 2011 at 12:05 am
Thanks for the reply.
Sorry I missed the 'Order by' clause. The select is ordered by the clauserank column.
Yes. This is not actual code. I cann't publish it.
The whole Idea is, I have some conditions in the clause column and it has to be executed in the order of clause
rank.
From the replies posted, I came to know about the tally table,
I got another way,
Is it possible to execute SQL statements as a set.
Suppose I frame the SQL in the select itself and execute all.. that would be great.
January 17, 2011 at 12:12 am
Thanks for the comments
1. @clauses are valid and safe, I'm sure
2. No.. Couldn't do that, the first IF check is to skip the execution and direct selection, but with the ranking.
3. Can change. Will do
4. Sorry. I missed the Order by clause while entering the question.
5. is there any possibility, I have set of SQL Select statement framed in the table, and it is all executed at one shot 🙂
like
with expn ( strSQL ) as
select strSQL from @tablevar
execute strSQL
January 17, 2011 at 4:25 am
as long as you depend on a dynamic where you will need looping. can't run from it
--
Thiago Dantas
@DantHimself
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply