I would like to pass in comma delimted integer numbers in terms of string
array from front end .net
application as an input parameter to a stored procedure.

Stored procedure would some how sort this input string into an integer array
and qualify
a simple select against the "in" filter clause.

I know how to do it using dynamic sql utilizing sp_executesql.

But Please let me know if there is a *** BETTER WAY OF DOING IT...***

Here is sample ddl and input sample data with the sample stored proc that I
have in mind.

Thank you in advance.


set nocount on
go
create table z_test_del
(
    mydate datetime,
    mynum int
)
go
insert z_test_del values('1/1/1992',1)
insert z_test_del values('1/1/1993',2)
insert z_test_del values(null,3)
insert z_test_del values('1/1/1994',4)
insert z_test_del values('1/1/1995',5)
insert z_test_del values(null,6)
go

declare    @myvars nvarchar(3000)
set @myvars='1,3'
declare @sql nvarchar(3000)
set @sql= 'select * from z_test_del '

if len(@myvars)>0
set @sql=@sql+ ' where mynum in (' + @myvars + ')'
EXECUTE sp_executesql @sql
GO

CREATE PROCEDURE dbo.mytestproc
    @myvars varchar(8000) = null
AS
    -- sample @myvars ==> '1,3,4'
    select * from z_test_del
    where mynum in @myvars -- this is my intended sql and I understand that
compiler will not compile it.

GO

drop table z_test_del
go

Mr. Dejan Sarka took time to find a solution to that quandry:
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx


ML

Thanks, it did work for me.


Show quoteHide quote

1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
  A string is a string; it is a scalar value like any other parameter;
it is not code.  This is not just an SQL problem; this is a basic
misunderstanding  of programming of principles.  .

2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator.  I like to use the
traditional comma.  Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma.  You will need a table of sequential numbers -- a
standard SQL programming trick,  Now, the query, in SQL-92 syntax
(translate into your local dialect):

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
       CAST (SUBSTRING (I1.input_string
                        FROM S1.seq
                         FOR MIN(S2.seq) - S1.seq -1)
         AS INTEGER)
  FROM InputStrings AS I1,  Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
   AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
   AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step.  The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.

You can then write:

SELECT *
   FROM Foobar
  WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);

Hey, I can write kludges with the best of them, but I don't.  You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc.  Basically, you must write part of a compiler in SQL.
Yeeeech!  Or decide that you do not want to have data integrity, which
is what most Newbies do in practice.

3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", like this in SQL/PSM
(translate into your local dialect):

CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER)  -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
   FROM Floob
WHERE my_col
        IN (SELECT DISTINCT parm
              FROM ( VALUES (p1), (p2), .., (pN))
                          AS ParmList(parm)
               WHERE parm IS NOT NULL
                    AND <other conditions>)
  AND <more predicates>;
<more code>;
END;

In SQL Server, you wold use "SELECT @p1 UNION ALL ..SELECT @pN" instad
of VALUES.

3) The right way!  You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems.  A good optimizer will not need the
SELECT DISTINCT, just a SELECT.

Hi sqlster,

I cannot see any reason for using Dynamic SQL here.
creating a table with column you wish to search and then populating it
with the values will do the job.

set nocount on
go
create table tmpNumbers
(
    n int
)
insert into tmpNumbers values(1)
insert into tmpNumbers values(2)

create table z_test_del
(
        mydate datetime,
        mynum int
)
go
insert z_test_del values('1/1/1992',1)
insert z_test_del values('1/1/1993',2)
insert z_test_del values(null,3)
insert z_test_del values('1/1/1994',4)
insert z_test_del values('1/1/1995',5)
insert z_test_del values(null,6)
go


select * from z_test_del  where mynum in (select n from tmpNumbers )

drop table z_test_del
drop table tmpNumbers

With warm regards
Jatinder Singh

My table design - where did I go wrong?
Alter table / update... set - strange problem
Newbee question about permissions and sp_OACreate
Stored Procs vs VIEWS: Seeking Comparison
How to find gaps in sequential key