SP gives conversion error - 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 2005 Forums

Transact-SQL (2005)

SP gives conversion error

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-16 : 00:56:26

Hi All,
I have the following proc which is used in a report. Of these @rid is multi-valued. but in the report i am getting conversion from nvarchar to int error.
Could any one help?

CREATE PROC sp_UserPerformance (@rid bigint,@fromdate datetime,@todate datetime,@locid bigint)
AS
BEGIN
Select UserName as 'User Name'
,isnull(( Select Sum(HC_REQ_TEAM.Position)
From HC_REQ_TEAM WITH(NOLOCK)
WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID
AND HC_REQ_TEAM.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 ))
and HC_REQ_TEAM.ReqID in(Select ReqID
From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings

From HC_USERS WITH(NOLOCK)
WHERE HC_USERS.RID in (@rid)
AND HC_USERS.Locationid in (@locid)
--(4,1,18,17 )
Group By HC_USERS.RID,HC_USERS.UserName
END

Sachin.Nand

2937 Posts

Posted - 2010-04-16 : 03:15:00

You mean to say the value set in @rid='1,2,3' and so on... and then you are trying something like where WHERE HC_USERS.RID in (@rid)

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 04:22:34

See here for different ways on how to work with multi-value-parms in SP.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources