June 15, 2006 at 2:03 pm
I am triying ot pass a List like 'A', 'B', 'C', 'D'
Like one argument for a Store Procedure like this:
CREATE PROCEDURE Test @List varchar(50) AS
SELECT * FROM Types
WHERE Type IN (@List)
GO
EXEC Test "'A', 'B', 'C', 'D'"
I dont obtain Nothing in the Store Procedure
What its wrong???
Thaks for your help
June 15, 2006 at 2:34 pm
Pass them in as XML and then read the XML into a temp table.
<PARMS><VAL>A</VAL><VAL>B</VAL>.....</PARMS>
On how to read XML look up
EXEC sp_xml_preparedocument and sp_xml_removedocument
Thanks.
June 15, 2006 at 11:01 pm
WTF?
For this you need dynamic SQL.
CREATE PROCEDURE Test
(
@List varchar(50)
)
AS
DECLARE @SQL VARCHAR(1000)
SELECT @List = REPLACE(@List, '"', CHAR(39))
SELECT @SQL = 'SELECT * FROM Types WHERE Type IN (' + @List + ')'
EXEC (SQL)
GO
Call with
EXEC Test '"A", "B", "C", "D"'
N 56°04'39.16"
E 12°55'05.25"
June 16, 2006 at 1:18 am
What do you mean WTF?
XML is a perfectly valid way of passing multiple parameters (and full records) to a stored proc without the need to resort to dynamic SQL.
Carlos, your other alternative is to use one of the many string split udf's (found on this site or elsewhere) to convert your delimited parameter string into a table and do an inner join.
June 16, 2006 at 1:34 am
Why use XML and use additional resources from SQL Server when not needed?
N 56°04'39.16"
E 12°55'05.25"
June 16, 2006 at 1:38 am
Because dynamic sql is almost always a bad idea, especially when you're executing it like this with no validation or checking for sql injection.
June 16, 2006 at 2:58 am
As the question is written in the original posting, I am assuming that the client application is sending the list to the database. Hopefully the programmer of the client application has done some rudimentary checks that only some OPTION values are passed to the database, not free text from end-user.
So how would you stop the possibility of SQL injection with SQL in the original posting using XML? Same procedure as you stated, convert and insert into table variable and the join?
There is another possibilty with string list
CREATE PROCEDURE Test
(
@List varchar(8000)
)
AS
CREATE TABLE #Values (Value VARCHAR(100))
SELECT @List = REPLACE(@List, '",', char(39) + ' union all select')
SELECT @List = REPLACE(@List, '"', char(39))
EXEC ('INSERT #Values SELECT ' + @List)
SELECT * FROM MyTable INNER JOIN #Values ON #Values.Value = MyTable.MyField
DROP TABLE #Values
N 56°04'39.16"
E 12°55'05.25"
June 16, 2006 at 3:20 am
SQL injection doesn't need to come from free text, it's pretty simple to create a bespoke HTML post simulating OPTION values containing whatever you want.
It's not a point that I think needs to be laboured any more I'm just saying the initially proposed solution to use XML is totally acceptable and didn't warrant a WTF response. It is also, IMHO, slightly more elegant.
Below is an example using xml doc.
DECLARE @Types TABLE
(
Type char(1) NOT NULL
)
INSERT INTO @Types VALUES ('A')
INSERT INTO @Types VALUES ('B')
INSERT INTO @Types VALUES ('C')
INSERT INTO @Types VALUES ('D')
INSERT INTO @Types VALUES ('E')
INSERT INTO @Types VALUES ('F')
INSERT INTO @Types VALUES ('G')
INSERT INTO @Types VALUES ('H')
INSERT INTO @Types VALUES ('I')
INSERT INTO @Types VALUES ('J')
DECLARE @params nvarchar(1000),
@doc int
SET @params = N'<PARMS><VAL>A</VAL><VAL>B</VAL><VAL>C</VAL></PARMS>'
EXEC sp_xml_preparedocument @doc OUTPUT, @params
SELECT *
FROMOPENXML(@doc, '/PARMS/VAL', 2) WITH
(
ParamVal char(1) '.'
) Params INNER JOIN @Types t ON Params.ParamVal = t.Type
EXEC sp_xml_removedocument @doc
June 16, 2006 at 3:46 am
Hi, few weeks ago I wrote an article in our blog about how to pass an array into stored procedure. You can check it on http://www.syntacticsugar.com/blog/index.php?cat=26
June 16, 2006 at 4:53 am
This is a FAQ, and here are the usual references...
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sql-server-performance.com/mm_list_random_values.asp
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply