Alter Stored Procedure

Question

I currently have a stored procedure that I want to take on a flexible number of arguments. Does anyone know if that is possible?

ALTER PROCEDURE myStoredProc
    (@variable1 INT, 
     @variable2 INT, 
     @variable3 INT)

The example above, I have 3 parameters. But is it possible to have N number? And in the body have loops that go over the N number of variables for some sort of execution?

Thank you.

Anytime you think "I need a variable number of variables", instead think "I need an array". [This question may help out if it's mysql](https://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine) — JNevill, Jun 05 '17 at 14:45
If SQL Server use **[TVP](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine)** — Lukasz Szozda, Jun 05 '17 at 14:48
Does this help? https://stackoverflow.com/questions/7462552/stored-procedure-with-variable-number-of-parameters — coolswastik, Jun 05 '17 at 14:49

Mehdi akbari · Answer 1 · 2017-06-05T15:55:04.810

In SqlServer you can use Xml Parameter and pass all parameters as a Xml. Your procedure looks like this :

Create procedure myStoredProc (@xml xml)
as
SELECT   
c.value('(.)', 'VARCHAR(8000)') AS paramvalue
FROM   @xml.nodes('/Root/param') T(c)  
GO

This procedure parse simply the input Xml and selects all parameters.

And you can call your stored procedure with a Xml Parameter as following

DECLARE @xml xml   
SET @xml='<Root>  
    <param>1</param>  
    <param>2</param>  
    <param>3</param>  
</Root>' 

exec myStoredProc @xml

Alter Stored Procedure

1 Answers1