Passing in a Variable with Multiple Values

Passing in a Variable with Multiple Values

Post by rclu.. » Fri, 02 Feb 2001 02:19:11



I have a listbox that allows multiple selection.
I want to use those multiple selections in my Stored Procedure
using the "IN" statement.

Example:
Cities selected:  Chicago,Milwuakee,Rockford
Stored Procedure:


Obviously, this does not work.
Does anyone have any experience in using a Multiselect Parameter
in a Stored Procedure.
Or does anyone have any ideas how to get this to work...THANKS

-Robb

Sent via Deja.com
http://www.deja.com/

 
 
 

Passing in a Variable with Multiple Values

Post by Shekar Akit » Fri, 02 Feb 2001 02:54:29


Use syntax below


Shekar


>I have a listbox that allows multiple selection.
>I want to use those multiple selections in my Stored Procedure
>using the "IN" statement.

>Example:
>Cities selected:  Chicago,Milwuakee,Rockford
>Stored Procedure:


>Obviously, this does not work.
>Does anyone have any experience in using a Multiselect Parameter
>in a Stored Procedure.
>Or does anyone have any ideas how to get this to work...THANKS

>-Robb

>Sent via Deja.com
>http://www.deja.com/


 
 
 

Passing in a Variable with Multiple Values

Post by Tony Rogerso » Fri, 02 Feb 2001 02:51:02


Create a temporary table and pass it it...

CREATE TABLE #IN (
    myval    int     not null
    )

INSERT #IN VALUES ( 1 )
INSERT #IN VALUES ( 2 )
INSERT #IN VALUES ( 3 )

EXEC MySP
GO

CREATE PROC MySp
AS
    EXEC sp_executesql N'SELECT * FROM mytab t INNER JOIN #IN i ON i.myval =
t.myid'

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group - FAQ, SQL Tutorials etc...]


> I have a listbox that allows multiple selection.
> I want to use those multiple selections in my Stored Procedure
> using the "IN" statement.

> Example:
> Cities selected:  Chicago,Milwuakee,Rockford
> Stored Procedure:


> Obviously, this does not work.
> Does anyone have any experience in using a Multiselect Parameter
> in a Stored Procedure.
> Or does anyone have any ideas how to get this to work...THANKS

> -Robb

> Sent via Deja.com
> http://www.deja.com/

 
 
 

Passing in a Variable with Multiple Values

Post by Joe Celk » Fri, 02 Feb 2001 04:37:35


Quote:>> I want to use those multiple selections in my Stored Procedure

using the "IN" statement.  <<

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 parameer 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');
 ...

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(5) NOT NULL PRIMARY KEY,
 parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma.  Now, the real query:

INSERT INTO ParmList (keycol, parmlist)
SELECT keycol,
       CAST (SUBSTRING (',' || I1.input_string || ','
                        FROM S1.seq +1
                         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
extracts 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);

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.
---
Trilogy, FORTUNE, and Goldman Sachs are hosting the E-Business event of
the year. Find out more by visiting www.battleroyale2001.com

Sent via Deja.com
http://www.deja.com/

 
 
 

Passing in a Variable with Multiple Values

Post by Steve Dassi » Fri, 02 Feb 2001 05:28:25


You know this query makes a giant sucking sound
on server.Tell us at least one system it runs on
as is.

Steve

Sent via Deja.com
http://www.deja.com/

 
 
 

1. passing Multiple param values using one variable

I am trying to do something like this.

-------------------------------------------
create procedure teststatus

as

select StatusCode, StatusName
from tblStatus

-------------------------------------------

for executing this i will pass


This does not return the expected result. No records are returned.

Any ideas?

tia

2. ORA-01200 on RAID 5

3. Passing multiple values to a variable

4. Help with an application error

5. Passing multiple values with a variable???

6. SQL Server

7. Passing value from a PL/SQL variable to a SQL*Plus variable

8. $5->$50,00

9. Passing Multiple Variables to a Select SQL

10. Can you pass a where with multiple statements without a variable for each where statement

11. Passing multiple variables to Select SQL

12. Passing variables between multiple forms - OPAL 4.5

13. Passing value of strin variables to a report title