How to : stocked procedure, WHERE x IN (@parm)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to : stocked procedure, WHERE x IN (@parm)

Author

Topic

saxrub
Starting Member

4 Posts

Posted - 2010-02-11 : 04:41:46

Hi all,

Here is a script to create a stored proc.javascript:insertsmilie('

')

CREATE PROCEDURE [dbo].[MYPROC]
(@Param1 varchar(max))
AS
BEGIN
SET NOCOUNT ON;
SELECT * from MYTABLE where MYFIELD in (@Param1)
END

If I call

Exec Myproc 'X'

The proc return all the records of MyTable where MYFIELD values = 'X'javascript:insertsmilie('

')

Now, and it's my problem,javascript:insertsmilie('

') I want to retreive all the records for X and Y (typicaly WHERE MYFIELD IN ('X','Y')

How to call MYPROC and give the X and Y values ?

All the best

Pascal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 04:44:24

modify procedure like


CREATE PROCEDURE [dbo].[MYPROC]
(@Param1 varchar(max))
AS
BEGIN
SET NOCOUNT ON;
SELECT * from MYTABLE where ',' + @Param1 + ',' LIKE '%,' + MYFIELD + ',%' 
END

then call it like

EXEC Myproc 'X,Y'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-11 : 04:44:51

refer to here
http://www.sommarskog.se/arrays-in-sql-2005.html
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

KH
[spoiler]Time is always against us[/spoiler]

mymatrix
Starting Member

24 Posts

Posted - 2010-02-11 : 05:01:12

You can do it using dynamic query but it would be a performance hit in comparison with Static Query.

Dynamic Query is mentioned below:

Procedure declaration :
CREATE PROCEDURE [dbo].[MYPROC]
(@Param1 varchar(max))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sQuery VARCHAR(max)
SET @sQuery= 'SELECT * from MYTABLE where MYFIELD in (' + @Param1 + ')'
EXEC (@sQuery)

END

Procedure calling :

declare @test varchar(max)
set @test='''apple'',''mango'''

EXEC MYPROC @test

**************************************

Even my blood group says be -ve to all the negatives.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-11 : 05:49:14

<<
set @test='''apple'',''mango'''
>>

Now you have complex task of assigning single quotes to the values

Madhivanan

Failing to plan is Planning to fail

mymatrix
Starting Member

24 Posts

Posted - 2010-02-11 : 06:06:36

quote:
Originally posted by madhivanan

<<
set @test='''apple'',''mango'''
>>

Now you have complex task of assigning single quotes to the values

Madhivanan

Failing to plan is Planning to fail

If you are passing multiple string values, then you have to pass single quotes. Although it can be done in improved way like :

SET QUOTED_IDENTIFIER OFF
GO

declare @test varchar(max)
set @test="'apple','mango'"

EXEC MYPROC @test

**************************************

Even my blood group says be -ve to all the negatives.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 06:11:08

quote:
Originally posted by mymatrix

quote:
Originally posted by madhivanan

<<
set @test='''apple'',''mango'''
>>

Now you have complex task of assigning single quotes to the values

Madhivanan

Failing to plan is Planning to fail

If you are passing multiple string values, then you have to pass single quotes. Although it can be done in improved way like :

SET QUOTED_IDENTIFIER OFF
GO

declare @test varchar(max)
set @test="'apple','mango'"

EXEC MYPROC @test

**************************************

Even my blood group says be -ve to all the negatives.

why do want to pass this dynamically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mymatrix
Starting Member

24 Posts

Posted - 2010-02-11 : 07:03:29

quote:
Originally posted by visakh16
why do want to pass this dynamically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

We can even pass it like this
exec MYPROC "'apple','mango'"

**************************************

Even my blood group says be -ve to all the negatives.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 07:11:48

quote:
Originally posted by mymatrix

quote:
Originally posted by visakh16
why do want to pass this dynamically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

We can even pass it like this
exec MYPROC "'apple','mango'"

**************************************

Even my blood group says be -ve to all the negatives.

Nope my question was why to go for dynamic solution when you can do it otherwise

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mymatrix
Starting Member

24 Posts

Posted - 2010-02-11 : 07:24:50

That's what i have writtern in my first post as well that using 'Dynamic Sql' would be a performance hit.
It can be done with other options like using Temp table or Table variable as well.

**************************************

Even my blood group says be -ve to all the negatives.

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 08:42:10

"You can do it using dynamic query but it would be a performance hit in comparison with Static Query"

Not just performance hit, you have to:

a) Grant the user SELECT permission on the Table - which represents a risk
b) remember to guard against SQL Injection every time you use dynamic SQL

saxrub
Starting Member

4 Posts

Posted - 2010-02-11 : 09:03:47

Hi all,
If my initial question was "How to : stocked procedure, WHERE x IN (@parm)",
if mymatrix question give us a resolution in this question, (... SET @sQuery= 'SELECT * from MYTABLE where MYFIELD in (' + @Param1 + ')'...)
the question should have been "How to cross parameters during dynamic call on a report of Reporting services based on a query using a "WHERE x IN (@parm)" clause ?
for exemple : http://cx00-qd.atlantica.credit-agricole.fr/CX00QDsrv?%2fDAV600&rs:Command=Render&parm='V32901','V82132'

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 09:19:31

The way I do what was asked in the original post is:


CREATE PROCEDURE [dbo].[MYPROC]
(@Param1 varchar(max))
AS
BEGIN
SET NOCOUNT ON;
SELECT *
from MYTABLE AS T
    JOIN dbo.MySplitFunction(@Param1) AS S
       ON S.MySplitValue = T.MYFIELD
END

Search forums here for suitable Split functions

saxrub
Starting Member

4 Posts

Posted - 2010-02-11 : 11:04:26

What a nice resolution that this function SPLIT!
Thanks to all, I shall lie down less stupid!
Pascal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources