Prev: using schema in security mode
Next: Float Types Used as Primary Key on SQL 2005
From: SetonSoftware on 19 Jul 2010 09:18
I have a SQL Server 2005 stored procedure that will receive a series
of valeus to be passed to an IN clause. Since IN clauses do not take
parameters, I need to use a UDF like fnc_Parse2TableString() to parse
the comma-delimited values into a table object as shown below:

DECLARE @DeptNames varchar(max)

SET @DeptNames = 'Accounting, Finance'

SELECT *
FROM Personnel
WHERE Department IN
CASE
WHEN @DeptNames <> '' THEN (SELECT data FROM
dbo.fnc_Parse2TableString(@DeptNames, ','))
END


What I'm trying to accomplish is to have @DeptNames as an optional
parameter. Since this SQL does not work as shown, what must I do in
order to accomplish this?

I really want to avoid dynamic SQL and since I may have many such
optional IN clause parameters, managing multiple versions of the same
SQL would become cumbersome.

Thanks

Carl
From: --CELKO-- on 19 Jul 2010 11:04
>> I have a SQL Server 2005 stored procedure that will receive a series of valeus to be passed to an IN clause. Since IN clauses do not take parameters, ..<<

Wrong, you just need to learn how to do it. It means thinking in sets
and not in procedural code

>> I need to use a UDF like fnc_Parse2TableString() to parse the comma-delimited values into a table object as shown below: <<

Yep, just like you were in a 1960's C programming class and having to
do low-level I/O. And you are using proprietary "features" that will
foil the optimizer, too.

SQL Server can handle up to 2K parameters, which is actually a short
list compared to DB2's 32K. Since they are parameters, the compiler
handles ALL the error checking (I'll bet your parser doesn't and I
will bet that you will not update your code to make your parser work
like the next release of T-SQL), can do optimizer tricks with them,
detect missing parameters, etc.

Here is a skeleton for 5 values. Yo use the table constructor to
create a derived table where you need it.

CREATE PROCEDURE Foobar
(@p1 INTEGER = NULL,
@p2 INTEGER = NULL,
@p3 INTEGER = NULL,
@p4 INTEGER = NULL,
@p5 INTEGER = NULL,
etc.)

BEGIN
...
SELECT ..
FROM ..
WHERE x
IN (SELECT parm
FROM (VALUES (@p1), (@p2), (@p3), @p4), (@p5)) AS
X(parm)
WHERE parm IS NOT NULL;
...
END;

You can generalize this, do calculations and add CASE expression to
the code. YOu can cast the first line to desired data types and force
the columns. After that use cut & paste.

VALUES (CAST (sku_1 AS CHAR(9)), CAST (price_1 AS DECIMAL(8,2), CAST
(order_qty_1 AS INTEGER),
(sku_2, price_2, order_qty_2),
etc. ) AS X(..)

All declarative code, no need for procedural thinking at all.
From: Plamen Ratchev on 19 Jul 2010 11:12
You can simply use IF statement to execute two queries with different
WHERE predicate:

IF @DeptNames <> ''
<query with IN>;
ELSE
<query without IN>;

Also, see Erland's article on different technique for splitting
arrays:
http://www.sommarskog.se/arrays-in-sql-2005.html

--
Plamen Ratchev
http://www.SQLStudio.com
From: BGL on 19 Jul 2010 13:17

We used this suggested approach for a "Google Friend Connect" custom feature
against a MySQL backend.

We needed to SELECT data for the current user, as well as the current user's
Friends (up to 100). MySQL doesn't support the row constructor, so we had to
UNION ALL-up the input params in the derived table, but the SP call from the
web app was a cinch and the SP was lightning fast.

"--CELKO--" wrote:

> >> I have a SQL Server 2005 stored procedure that will receive a series of valeus to be passed to an IN clause. Since IN clauses do not take parameters, ..<<
>
> Wrong, you just need to learn how to do it. It means thinking in sets
> and not in procedural code
>
> >> I need to use a UDF like fnc_Parse2TableString() to parse the comma-delimited values into a table object as shown below: <<
>
> Yep, just like you were in a 1960's C programming class and having to
> do low-level I/O. And you are using proprietary "features" that will
> foil the optimizer, too.
>
> SQL Server can handle up to 2K parameters, which is actually a short
> list compared to DB2's 32K. Since they are parameters, the compiler
> handles ALL the error checking (I'll bet your parser doesn't and I
> will bet that you will not update your code to make your parser work
> like the next release of T-SQL), can do optimizer tricks with them,
> detect missing parameters, etc.
>
> Here is a skeleton for 5 values. Yo use the table constructor to
> create a derived table where you need it.
>
> CREATE PROCEDURE Foobar
> (@p1 INTEGER = NULL,
> @p2 INTEGER = NULL,
> @p3 INTEGER = NULL,
> @p4 INTEGER = NULL,
> @p5 INTEGER = NULL,
> etc.)
>
> BEGIN
> ...
> SELECT ..
> FROM ..
> WHERE x
> IN (SELECT parm
> FROM (VALUES (@p1), (@p2), (@p3), @p4), (@p5)) AS
> X(parm)
> WHERE parm IS NOT NULL;
> ...
> END;
>
> You can generalize this, do calculations and add CASE expression to
> the code. YOu can cast the first line to desired data types and force
> the columns. After that use cut & paste.
>
> VALUES (CAST (sku_1 AS CHAR(9)), CAST (price_1 AS DECIMAL(8,2), CAST
> (order_qty_1 AS INTEGER),
> (sku_2, price_2, order_qty_2),
> etc. ) AS X(..)
>
> All declarative code, no need for procedural thinking at all.
> .
>
From: --CELKO-- on 19 Jul 2010 20:18
This is an old DB2 trick; I had not seen it done with MySQL. Since the
data is getting pushed on a parameter stack, it should fast in most
SQLs. SQL Server can take 2k parameters, but DB2 takes 32k and Oracle
takes 64K.

I have an article on it I have to send off this week; I will let you
know when it comes out.
 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: using schema in security mode
Next: Float Types Used as Primary Key on SQL 2005