How do I Pass set of strings to Stored procedure WHERE IN clasue

#1

Hello All,
We have stored procedure which will receive set of names and birth
date of the candidate
CREATE PROCEDURE ml_test_student
@nameStr varchar(255),@dateStr
AS
SELECT distinct s.StudentID, "|", s.Name, "|", s.birthdate,"|",
IsValid FROM
Student s
WHERE Id in (@nameStr) and birthdate < @dateStr ORDER by Name

Execution ---->
For single name
exec ml_test_fasb 'raju' '19980401'

Result: 1 rows retrieved.

If i need to pass multiple names on the namestr what should I do.. is
any changes are required in existing procedure definiton...
or else i should use temporary table...

If i should use temp table... how do I parse the string and insert
into table..

Could any one help me on this... Please...

#2

rpmohan wrote:

Quote:

Hello All,
We have stored procedure which will receive set of names and birth
date of the candidate
CREATE PROCEDURE ml_test_student
@nameStr varchar(255),@dateStr
AS
SELECT distinct s.StudentID, "|", s.Name, "|", s.birthdate,"|",
IsValid FROM
Student s
WHERE Id in (@nameStr) and birthdate < @dateStr ORDER by Name

Execution ----
For single name
exec ml_test_fasb 'raju' '19980401'

Result: 1 rows retrieved.

What you in effect are executing is

SELECT distinct s.StudentID, "|", s.Name, "|", s.birthdate,"|", IsValid
FROM Student s
WHERE Id in ('raju') and birthdate < '19980401'
ORDER by Name

Quote:

If i need to pass multiple names on the namestr what should I do.. is
any changes are required in existing procedure definiton...
or else i should use temporary table...

You've got to options:
(1) Deconstruct the strong and put the elements of the string into a temporary table. (It is not as difficult as it may sound.)
(2) Look into dynamic sql. Some version af Sybase SQLServer support EXEC @string, where @string contains an SQL-statement. (Sometimes unofficially, mind you.)

Quote:

If i should use temp table... how do I parse the string and insert
into table..

charindex will find the position to split the string, substring will help you do the splitting.

--
Kristian Damm Jensen
damm (at) ofir (dot) dk

#3

You can use the following too...

exec ml test fasb "'raju','ram','mark','shame'",'19980401'

Try it...
Ram DSL

"Kristian Damm Jensen" <REdammMOVE (AT) ofir (DOT) dk> wrote

Quote:

rpmohan wrote:
Hello All,
We have stored procedure which will receive set of names and birth
date of the candidate
CREATE PROCEDURE ml test student
@nameStr varchar(255),@dateStr
AS
SELECT distinct s.StudentID, "|", s.Name, "|", s.birthdate,"|",
IsValid FROM
Student s
WHERE Id in (@nameStr) and birthdate < @dateStr ORDER by Name

Execution ----
For single name
exec ml test fasb 'raju' '19980401'

Result: 1 rows retrieved.

What you in effect are executing is

SELECT distinct s.StudentID, "|", s.Name, "|", s.birthdate,"|", IsValid
FROM Student s
WHERE Id in ('raju') and birthdate < '19980401'
ORDER by Name

If i need to pass multiple names on the namestr what should I do.. is
any changes are required in existing procedure definiton...
or else i should use temporary table...

You've got to options:
(1) Deconstruct the strong and put the elements of the string into a
temporary table. (It is not as difficult as it may sound.)
(2) Look into dynamic sql. Some version af Sybase SQLServer support EXEC
@string, where @string contains an SQL-statement. (Sometimes
unofficially, mind you.)

If i should use temp table... how do I parse the string and insert
into table..

charindex will find the position to split the string, substring will
help you do the splitting.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How do I Pass set of strings to Stored procedure WHERE IN clasue

comp.databases.sybase comp.databases.sybase

How do I Pass set of strings to Stored procedure WHERE IN clasue - 02-02-2004 , 11:04 PM

Re: How do I Pass set of strings to Stored procedure WHERE IN clasue - 02-03-2004 , 01:01 AM

Re: How do I Pass set of strings to Stored procedure WHERE IN clasue - 02-09-2004 , 09:08 AM