Wrong datatype for SP?

Question

Post reply

Wrong datatype for SP?

Viewing 8 posts - 16 through 22 (of 22 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Junglee_George (8/30/2013)

Hi

I 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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 3

VSSGeorge

SSCrazy Eights

Points: 8146

August 30, 2013 at 3:39 am

#1646264

Thanks Gail. But Chris hasn’t written the code for the splitter function there. I need that code.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

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/

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

OTF SSCertifiable Points: 6054 More actions · Answer 7

OTF

SSCertifiable

Points: 6054

August 30, 2013 at 6:27 am

#1646325

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

SrcName (8/30/2013)

Hi, SSC-Dedicated

Based 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

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)
Intro to Tally Tables and Functions