April 27, 2004 at 4:10 pm
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
April 27, 2004 at 9:28 pm
/* SET NOCOUNT ON */
SELECT GEO , CustomerType, SUM(Revenue) As Revenue
FROM tblRevenue
(PATINDEX('%'+Mon+'%',@mon)>0 ) and (Year=@year)
GEO , CustomerType
RETURN
--Jeff Moden
April 29, 2004 at 7:25 am
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
April 29, 2004 at 1:26 pm
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
April 29, 2004 at 8:24 pm
The PATINDEX example works. Try it.
--Jeff Moden
April 30, 2004 at 8:31 am
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
May 1, 2004 at 5:57 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply