Stored Proc and In clause - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Stored Proc and In clause

Author

Topic

dineshkunder
Starting Member

9 Posts

Posted - 2005-07-23 : 21:57:20

Thanks in advance!

I am having problems in the stored procedure below passing in values for @month like '2' or '2,3,4' and getting the procedure to work or return any values when there are no errors. I tried casting and when I don't use Cast I get errors when I sen multiple values. I tried Casting it to varchar, Char etc. but no records returned. I also sent in values for @month = "'2','3','4'" but that did not work either.

exec testhitterstats_sp @month= "'5','6'" -> did not work, error with no casting
exec testhitterstats_sp @month= 5 -> works with no casting
exec testhitterstats_sp @month= '5' -> works with no casting
exec testhitterstats_sp @month= 5,6 -> did not work , error with no casting

What am I doing wrong below? Any help would be greatly appreciated!!

CREATE PROCEDURE [testhitterstats_sp]
@month varchar(20)

AS

SELECT stats_hitting.player_id, SUM(AB) as ab , SUM(H) AS h

FROM stats_hitting.player_id,

JOIN games on stats_hitting.game_id = games.game_id and cast(datepart(month,game_date) as char(1) ) in (@month)

where profile_players.player_status_id in (1)

GROUP BY stats_hitting.player_id

GO

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-24 : 01:35:16

CREATE PROCEDURE [testhitterstats_sp]
@month varchar(20)

AS

DECLARE @str varchar(1000)

SET @str = 'SELECT stats_hitting.player_id, SUM(AB) as ab , SUM(H) AS h
FROM stats_hitting.player_id
JOIN games ON stats_hitting.game_id = games.game_id AND MONTH(game_date) IN (' + @month + ')
where profile_players.player_status_id in (1)
GROUP BY stats_hitting.player_id'

EXEC(@str)

Pass in the @month parameter as: '5,6'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-24 : 01:47:29

>>> where profile_players.player_status_id in (1) <<<

Huh?

Why not = 1

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

dineshkunder
Starting Member

9 Posts

Posted - 2005-07-24 : 10:28:32

Thanks, I was wondering if there was a non-dynamic Sql answer to the above, I am currently using one. My original SQl is much longer andd complicated and i wanted to try and not use EXEC in the stored procedure as it is not very efficient? Is that right about using EXEC in SP being not efficient??

emilg
Starting Member

13 Posts

Posted - 2005-07-25 : 04:08:55

Assumming that the MAX number of months that you can pass as parameters will be 12 you can write:

CREATE PROCEDURE [testhitterstats_sp]
(
@month1 int = NULL,
@month2 int = NULL,
@month3 int = NULL,
@month4 int = NULL,
@month5 int = NULL,
@month6 int = NULL,
@month8 int = NULL,
@month9 int = NULL,
@month10 int = NULL,
@month11 int = NULL,
@month12 int = NULL
)
AS

SELECT stats_hitting.player_id, SUM(AB) as ab , SUM(H) AS h
FROM stats_hitting.player_id
JOIN games
on stats_hitting.game_id = games.game_id
and cast(datepart(month,game_date) as char(1) )
in (@month1, @month2, @month3, @month4, @month5, @month6,
@month7, @month8, @month9, @month10, @month11, @month12)
where profile_players.player_status_id in (1)
GROUP BY stats_hitting.player_id

Then you will call the procedure with as many arguments you want, not more than 12:

exec testhitterstats_sp 1,5,7,9

Emil

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 08:45:44

A non-dynamic SQL method...


CREATE PROCEDURE [testhitterstats_sp]
@month varchar(20)

AS

SELECT stats_hitting.player_id, SUM(AB) as ab , SUM(H) AS h
FROM stats_hitting.player_id, 
JOIN games on stats_hitting.game_id = games.game_id and (','+@month+',') like ('%,'+convert(varchar,month(game_date))+',%')
where profile_players.player_status_id in (1)
GROUP BY stats_hitting.player_id

Corey