CODEWITHSUNDEEP
sql-serverstored-proceduresparameters
Kumara
Kumara

Reputation: 291

How to pass a variable number of parameters to a SQL Server stored procedure?

I used SQL Server 2005 for my small web application. I Want pass parameters to SP . But there is one condition. number of parameter that can be change time to time. Think ,this time i pass name and Address , next time i pass name,surname,address ,

this parameter range may be 1-30 ,

Upvotes: 13

Views: 27670

Answers (4)

Anup Shah
Anup Shah

Reputation: 1254

Preparing and exctracting tags from XML is aperformance killer. The inconsistant execution timings depends on whether plan is cached or not.

Using NVARCHAR(MAX) instead is a better option. Just prepare you parameter strings with "@name=Value" pair, and add a unique parameter sepearetor if needed. Inside the procedure use SUBSTRING, CHARINDEX, etc. to get individual parameters.

Upvotes: 1

stackoverflow
stackoverflow

Reputation: 455

You can use XML types. Here is a sample procedure:

CREATE PROCEDURE udsp_VariableParams(@params XML)
AS
BEGIN
    DECLARE @vdoc INT
    EXEC sp_xml_preparedocument @vdoc OUTPUT, @params
    SELECT * FROM OPENXML(@vdoc, '/params/param', 1) WITH ([paramname] VARCHAR(40), [paramvalue] VARCHAR(150))
    EXEC sp_xml_removedocument @vdoc    
END

EXEC udsp_VariableParams 
  '<params>
     <param paramname="name" paramvalue="value"/>
     <param paramname="address" paramvalue="value"/>
  </params>'

EXEC udsp_VariableParams 
  '<params>
     <param paramname="name" paramvalue="value"/>
     <param paramname="surname" paramvalue="value"/>
     <param paramname="address" paramvalue="value"/>
  </params>'

Upvotes: 10

Ray Lu
Ray Lu

Reputation: 26648

You can set the default values for the parameters at the SP level so that the parameters become optional.

e.g.

CREATE PROCEDURE dbo.Test
 @param1 int, -- Not an optional
 @param2 int = 10, --Optional
 @param3 bit = 0, --Optional
 @param4 varchar(50) = '', --Optional
 @param5 nvarchar(100) = null --Optional
 -- more parameters ..... 
AS
BEGIN

 -- The SQL goes here...

END

Then you can populate the optional parameters at your choice.

Upvotes: 6

Remus Rusanu
Remus Rusanu

Reputation: 294197

You declare the procedure with default parameters and you invoke it with named parameters instead of positional parameters:

CREATE PROCEDURE usp_myProcedure
  @name varchar(100) = '',
  @surname varchar(100) = '',
  @address varchar(100) = ''
AS
BEGIN
...
END

to invoke it from T-SQL:

exec usp_myProcedure @name='John', @surname = 'Doe';
exec usp_myProcedure @name='Jane', @address = '123 Anystreet';

To invoke it from C#:

SqlCommand cmd = new SqlCommand('usp_MyProcedure', ...);
cmd.CommandType = commandtype.StoredProcedure;
cmd.Parameters.AddWithValue('@name', 'John');
cmd.Parameters.AddWithValue('@surname', 'Doe');

Upvotes: 12

Related Questions