Stored Procedure - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

sqlnewbie10
Starting Member

8 Posts

Posted - 2010-03-17 : 07:00:30

Does anyone have any examples of passing multiple values into a parameter without using csv?

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 07:09:16

XML?

Table Valued Parameter in SQL2008?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-17 : 07:13:01

Sounds like you are looking for an array analogue.

From the desk of Erland Sommarskog, SQL Server MVP.

http://www.sommarskog.se/arrays-in-sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 07:14:25

Dynamic SQL for INSERT statements, or a UNION ALL statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 07:42:05

also

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 08:44:34

quote:
Originally posted by sqlnewbie10

Does anyone have any examples of passing multiple values into a parameter without using csv?

Why is it without using csv?

Madhivanan

Failing to plan is Planning to fail

sqlnewbie10
Starting Member

8 Posts

Posted - 2010-03-17 : 09:39:32

Thanks all for your help.

Hi visakh16
this is using csv

Hi madhivanan
because i already have a stored proc to execute as
EXECUTE myproc '1,2,3'
but will need to execute the proc as
EXECUTE myproc '1 2 3'
without comma's as this is how the data is recieved.

the procedure i had is as below

CREATE PROC dbo.GetCityDetails
(
@ID varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @GetID varchar(600)

SET @GetID =
'SELECT *
FROM dbo.tbl_table1
WHERE Id IN (' + @ID + ')'

EXEC(@GetID)
END
GO

is there a way to retrieve the same results without using csv or xml, thanks?

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 09:47:46

" but will need to execute the proc as
EXECUTE myproc '1 2 3'
without comma's as this is how the data is recieved"

CSV techniques let you choose the delimiter, so a space will be fine (if the CSV technique you have is fixed on "," for the delimiter there are others that let you choose the delimiter)

If you want to use the code style you have then change to:


SET @GetID = 
'SELECT *
FROM dbo.tbl_table1
WHERE Id IN (' + REPLACE(@ID, ' ', ',') + ')'

however beware that this method is weak on two counts: it requires permission on the table itself, rather than just Execute permission on the Stored Procedure, so is weaker from a permissions perspective.

Also it is susceptible to SQL Injection, and therefore you must add additional precautionary checks on @ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 09:58:57

quote:
Originally posted by sqlnewbie10

Thanks all for your help.

Hi visakh16
this is using csv

Hi madhivanan
because i already have a stored proc to execute as
EXECUTE myproc '1,2,3'
but will need to execute the proc as
EXECUTE myproc '1 2 3'
without comma's as this is how the data is recieved.

the procedure i had is as below

CREATE PROC dbo.GetCityDetails
(
@ID varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @GetID varchar(600)

SET @GetID =
'SELECT *
FROM dbo.tbl_table1
WHERE Id IN (' + @ID + ')'

EXEC(@GetID)
END
GO

is there a way to retrieve the same results without using csv or xml, thanks?

do you mean you didnt see other methods?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnewbie10
Starting Member

8 Posts

Posted - 2010-03-17 : 10:13:03

Thanks Kristen
Works a treat ;-)

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 11:40:51

Except that it sounds like you have ignored my advice on why this is a DANGEROUS way to tackle this problem ??

sqlnewbie10
Starting Member

8 Posts

Posted - 2010-03-17 : 17:10:48

Thanks again Kristen,

No, I have not ignored your concerns (sql injection attack) - i have taken this into consideration.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:02:45

quote:
Originally posted by sqlnewbie10

Thanks again Kristen,

No, I have not ignored your concerns (sql injection attack) - i have taken this into consideration.

Thanks

what have you done to avoid it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources