When you need to pass a definite number of parameters to a stored procedure it’s quite simple. You should define as much parameters as needed. But when you need to pass a non-definite number of parameters (maybe 5, 7, 10 or even 100) it’s harder to implement.
There are some ways to make it:
- convert values to string, for example, separated with comma;
- pass as XML;
- use a table-valued parameter.
In the first two cases you must serialize and deserialize values. The last methods allows to use a parameter like a standard database table. I’ll use it to implement the stored procedure that save phone numbers of the client.
- create a table type for Phone Numbers
|
1 2 3 4 5 |
CREATE TYPE [dbo].[PhoneNumberType] AS TABLE ( PhoneNumber VARCHAR(20), PhoneTypeID SMALLINT ) |
- create a stored procedure
Important! We must declare a table-value parameter as READONLY.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE PROCEDURE [dbo].[SaveClientPhoneNumbers] @ClientGuid UNIQUEIDENTIFIER, @PhoneNumbers PhoneNumberType READONLY AS BEGIN SET NOCOUNT ON; DELETE FROM PhoneNumbers WHERE ClientGuid = @ClientGuid; INSERT INTO PhoneNumbers ( ClientGuid, PhoneNumber, PhoneTypeID ) SELECT @ClientGuid, PhoneNumber, PhoneTypeID FROM @PhoneNumbers; END |
Now we can declare a variable with type PhoneNumberType, insert some rows into it and call the stored procedure. I use this approach for unit test.
[UPDATE 2016-11-18]
There are some considerations about performance of table-valued parameters. Look at Jeremiah Peschka’s post to get more information Using SQL Server’s Table Valued Parameters