Stored procedure with variable number of parameters
I have stored procedure where I ha开发者_如何学Pythonve to pass parameters, But the problem is I am not sure how many parameters is going to come it can be 1, in next run it can be 5.
cmd.Parameters.Add(new SqlParameter("@id", id)
Can anyone help how can I pass these variable number of parameters in stored procedure? Thanks
You could pass it in as a comma-separated list, then use a split function, and join against the results.
CREATE FUNCTION dbo.SplitInts
(
@List VARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
(
SELECT Item = CONVERT(INT, Item)
FROM
(
SELECT Item = x.i.value('(./text())[1]', 'INT')
FROM
(
SELECT [XML] = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a
CROSS APPLY
[XML].nodes('i') AS x(i)
) AS y
WHERE Item IS NOT NULL
);
Now your stored procedure:
CREATE PROCEDURE dbo.doStuff
@List VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT cols FROM dbo.table AS t
INNER JOIN dbo.SplitInts(@List, ',') AS list
ON t.ID = list.Item;
END
GO
Then to call it:
EXEC dbo.doStuff @List = '1, 2, 3, ...';
You can see some background, other options, and performance comparisons here:
- Split strings the right way – or the next best way
- Splitting Strings : A Follow-Up
- Splitting Strings : Now with less T-SQL
- Comparing string splitting / concatenation methods
- Processing a list of integers : my approach
- Splitting a list of integers : another roundup
- More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
- Removing Duplicates from Strings in SQL Server
On SQL Server 2016 or above, though, you should look at STRING_SPLIT()
and STRING_AGG()
:
- Performance Surprises and Assumptions : STRING_SPLIT()
- STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
- STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
- SQL Server v.Next : STRING_AGG() performance
- Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions
SQLServer lets you pass TABLE
parameter to the stored procedure. So you can define table type, CREATE TYPE LIST_OF_IDS AS TABLE (id int not null primary key)
, alter your procedure to accept a variable of this type (it should be readonly).
Stored procedures support optional parameters. Like C# 4, you can specify a default value using =
. For example:
create procedure dbo.doStuff(
@stuffId int = null,
@stuffSubId int = null,
...)
as
...
For parameters you don't want to pass, either set them to null
or don't add them to cmd.Parameters
at all. They will have their default value in the stored procedure
Have you considered using dictionary for that purpose? It will allow you to pass any number of parameters as key-value pairs. Then you'll need just to go through the dictionary and add those parameters to cmd.
void DoStuff(Dictionary<string, object> parameters)
{
// some code
foreach(var param in parameters)
{
cmd.Parameters.Add(new SqlParameter(param.Key, param.Value);
}
// some code
}
In stored procedure itself you'll need to specify default values for the parameters.
CREATE PROCEDURE DoStuff(
@id INT = NULL,
@value INT = NULL,
-- the list of parameters with their default values goes here
)
AS
-- procedure body
Here is a code snippet that splits a string based on ,
as delimiter. You can even parametrize the comma. It's useful on systems that don't have the String_split
function yet:
DECLARE @startindex INT
DECLARE @commaindex INT
DECLARE @paramAsString VARCHAR(MAX) -- this represents the input param
DECLARE @param VARCHAR (1024)
DECLARE @paramsTable TABLE(param VARCHAR(1024) NOT NULL) -- the splitted params come here
SET @startindex = 1
WHILE @startindex < LEN(@paramAsString)
BEGIN
SET @commaindex = CHARINDEX(',', @paramAsString, @startindex)
IF @commaindex = 0
BEGIN
SET @param = SUBSTRING(@paramAsString, @startindex, LEN(@paramAsString))
SET @startindex = LEN(@settlementEntities)
END
ELSE
BEGIN
SET @param = SUBSTRING(@paramAsString, @startindex, (@commaindex - @startindex))
SET @startindex = @commaindex + 1
END
IF @se IS NOT NULL AND 0 < LEN(RTRIM(LTRIM(@param)))
BEGIN
SET @param = RTRIM(LTRIM(@param))
INSERT INTO @paramsTable (param) VALUES (@param)
END
END
Another way you could do this is by serializing the arguments as a JSON string and passing that into your stored procedure. You can use the NewtonSoft.JSON package to do something like this: string json = JsonConvert.SerializeObject(obj);
and pass the json
var into your proc.
if you have Sql Server 2008 or later, you can use a table valued parameter...
https://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/
精彩评论