"IN Clause" Stored Procedure

Question

Post reply

"IN Clause" Stored Procedure

goforit@mailinator.com

Newbie

Points: 5
More actions
April 27, 2004 at 4:10 pm

#107974

I am having problem passing "," value into stored procedure using stored procedure .
The below stored procedure wont work for a range of months like ( march,april,may) . It would work for just single value ..any thoughts ?

ALTER PROCEDURE
sp_RevByGeo
@mon
varchar(255),
@year
int
AS
/* SET NOCOUNT ON */
SELECT GEO , CustomerType, SUM(Revenue) As Revenue
FROM tblRevenue
Where
(Mon in (@mon) ) and (Year=@year)
GROUP BY
GEO , CustomerType
RETURN

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Using your stored procedure...

ALTER PROCEDURE sp_RevByGeo
@mon varchar(255),
@year int
AS



Where
GROUP BY

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vadba SSChampion Points: 11133 More actions · Answer 2

You cannot place a list of values in a variable for the IN clause: Mon in (@mon)

To use @mon, you would have to resort to dynamic SQL. Also, since your working with character data, if you executed PRINT @mon, each value must be quoted. For example, "PRINT @mon" must display 'march','april','may', not march,april,may, since the correct syntax would be WHERE mon IN ('march','april','may')

Assuming a properly quoted @mon, you could use:

DECLARE @cmd varchar(8000)

SET @cmd = 'SELECT GEO , CustomerType, SUM(Revenue) As Revenue'

+ ' FROM tblRevenue'

+ ' WHERE Mon in (' + @mon + ')'

+ ' AND Year = ' + @year

+ ' GROUP BY GEO , CustomerType'

EXEC (@cmd)

Mike

Steve Croll SSC-Addicted Points: 488 More actions · Answer 3

I've used the fn_split UDF very effectively in this situation. Here is a link for the code:

http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html

it would look something like this:

ALTER PROCEDURE sp_RevByGeo

@mon varchar(255),

@year int

AS

SELECT GEO , CustomerType, SUM(Revenue) As Revenue

FROM tblRevenue

Where (Mon in (select value from dbo.fn_split(@mon)) ) and (Year=@year)

GROUP BY GEO , CustomerType

RETURN

The nice thing about this, as opposed to the the dynamic SQL posted above is that you aren't recompiling your stored proc everytime. Optionally you could do this query by performing a join, which may be more efficient:

ALTER PROCEDURE sp_RevByGeo

@mon varchar(255),

@year int

AS

SELECT GEO , CustomerType, SUM(Revenue) As Revenue

FROM tblRevenue r

Inner Join dbo.fn_split(@mon) m on r.Mon = m.value

GROUP BY GEO , CustomerType

RETURN

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

The PATINDEX example works. Try it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Croll SSC-Addicted Points: 488 More actions · Answer 5

The patindex will definintely work. The question is which one is the most efficient. Does the patindex that you propose essentially use a LIKE to do the comparison? (I know you can use PATINDEX in conjunction with LIKE, but it seems that the overall operation can be more resource intensive if you are using it against a large dataset.) In my experience, the fn_split UDF is a very elegant solution to this problem that is well documented. Give it a try.

steve

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Steve,

To answer your first question, I've found that PatIndex is a wee bit faster than LIKE. In either case, both take a toll on speed when it comes to large data sets.

The fn_split UDF does not come with SQL Server (must've been homegrown in your shop) but in any case, UDF's are really nothing more than scalar (returns one value) stored procedures (unless they are inline table defined). Stored procedures will always take longer than the built in functions of SQL Server.

Still, I'd be very interested in seeing the fn_split UDF. Any chance of you posting it? Thanks alot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)