SQL Server stored procedure

Question

Is it possible to write stored procedure that works with a variable number of parameters? (in SQL Server 2005)

Answer 1

Yes, just set a default value:

CREATE PROCEDURE SomeProc
    @SomeParam int,
    @SomeParam2 varchar(20) = 'Test Text'
AS
...

Then you can execute as:

EXEC SomeProc 1

Answer 2

Yes, this is possible. When I needed this I used Arrays and Lists in SQL Server. It will walk you through the details of a few different approaches.

Answer 3

Yes, you could write a stored procedure in C# or VB.NET and include it as a SQLCLR stored procedure.

A C# method can accept a params parameter of variable length.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertSomeValues(SqlString currencyCode, 
                                        SqlString name, 
                                        params object[] args)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            ...... do whatever you need to do here........
        }
    }
}

The SQL CLR - the inclusion of the .NET runtime into SQL Server - was introduced with SQL Server 2005. See the MSDN docs for more details.

Answer 4

Some methods..

Defaults values Table variable CLR store Procedures delimited strings

Answer 5

One way to do this is by using XML:

CREATE PROC dbo.GetOrderList (
    @OrderList varchar(max)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @DocHandle int

    DECLARE @TBL TABLE (
        paramname varchar(50),
        paramvalue varchar(50)
    )

    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @OrderList

    INSERT INTO @TBL (
        paramname,
        paramvalue
    )
    SELECT
        paramname,
        paramvalue
    FROM OPENXML (@DocHandle, '/ROOT/param', 1) WITH (
        paramname,
        paramvalue
    )

    EXEC sp_xml_removedocument @DocHandle 
END
GO

GRANT EXEC ON dbo.GetOrderList TO public
GO

Then your xml would look like this:

<root>
    <param>
        <paramname>thisparam</paramname>
        <paramvalue>1</paramvalue>
    </param>
</root>