Prev: Question on OVER Clause Useage
Next: Need help with sequel statement.
From: Erland Sommarskog on 29 Jul 2010 05:02
SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> Erland, the query is the same just that i didn't mention the exact column,
> table names in it!

But you did not care to explain why you removed the single quotes, despite
you said the query was working with it. You did not give any sample data
that demonstrated the problem.

Frankly, if you don't care to spend even five seconds to demonstrate your
problem, why would even spend the time to write this message?

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: BGL on 2 Aug 2010 12:56
> Good SQL programmers follow best practices, they certainly don't pass 100's
> of parameters into a stored procedure when the application already has the
> data as a "set".

Why isn't passing in 100 params (which are data typed and can be easily
constructed back into a table) not a best practice?

Except for table variables in SQL Server 2008, wouldn't we need to result to
a looping construct to parse a delimited string or use XML to accomplish the
same?

Even if the SP call overhead if costly for many parameters, won't you make
it back plus more by not having to parse a parameter string (or shred the
XML) inside the SP?


"Tony Rogerson" wrote:

>
>
> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message
> news:6f1b30bd-c903-47ac-b8e0-6232bc5f5c36(a)f6g2000yqa.googlegroups.com...
> >>> I am passing comma separated values to a stored proc within which that
> >>> value is used something like this.<<
> >
> > Do not write code like this. I just did an article on the use of a
> > long parameter list at SQL Server Central.
>
> And its been berated by a number of professionals and also has a poor
> rating.
>
> Long parameter lists are a very very bad idea, unmaintainable compared to
> other established solutions that you can get the code off the shelf for.
>
> >
> > Good SQL programmers do not like to write procedural code. We also
> > know that SQL is compiled and not interpreted like BASIC.
>
> Good SQL programmers follow best practices, they certainly don't pass 100's
> of parameters into a stored procedure when the application already has the
> data as a "set".
>
> --ROGGIE--
>
>
From: Erland Sommarskog on 2 Aug 2010 15:22
BGL (BGL(a)discussions.microsoft.com) writes:
> Why isn't passing in 100 params (which are data typed and can be easily
> constructed back into a table) not a best practice?

Because it is difficult to maintain, and the performance of long IN clauses
is not very good.

> Except for table variables in SQL Server 2008, wouldn't we need to
> result to a looping construct to parse a delimited string or use XML to
> accomplish the same?

Not necessarily. Look at http://www.sommarskog.se/arrays-in-sql-2005.html
for a couple methods to parse a list, both with and without loops. I also
discuss the option of passing many parameters.

> Even if the SP call overhead if costly for many parameters, won't you make
> it back plus more by not having to parse a parameter string (or shred the
> XML) inside the SP?

Not according to my performance tests, which you find in the second appendix
to my article.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2 3
Prev: Question on OVER Clause Useage
Next: Need help with sequel statement.