In clause in a proc - 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 2008 Forums

Transact-SQL (2008)

In clause in a proc

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-03-04 : 09:15:12

I am passing integers into the proc, but it does not work. Is there a way the get it to work. It is coming from a ssrs report multipledropdown. Here is my proc

ALTER PROCEDURE [PubWebdbo].[usp_PASORT_Read_RptByStatus]
(
@Statuses varchar(150)
)
AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.offender.offenderid,
dbo.offender.sid,
dbo.offender.firstname,
dbo.offender.middlename,
dbo.offender.lastname,
dbo.ncic_offenderstatus.DESCRIPTION,
dbo.offender.inmate_num
FROM dbo.offender
INNER JOIN dbo.ncic_offenderstatus
ON dbo.offender.offenderstatus = dbo.ncic_offenderstatus.id
WHERE ( dbo.offender.offenderstatus IN ( @Statuses ) )
ORDER BY dbo.offender.lastname,
dbo.offender.firstname

Dave
Helixpoint Web Development
http://www.helixpoint.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-04 : 09:21:09

SSRS doesn't support multi value parameters for stored procedures. Create it as a simple inline select instead and it'll work just fine.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-03-04 : 09:28:15

The reason I am running a proc is I need to run a scaler function to decript a ssn number. I did not add that in the sql. Decryptit

SELECT dbo.offender.offenderid,
dbo.offender.sid,
dbo.offender.firstname,
dbo.offender.middlename,
dbo.offender.lastname,
dbo.ncic_offenderstatus.DESCRIPTION,
dbo.offender.inmate_num,
(SELECT pasortdbo.Decryptit(dbo.offender.ssn, N'XXXXXX', 0) AS expr1)
AS ssn
FROM dbo.offender

Dave
Helixpoint Web Development
http://www.helixpoint.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-04 : 09:36:56

I don't get it...what does this have to do with you running the query as a procedure or not? The behavior is the same whether you have a udf in the select or not.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-03-04 : 09:45:58

They don't want the decrypt password in the report

Dave
Helixpoint Web Development
http://www.helixpoint.com

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-03-04 : 09:58:14

This looks like it might work

http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/232/using-the-in-clause-with-stored-procedures.aspx

Dave
Helixpoint Web Development
http://www.helixpoint.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources