> I would like to implement a stored procedure as a CLR one.
>
> CREATE PROCEDURE MultiParamSP (
> @a nvarchar(100) = '',
> @b nvarchar(100) = '',
> [..., n]
> )
> AS
> EXTERNAL NAME ...;
>
> I would like MultiParamSP to support undefined at compile time number of
> nvarchar(100)
> parameters.
>
> It's easy to declare the corresponding method in C# code :
> public static void MultiParamSP (params string[] str)
> {
> //
> }
>
> But what should the "CREATE PROCEDURE MultiParamSP" script looks like when
> it is time to declare the parameters?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns986DF15115660Yazorman@127.0.0.1...
> ggeshev (ggeshev@tonegan.bg) writes:
>> I would like to implement a stored procedure as a CLR one.
>>
>> CREATE PROCEDURE MultiParamSP (
>> @a nvarchar(100) = '',
>> @b nvarchar(100) = '',
>> [..., n]
>> )
>> AS
>> EXTERNAL NAME ...;
>>
>> I would like MultiParamSP to support undefined at compile time number of
>> nvarchar(100)
>> parameters.
>>
>> It's easy to declare the corresponding method in C# code :
>> public static void MultiParamSP (params string[] str)
>> {
>> //
>> }
>>
>> But what should the "CREATE PROCEDURE MultiParamSP" script looks like
>> when
>> it is time to declare the parameters?
>
> I'm afraid that you are not in Kansas anymore.
>
> I don't really know what you want to do, but look at an article of
> mine for ideas: http://www.sommarskog.se/arrays-in-sql.html.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

> Now with MSSQL Server 2005 I would like to throw away this "extnded SP"
> way of doing this because Microsoft announce a wish to leave off
> extended stored procedures. So I wish to implement the same MultiParamSP
> stored procedure as a CLR one. And the main idea is NOT TO CHANGE THE
> SOURCE OF THE T-SQL STORED PROCEDURES WHICH CALL MultiParamSP.
> So the calls should remain :
> exec MultiParamSP 'aa', 'bb'
> or
> exec MultiParamSP 'aa', 'bb', 'cc'
> or
> exec MultiParamSP 'aa', 'bb', 'cc' 'dd'.

"ggeshev" <ggeshev@tonegan.bg> wrote in message
news:OiztJkY$GHA.3536@TK2MSFTNGP04.phx.gbl...
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns986DF15115660Yazorman@127.0.0.1...
>> ggeshev (ggeshev@tonegan.bg) writes:
>>> I would like to implement a stored procedure as a CLR one.
>>>
>>> CREATE PROCEDURE MultiParamSP (
>>> @a nvarchar(100) = '',
>>> @b nvarchar(100) = '',
>>> [..., n]
>>> )
>>> AS
>>> EXTERNAL NAME ...;
>>>
>>> I would like MultiParamSP to support undefined at compile time number of
>>> nvarchar(100)
>>> parameters.
>>>
>>> It's easy to declare the corresponding method in C# code :
>>> public static void MultiParamSP (params string[] str)
>>> {
>>> //
>>> }
>>>
>>> But what should the "CREATE PROCEDURE MultiParamSP" script looks like
>>> when
>>> it is time to declare the parameters?
>>
>> I'm afraid that you are not in Kansas anymore.
>>
>> I don't really know what you want to do, but look at an article of
>> mine for ideas: http://www.sommarskog.se/arrays-in-sql.html.
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>
> Thank you for the article, Erland!
>
> But I need a solution to the question exactly as I defined it!
>
> Look, in many, really many existng T-SQL stored procedures I have calls to
> the MultiParamSP in the various forms:
> exec MultiParamSP 'aa', 'bb'
> or
> exec MultiParamSP 'aa', 'bb', 'cc'
> or
> exec MultiParamSP 'aa', 'bb', 'cc', 'dd'
>
> Now MultiParamSP has no fixed number of VARCHAR(100) parameters, because
> it is implemented as an extended stored procedure and the declaration is
> as follows :
>
> EXEC sp_addextendedproc MultiParamSP, 'MultiParamSP.dll'.
> As you see this way of declaration does not fix the number of parameters
> the SP receives.
>
> Now with MSSQL Server 2005 I would like to throw away this "extended SP"
> way of doing this because Microsoft announce a wish to leave off extended
> stored procedures.
> So I wish to implement the same MultiParamSP stored procedure as a CLR
> one.
> And the main idea is NOT TO CHANGE THE SOURCE OF THE T-SQL STORED
> PROCEDURES WHICH CALL MultiParamSP.
> So the calls should remain :
> exec MultiParamSP 'aa', 'bb'
> or
> exec MultiParamSP 'aa', 'bb', 'cc'
> or
> exec MultiParamSP 'aa', 'bb', 'cc' 'dd'.
>
> Thank you, Erland!
>

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns986E802CD40D1Yazorman@127.0.0.1...
> ggeshev (ggeshev@tonegan.bg) writes:
>> Now with MSSQL Server 2005 I would like to throw away this "extnded SP"
>> way of doing this because Microsoft announce a wish to leave off
>> extended stored procedures. So I wish to implement the same MultiParamSP
>> stored procedure as a CLR one. And the main idea is NOT TO CHANGE THE
>> SOURCE OF THE T-SQL STORED PROCEDURES WHICH CALL MultiParamSP.
>> So the calls should remain :
>> exec MultiParamSP 'aa', 'bb'
>> or
>> exec MultiParamSP 'aa', 'bb', 'cc'
>> or
>> exec MultiParamSP 'aa', 'bb', 'cc' 'dd'.
>
> Unfortunately, there is no way out. If you want to replace the XP with a
> CLR, you will need to find a new interface for it. Or implement your
> CLR procedure with 20 parameters, or whatever is the maximum number of
> parameters specified in a call to the XP today.
>
> ...but since extended stored procedures reside in master, I guess you will
> need to change the calls anyway to remove the "master.." stuff.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx