August 30, 2013 at 3:16 am
Junglee_George (8/30/2013)
HiI achieved it through this way. The code below is working.
If any flaws are there in my approach, replies are welcome.
...
1. This expression
WHERE MC.isDeleted=0x0 AND charindex(MC.materialItemContainerCode,@containerCodes)<>0
isn't SARGable. An index containing MC.materialItemContainerCode as a key, which might be used to seek MC.materialItemContainerCode, will not be used by this query.
2. Values of MC.materialItemContainerCode which are left-side fragments of values in @containerCodes will yield false positives.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2013 at 3:30 am
This kind of query is a common problem with known, tested, well-performing solutions. There's no need to re-invent the wheel here.
See Chris's query using a splitter function or consider a table-valued parameter
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 3:39 am
Thanks Gail. But Chris hasn’t written the code for the splitter function there. I need that code.
August 30, 2013 at 3:42 am
Junglee_George (8/30/2013)
Thanks Gail. But Chris hasn’t written the code for the splitter function there. I need that code.
you can find the code for splitter in SSC article with the same name
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 30, 2013 at 3:57 am
Junglee_George (8/30/2013)
Thanks Gail. But Chris hasn’t written the code for the splitter function there. I need that code.
It's not his code. It's from an SSC article and a quick google search on the function name will pull up the article with its code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 3:59 am
Junglee_George (8/30/2013)
Thanks Gail. But Chris hasn’t written the code for the splitter function there. I need that code.
Sorry about that. It's in this article[/url].
I recommend you read the article, you will learn far more than if you copy and paste the code for the function.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2013 at 6:27 am
By all means use the Splitter, I've used it a few times and it's work really well but take the time to look at the Table Valued Parameter approach suggested as well, if for nothing else, to learn it as it doesn't hurt to have more than one tool in your toolbox.
August 30, 2013 at 7:17 am
SrcName (8/30/2013)
Hi, SSC-DedicatedBased on your experience,
i would be appreciate if you explain in detail how SQL Injection will pass this.
P.S.
maybe in this case it's no so important, this select statement is not of crucial importance
If you look just above people's avatars, you'll find their names or at least a "handle" they go by.
There's no sense in me spending time to "explain in detail" about SQL Injection. If you Google for "SQL Injection", you'll find dozens of articles on the subject including what it is and how to prevent it. The only time that you don't have to worry about SQL Injection in your passed parameters is if you're not concatenating the variables directly and untreated into Dynamic SQL. You should always be concerned about such a thing because even stored procedures where the "select statement is not of crucial importance" can be attacked and it's still one of the leading reasons why people are able to attack your server with virtually no effort at all.
--Jeff Moden
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply