need help on stored Proc "IN" parameter formatting

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

General SQL Server Forums

New to SQL Server Programming

need help on stored Proc "IN" parameter formatting

Author

Topic

richdiaz99
Starting Member

22 Posts

Posted - 2011-07-20 : 12:42:07

I have a simple SELECT proc that runs if I hardcode the "IN" values but when I change the "IN" values to a paramater the proc fails. Any help is much appreciated!

USE [BBTDB]
GO
/****** Object: StoredProcedure [blahdboblah].[BlahArchive]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [blahdboblah].[BlahArchive]
(
@IdList varchar(512)
)
AS
BEGIN
SET NOCOUNT ON;

SELECT
[pk]
,[BlahId]
,[Name]
,[Description]
,[PresentedDate]
,[PresentedTime]
,[DeactivateDate]

From [blahDb].[blahdboblah].[blahTable]
WHERE PresentedDate < GETDATE() AND DeactivateDate IS NULL
/* this works -> AND BlahId IN ('2','12','13','14','15') */
/*below does not when values passed in the above format*/
AND BlahIdIN (@IdList)
ORDER BY BlahId
END

/*THANKS!!*/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-20 : 13:04:20

Here is a link that talks about various ways to pass lists/arrays in SQL Server:
http://www.sommarskog.se/arrays-in-sql-2008.html

richdiaz99
Starting Member

22 Posts

Posted - 2011-07-20 : 13:24:31

quote:
Originally posted by Lamprey

Here is a link that talks about various ways to pass lists/arrays in SQL Server:
http://www.sommarskog.se/arrays-in-sql-2008.html

/head-explodes

that is a great article but I am afraid it is beyond my ability

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-20 : 13:31:01

and ','+@IdList+',' like '%,'+BlahId+',%'

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-20 : 13:38:13

Dynamic SQL works, but before using it take care of SQL Injection and security changes.

--
Gail Shaw
SQL Server MVP

richdiaz99
Starting Member

22 Posts

Posted - 2011-07-20 : 14:32:55

quote:
Originally posted by webfred

and ','+@IdList+',' like '%,'+BlahId+',%'

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

Thank you webfred. How would I call this in SQL Studio 2008? The below fails to work, I'm guessing I need to reformat the parameter?

EXEC [blahdboblah].[BlahArchive]'13,15'
EXEC [blahdboblah].[BlahArchive]('13','15')
EXEC [blahdboblah].[BlahArchive]('13,15')

Thanks GilaMonster. Yes, the application only accepts numbers and commas for the parameter.

Many thanks for the replies, there's no way I could of figured this out

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-20 : 14:39:42

quote:
Originally posted by richdiaz99

Thanks GilaMonster. Yes, the application only accepts numbers and commas for the parameter.

But you're passing it as a string. If you use dynamic SQL and concatenate that into the string (which is what webfred is suggesting) you will be vulnerable to SQL injection.

Maybe go read Erland's article again and ask for clarification on what's difficult for you. What you're trying to do here is not something SQL supports. It's not a matter of where brackets go, you cannot just pass a list of values to an IN. It is not the way it works.

Oh, if this is SQL 2008, go read up on table-type parameters.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources