List of Values to Stored Procedure

Question

Post reply

List of Values to Stored Procedure

DKG2014S

SSC Veteran

Points: 267
More actions
February 27, 2014 at 9:40 am

#284163

Hi,
I wrote a SP to pass a value to SP. It works well
ALTER PROC [dbo].[getCompleteItems] (@p_ItemID varchar(32))
AS
DECLARE @IntVariable char(32);
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
/* SQL String */
SET @SQLString =
N'select * from [dbo].[Complete_Items] where ITEM_ID = @p_ItemID';
/* Parameter Definition */
SET @ParmDefinition = N'@p_ItemID char(32)';
/* Parameter Value */
SET @IntVariable = @p_ItemID;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@p_ItemID = @IntVariable;
GO
The above SP works for only 1 item_id. How to pass several item_id's ???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I'd say do away with the dynamic SQL and use a table valued parameter.

John

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 2

I agree with Mitchell. This approach opens up systems to SQL injection attacks if the database is utilized by any interactive programming. Performance may be subpar. this seems also like it would be difficult to maintain or document.

Thanks

John.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

A Table Valued Parameter could be a good option. If you have your @p_ItemID as several items separated by commas (or another delimiter) you could use a splitter as well. Here's an option using the DelimitedSplit8K. For information on it, check this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

ALTER PROC [dbo].[getCompleteItems] (@p_ItemID varchar(32))

AS

SELECT *

FROM [dbo].[Complete_Items] ci

WHERE EXISTS( SELECT 1

FROM [dbo].[DelimitedSplit8K]( @p_ItemID, ',') s

WHERE ci.ITEM_ID = s.Item);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

JohnFTamburo (2/27/2014)
This approach opens up systems to SQL injection attacks if the database is utilized by any interactive programming.

Can you explaing how is this vulnerable to SQL Injection? It seems that this is the correct way to use dynamic SQL, it's just the wrong place.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 5

Luis Cazares (2/27/2014)
JohnFTamburo (2/27/2014)
This approach opens up systems to SQL injection attacks if the database is utilized by any interactive programming.
Can you explaing how is this vulnerable to SQL Injection? It seems that this is the correct way to use dynamic SQL, it's just the wrong place.

I'm re-reviewing this. I may just stand corrected here regarding injection, since the dynamic SQL uses a parameter.

Thanks

John.