Pass a list-structure as an argument to a stored procedure
Is it possible to pass a Dictionary<String, String>
(or something similar; like a key/value pair) as an argument to a stored procedure on an MS SQL 2005 server?
I'm looking for a practic开发者_JAVA技巧al example.
Update
This question lead me to ask this one.
You need a way to split and process the string in TSQL, there are many ways to do this. This article covers the PROs and CONs of just about every method:
"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog
You need to create a split function. This is how a split function can be used:
SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers
that contains rows from 1 to 10,000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Once the Numbers table is set up, create this split function:
CREATE FUNCTION [dbo].[FN_ListToTableRows]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this will return empty rows, and row numbers
----------------
SELECT
ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS ListValue
) AS InnerQuery
INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
WHERE SUBSTRING(ListValue, number, 1) = @SplitOn
);
GO
You can now easily split a CSV string into a table and join on it. To accomplish your task, sif you can pass in two parameters, one of keys one of values, then create your procedure like this:
CREATE PROCEDURE StoredProcedureName
(
@Params1 int
,@Array1 varchar(8000)
,@Params2 int
,@Array2 varchar(8000)
)
AS
DECLARE @YourTable table (col1 int, col2 int)
INSERT INTO @YourTable
(col1, col2)
SELECT
a1.ListValue, a2.ListValue
FROM dbo.FN_ListToTableRows(',',@Array1) a1
INNER JOIN dbo.FN_ListToTableRows(',',@Array2) a2 ON a1.RowNumber=a2.RowNumber
select * from @YourTable
GO
test it out:
exec StoredProcedureName 17,'127,204,110,198',7,'162,170,163,170'
OUTPUT:
(4 row(s) affected)
col1 col2
----------- -----------
127 162
204 170
110 163
198 170
(4 row(s) affected)
or if you want to pass in a single parameter key value pair use something like this:
CREATE PROCEDURE StoredProcedureName
(
@KeyValueList varchar(8000)
)
AS
DECLARE @YourTable table (RowKey varchar(500), RowValue varchar(500))
INSERT INTO @YourTable
(RowKey, RowValue)
SELECT
LEFT(y.ListValue,CHARINDEX(',',y.ListValue)-1),RIGHT(y.ListValue,LEN(y.ListValue)-CHARINDEX(',',y.ListValue))
FROM dbo.FN_ListToTableRows(';',@KeyValueList) y
SELECT * FROM @YourTable
GO
run it:
exec StoredProcedureName 'a,5;b,BBB;abc,xyz'
OUTPUT:
RowKey RowValue
------- -----------
a 5
b BBB
abc xyz
(3 row(s) affected)
Format this Dictionary into an XML and pass this over to SQL Server. It understands the XML datatype and can query it.
<Data>
<Entry Key="1" Value="One" />
<Entry Key="2" Value="Two" />
</Data>
And pass this as SqlDataType.Xml. SQL 2005 and later is able to query from XML. There are plenty examples of how to work with XML in SQL Server.
精彩评论