Accepting Dynamic Size of Parameters in Stored Procs

[Al Davis]

Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Bill]

One option is to do something more like:

exec sp_myproc "a;b;c"

You can use char_index to loop thru the string, i.e. parse it and count the number of tokens.


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Anu]

Hi,

please specify what exactly you need to do and how number of parameters becomes inconsistent.

Depends on the you clear problem definition I may be able to give you a better solution.

-Anu



------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Albert Davis]

Thanks for your reply, but what I was really trying to find out was how to send parameters to a stored proc that was unknown to the stored proc. This in turn would allow the stored proc to figure out how many params were sent dynamically (all using the same stored proc).
So if I had a stored proc called sp_mine and passed:
exec sp_mine "a", "b"
The stored proc would figure out that the caller has passed 2 params. And if I called the same stored proc and passed:
exec sp_mine "a", "b", "c", "d"
The stored proc would figure out that the caller has passed 4 params. Thus, this would allow the stored proc not to be "hard-coded" and would support any number of params sent...
Do you have any ideas how this would work?
FYI, I will be calling this from sp from an ODBC enabled application like "asp"... so being able to call this sp with dynamically constructed params unknown to the sp is what I would like the sp to support.
Thanx in advance,
-- Al



------------
Anu at 7/6/01 2:06:25 PM

Hi,

please specify what exactly you need to do and how number of parameters becomes inconsistent.

Depends on the you clear problem definition I may be able to give you a better solution.

-Anu



------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Albert Davis]

Thanks for your reply, but what I was really trying to find out was how to send parameters to a stored proc that was unknown to the stored proc. This in turn would allow the stored proc to figure out how many params were sent dynamically (all using the same stored proc).
So if I had a stored proc called sp_mine and passed:
exec sp_mine "a", "b"
The stored proc would figure out that the caller has passed 2 params. And if I called the same stored proc and passed:
exec sp_mine "a", "b", "c", "d"
The stored proc would figure out that the caller has passed 4 params. Thus, this would allow the stored proc not to be "hard-coded" and would support any number of params sent...
Do you have any ideas how this would work?
FYI, I will be calling this from sp from an ODBC enabled application like "asp"... so being able to call this sp with dynamically constructed params unknown to the sp is what I would like the sp to support.
Thanx in advance,
-- Al



------------
Bill at 7/6/01 1:52:13 PM

One option is to do something more like:

exec sp_myproc "a;b;c"

You can use char_index to loop thru the string, i.e. parse it and count the number of tokens.


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Bill]

I'm not *exactly* sure what you are saying, but if the requirement is to called the stored proc in the format of

exec sp_mine "a", "b", "c", "d"

... the only solution is of brute-force type, which I'd assumed you'd already ix-nayed. If there is an upper limit on the number of params, let's say 4, you could have

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @4=NULL
as
select "hello, world"

The null declarations allow you to not specify that parameter when executing, so you could

exec sp_mine 'a'
exec sp_mine 'a', 'b'
etc.

But in order to count the params, you'd have to have some ugly

If @4 is not null then @NumberOfParams = 4
else if @3 is not null...


------------
Albert Davis at 7/6/01 4:01:58 PM

Thanks for your reply, but what I was really trying to find out was how to send parameters to a stored proc that was unknown to the stored proc. This in turn would allow the stored proc to figure out how many params were sent dynamically (all using the same stored proc).
So if I had a stored proc called sp_mine and passed:
exec sp_mine "a", "b"
The stored proc would figure out that the caller has passed 2 params. And if I called the same stored proc and passed:
exec sp_mine "a", "b", "c", "d"
The stored proc would figure out that the caller has passed 4 params. Thus, this would allow the stored proc not to be "hard-coded" and would support any number of params sent...
Do you have any ideas how this would work?
FYI, I will be calling this from sp from an ODBC enabled application like "asp"... so being able to call this sp with dynamically constructed params unknown to the sp is what I would like the sp to support.
Thanx in advance,
-- Al



------------
Bill at 7/6/01 1:52:13 PM

One option is to do something more like:

exec sp_myproc "a;b;c"

You can use char_index to loop thru the string, i.e. parse it and count the number of tokens.


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d",&# 34;e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Albert Davis]

Thanks for the reply,

Ok let me explain what I am trying to accomplish. I have a CRM system that uses data type Binary(8) when a new record is inserted for its internal primary key on that record. This 64-bit primary key is used for programmatic tracking purposes. We use ASP.NET as the vehicle that drives the middle-tier integration piece. The program language of choice for this project is C# using the .NET Framework SDK. I am using the System.Data.SqlClient adaptors to natively communicate to SQL Server 7.0 thus providing excellent speed and cached access. There are sales agents that&#39;s belong to several companies which have several products that can be sold per company. So we have a one-to-many for employees-to-companies, and a one-to-many for companies-to-products. I have a stored proc that is used to gather all of the pertinent information about products that are available to companies based upon the companies Binary(8) primary key. I have built numerous utility classes that convert this Binary(8) into a nice hex string for query string purposes over the web in the format of &#34;0x0000000000000000&#34; when retrieving records via a DataSet. I don&#39;t need conversion classes for converting the hex string back to binary when trying to retrieve records based upon this hex string due to the fact that the drivers automatically convert this hex string into a Binary(8) data type for me (kind of like how if you select a Binary data type in SQL Analyzer, the data grid doesn&#39;t show you <binary> [like in Enterprise Manager] but instead it nicely converts the Binary(8) format into a hex string representation. Something like this:

[C#]

string sConn= &#34;server=yourserver;uid=sa;pwd=;database=db_pro ducts&#34;;

string sSql= &#34;SELECT * FROM PRODUCTS WHERE PRODUCT_ID = 0x0000000000000FAC&#34;;

DataSet dataSetAuth = new DataSet(); //Create a data set.

SqlConnection sqlConn = new SqlConnection(sConn); //Create a data connection.

SqlDataAdapter sqlAdapter = new SqlDataAdapter(); //Associate the connection with Sql Server.

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

sqlAdapter.Fill(dataSetAuth); //Fill the data set with the results of the sql statement.

DataTableCollection dataTblCollection = dataSetAuth.Tables; //Grab the collection of tables.

foreach(DataRow oDr in dataTblCollection[0].Rows) {

Console.WriteLine(oDr[&#34;Product_Name&#34;].ToString());

}

dataTblCollection = null;

sqlAdapter = null;

sqlConn = null;

dataSetAuth = null;



So here is my dilemma, remember when I talked about employees belong to many companies? and I mentioned that companies have certain products defined to them available to be sold? So I have all of the work pretty much done, with 2 or 3 work arounds if I can&#39;t get what I am achieving to work. What I would like to do is call a stored proc to get all of the products available via the company Binary(8) primary keys that they belong to. But the problem is one employee could belong to 2 companies so the call would look something like this in code:


***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

where another employee could have 4 companies that they belong to:

***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB,0x0000000000 0000FC,0x00000000000000FD&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

But as you can see the problem here is that I want to use the same stored proc and pass it as many params as I need without the INPUT of it being &#34;hard-coded&#34; within the proc itself. I would like the proc to &#34;figure out&#34; how many params were passed to it &#34;dynamically&#34; and then conduct business as usual. I can&#39;t do any string concatenation and then splitting of them due to the data type being Binary(8). I&#39;ve tried to send just a simple hex string of &#34;0x000000000000000A&#34; and converting it to Binary(8) and then doing a select on that variable but the convert doesn&#39;t convert the hex string properly.

I hope that I explained it well enough....

Any suggestions are greatly appreciated and again thanks for your help!

-- Al




------------
Bill at 7/6/01 5:54:57 PM

I&#39;m not *exactly* sure what you are saying, but if the requirement is to called the stored proc in the format of

exec sp_mine &#34;a&#34;, &#34;b&#34;, &#34;c&#34;, &#34;d&#34;

... the only solution is of brute-force type, which I&#39;d assumed you&#39;d already ix-nayed. If there is an upper limit on the number of params, let&#39;s say 4, you could have

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @4=NULL
as
select &#34;hello, world&#34;

The null declarations allow you to not specify that parameter when executing, so you could

exec sp_mine &#39;a&#39;
exec sp_mine &#39;a&#39;, &#39;b&#39;
etc.

But in order to count the params, you&#39;d have to have some ugly

If @4 is not null then @NumberOfParams = 4
else if @3 is not null...


------------
Albert Davis at 7/6/01 4:01:58 PM

Thanks for your reply, but what I was really trying to find out was how to send parameters to a stored proc that was unknown to the stored proc. This in turn would allow the stored proc to figure out how many params were sent dynamically (all using the same stored proc).
So if I had a stored proc called sp_mine and passed:
exec sp_mine &#34;a&#34;, &#34;b&#34;
The stored proc would figure out that the caller has passed 2 params. And if I called the same stored proc and passed:
exec sp_mine &#34;a&#34;, &#34;b&#34;, &#34;c&#34;, &#34;d&#34;
The stored proc would figure out that the caller has passed 4 params. Thus, this would allow the stored proc not to be &#34;hard-coded&#34; and would support any number of params sent...
Do you have any ideas how this would work?
FYI, I will be calling this from sp from an ODBC enabled application like &#34;asp&#34;... so being able to call this sp with dynamically constructed params unknown to the sp is what I would like the sp to support.
Thanx in advance,
-- Al



------------
Bill at 7/6/01 1:52:13 PM

One option is to do something more like:

exec sp_myproc &#34;a;b;c&#34;

You can use char_index to loop thru the string, i.e. parse it and count the number of tokens.


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Craig Somberg]

Hi.

Not gonna happen ...

Try loading a temp table with the key values you want and then do a join to that table or build a dynamic sql statement and pass that in for execution.

Craig


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Todd]

In what way does this sort of thing not meet your needs? You simply just have to declare enough params.

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @n=NULL


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Albert Davis]

Thanks for the reply,

Ok let me explain what I am trying to accomplish without &#34;hard-coding&#34; input params in the stored proc. I have a CRM system that uses data type Binary(8) when a new record is inserted for its internal primary key on that record. This 64-bit primary key is used for programmatic tracking purposes. We use ASP.NET as the vehicle that drives the middle-tier integration piece. The program language of choice for this project is C# using the .NET Framework SDK. I am using the System.Data.SqlClient adaptors to natively communicate to SQL Server 7.0 thus providing excellent speed and cached access. There are sales agents that&#39;s belong to several companies which have several products that can be sold per company. So we have a one-to-many for employees-to-companies, and a one-to-many for companies-to-products. I have a stored proc that is used to gather all of the pertinent information about products that are available to companies based upon the companies Binary(8) primary key. I have built numerous utility classes that convert this Binary(8) into a nice hex string for query string purposes over the web in the format of &#34;0x0000000000000000&#34; when retrieving records via a DataSet. I don&#39;t need conversion classes for converting the hex string back to binary when trying to retrieve records based upon this hex string due to the fact that the drivers automatically convert this hex string into a Binary(8) data type for me (kind of like how if you select a Binary data type in SQL Analyzer, the data grid doesn&#39;t show you <binary> [like in Enterprise Manager] but instead it nicely converts the Binary(8) format into a hex string representation. Something like this:

[C#]

string sConn= &#34;server=yourserver;uid=sa;pwd=;database=db_pro ducts&#34;;

string sSql= &#34;SELECT * FROM PRODUCTS WHERE PRODUCT_ID = 0x0000000000000FAC&#34;;

DataSet dataSetAuth = new DataSet(); //Create a data set.

SqlConnection sqlConn = new SqlConnection(sConn); //Create a data connection.

SqlDataAdapter sqlAdapter = new SqlDataAdapter(); //Associate the connection with Sql Server.

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

sqlAdapter.Fill(dataSetAuth); //Fill the data set with the results of the sql statement.

DataTableCollection dataTblCollection = dataSetAuth.Tables; //Grab the collection of tables.

foreach(DataRow oDr in dataTblCollection[0].Rows) {

Console.WriteLine(oDr[&#34;Product_Name&#34;].ToString());

}

dataTblCollection = null;

sqlAdapter = null;

sqlConn = null;

dataSetAuth = null;



So here is my dilemma, remember when I talked about employees belong to many companies? and I mentioned that companies have certain products defined to them available to be sold? So I have all of the work pretty much done, with 2 or 3 work arounds if I can&#39;t get what I am achieving to work. What I would like to do is call a stored proc to get all of the products available via the company Binary(8) primary keys that they belong to. But the problem is one employee could belong to 2 companies so the call would look something like this in code:


***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

where another employee could have 4 companies that they belong to:

***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB,0x0000000000 0000FC,0x00000000000000FD&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

But as you can see the problem here is that I want to use the same stored proc and pass it as many params as I need without the INPUT of it being &#34;hard-coded&#34; within the proc itself. I would like the proc to &#34;figure out&#34; how many params were passed to it &#34;dynamically&#34; and then conduct business as usual. I can&#39;t do any string concatenation and then splitting of them due to the data type being Binary(8). I&#39;ve tried to send just a simple hex string of &#34;0x000000000000000A&#34; and converting it to Binary(8) and then doing a select on that variable but the convert doesn&#39;t convert the hex string properly.

I hope that I explained it well enough....

Any suggestions are greatly appreciated and again thanks for your help!

-- Al



------------
Todd at 7/9/01 8:23:22 AM

In what way does this sort of thing not meet your needs? You simply just have to declare enough params.

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @n=NULL


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Todd]

I read this. My question remains the same.

In what way does the following not meet your needs

Create proc MyProc
@Var1 binary (8) = null,
@Var2 binary (8) = null,
@Var3 binary (8) = null
...
@VarN Binary (8) = null
as

Select *
from YourProducts_table
Where YourColumn in (@Var1,@Var2,Var3,...@VarN)





------------
Albert Davis at 7/9/01 10:22:09 AM

Thanks for the reply,

Ok let me explain what I am trying to accomplish without &#34;hard-coding&#34; input params in the stored proc. I have a CRM system that uses data type Binary(8) when a new record is inserted for its internal primary key on that record. This 64-bit primary key is used for programmatic tracking purposes. We use ASP.NET as the vehicle that drives the middle-tier integration piece. The program language of choice for this project is C# using the .NET Framework SDK. I am using the System.Data.SqlClient adaptors to natively communicate to SQL Server 7.0 thus providing excellent speed and cached access. There are sales agents that&#39;s belong to several companies which have several products that can be sold per company. So we have a one-to-many for employees-to-companies, and a one-to-many for companies-to-products. I have a stored proc that is used to gather all of the pertinent information about products that are available to companies based upon the companies Binary(8) primary key. I have built numerous utility classes that convert this Binary(8) into a nice hex string for query string purposes over the web in the format of &#34;0x0000000000000000&#34; when retrieving records via a DataSet. I don&#39;t need conversion classes for converting the hex string back to binary when trying to retrieve records based upon this hex string due to the fact that the drivers automatically convert this hex string into a Binary(8) data type for me (kind of like how if you select a Binary data type in SQL Analyzer, the data grid doesn&#39;t show you <binary> [like in Enterprise Manager] but instead it nicely converts the Binary(8) format into a hex string representation. Something like this:

[C#]

string sConn= &#34;server=yourserver;uid=sa;pwd=;database=db_pro ducts&#34;;

string sSql= &#34;SELECT * FROM PRODUCTS WHERE PRODUCT_ID = 0x0000000000000FAC&#34;;

DataSet dataSetAuth = new DataSet(); //Create a data set.

SqlConnection sqlConn = new SqlConnection(sConn); //Create a data connection.

SqlDataAdapter sqlAdapter = new SqlDataAdapter(); //Associate the connection with Sql Server.

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

sqlAdapter.Fill(dataSetAuth); //Fill the data set with the results of the sql statement.

DataTableCollection dataTblCollection = dataSetAuth.Tables; //Grab the collection of tables.

foreach(DataRow oDr in dataTblCollection[0].Rows) {

Console.WriteLine(oDr[&#34;Product_Name&#34;].ToString());

}

dataTblCollection = null;

sqlAdapter = null;

sqlConn = null;

dataSetAuth = null;



So here is my dilemma, remember when I talked about employees belong to many companies? and I mentioned that companies have certain products defined to them available to be sold? So I have all of the work pretty much done, with 2 or 3 work arounds if I can&#39;t get what I am achieving to work. What I would like to do is call a stored proc to get all of the products available via the company Binary(8) primary keys that they belong to. But the problem is one employee could belong to 2 companies so the call would look something like this in code:


***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

where another employee could have 4 companies that they belong to:

***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB,0x0000000000 0000FC,0x00000000000000FD&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

But as you can see the problem here is that I want to use the same stored proc and pass it as many params as I need without the INPUT of it being &#34;hard-coded&#34; within the proc itself. I would like the proc to &#34;figure out&#34; how many params were passed to it &#34;dynamically&#34; and then conduct business as usual. I can&#39;t do any string concatenation and then splitting of them due to the data type being Binary(8). I&#39;ve tried to send just a simple hex string of &#34;0x000000000000000A&#34; and converting it to Binary(8) and then doing a select on that variable but the convert doesn&#39;t convert the hex string properly.

I hope that I explained it well enough....

Any suggestions are greatly appreciated and again thanks for your help!

-- Al



------------
Todd at 7/9/01 8:23:22 AM

In what way does this sort of thing not meet your needs? You simply just have to declare enough params.

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @n=NULL


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Anu]

Here you go .................

declare @string varchar(1024)
set @string=&#39;&#39;a&#39;,&#39;b&#39;,&#39;c&#39;,& #39;d&#39;&#39; /* which you have give the string in the ASP*/

/* you have a table called tab1 with col1,col2,col3 */
if exists(select * from sysobjects where name=&#39;tab1&#39
begin
drop table tab2
create table tab2(col1 varchar(10),col2 varchar(10),col3 varchar(10))
end
else
begin
create table tab2(col1 varchar(10),col2 varchar(10),col4 varchar(10))
end

/* you have the following values for eg.,*/
/*
insert into tab2 select&#39;a&#39;,&#39;1&#39;,&#39;1&#39;
insert into tab2 select &#39;a&#39;,&#39;1&#39;,&#39;1&#39;
insert into tab2 select &#39;b&#39;,&#39;1&#39;,&#39;1&#39;
insert into tab2 select &#39;c&#39;,&#39;1&#39;,&#39;1&#39;
*/
/* Here starts the script for creating dynamic stored procedure with dynamic parameters*/

declare @cnt int
declare @comma int
declare @param varchar(2048)
declare @datatype varchar(50)
declare @proc varchar(2048)
declare @count varchar(3)
declare @dyn_cond varchar(2048) /* If you use the same condition for dynamic parameters*/
set @cnt=1
set @comma=0
set @datatype=&#39; varchar(10)= null,&#39; /* If you use the same datatype for all parameters*/
set @param=&#39;&#39;
set @dyn_cond=&#39;&#39;
select len(@string)
while(@cnt<len(@string))
begin
if(substring(@string,@cnt,1)=&#39;,&#39
begin
set @comma=@comma+1
set @count=convert(varchar,(@comma))
set @param= @param+&#39; @param&#39; +@count+@datatype
set @dyn_cond =@dyn_cond +&#39; col1=&#39;+&#39;@param&#39;+@count+&#39; or&#39;
select @dyn_cond
end
set @cnt=@cnt+1
end
set @count=convert(varchar,(@comma+1))
set @param= @param+&#39; @param&#39; +@count+@datatype
select @param
set @dyn_cond =@dyn_cond +&#39; col1=&#39;+&#39;@param&#39;+@count
select @dyn_cond


if exists(select * from sysobjects where name=&#39;myproc&#39
begin
drop procedure myproc
select @proc= &#39;create procedure myproc &#39;+ substring(@param,1,len(@param)-1)+ &#39; as &#39;+ &#39;select * from tab2 where &#39;+ @dyn_cond
select @proc
exec(@proc)
end
else
begin
select @proc= &#39;create procedure myproc &#39;+ substring(@param,1,len(@param)-1)+ &#39; as &#39;+ &#39;select * from tab2 where &#39;+@dyn_cond
select @proc
exec(@proc)
end

/* the dynamically created stored procedure will be as below*/

sp_helptext myproc

/* now the stored procedure is ready to execute with the @string*/
if exists(select * from sysobjects where name=&#39;myproc&#39
begin
declare @stmt1 varchar(2048)
set @stmt1=&#39; exec myproc &#39;+@string
select(@stmt1)
end


/************************************************** **********************/

-Anu



------------
Albert Davis at 7/9/01 10:22:09 AM

Thanks for the reply,

Ok let me explain what I am trying to accomplish without &#34;hard-coding&#34; input params in the stored proc. I have a CRM system that uses data type Binary(8) when a new record is inserted for its internal primary key on that record. This 64-bit primary key is used for programmatic tracking purposes. We use ASP.NET as the vehicle that drives the middle-tier integration piece. The program language of choice for this project is C# using the .NET Framework SDK. I am using the System.Data.SqlClient adaptors to natively communicate to SQL Server 7.0 thus providing excellent speed and cached access. There are sales agents that&#39;s belong to several companies which have several products that can be sold per company. So we have a one-to-many for employees-to-companies, and a one-to-many for companies-to-products. I have a stored proc that is used to gather all of the pertinent information about products that are available to companies based upon the companies Binary(8) primary key. I have built numerous utility classes that convert this Binary(8) into a nice hex string for query string purposes over the web in the format of &#34;0x0000000000000000&#34; when retrieving records via a DataSet. I don&#39;t need conversion classes for converting the hex string back to binary when trying to retrieve records based upon this hex string due to the fact that the drivers automatically convert this hex string into a Binary(8) data type for me (kind of like how if you select a Binary data type in SQL Analyzer, the data grid doesn&#39;t show you <binary> [like in Enterprise Manager] but instead it nicely converts the Binary(8) format into a hex string representation. Something like this:

[C#]

string sConn= &#34;server=yourserver;uid=sa;pwd=;database=db_pro ducts&#34;;

string sSql= &#34;SELECT * FROM PRODUCTS WHERE PRODUCT_ID = 0x0000000000000FAC&#34;;

DataSet dataSetAuth = new DataSet(); //Create a data set.

SqlConnection sqlConn = new SqlConnection(sConn); //Create a data connection.

SqlDataAdapter sqlAdapter = new SqlDataAdapter(); //Associate the connection with Sql Server.

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

sqlAdapter.Fill(dataSetAuth); //Fill the data set with the results of the sql statement.

DataTableCollection dataTblCollection = dataSetAuth.Tables; //Grab the collection of tables.

foreach(DataRow oDr in dataTblCollection[0].Rows) {

Console.WriteLine(oDr[&#34;Product_Name&#34;].ToString());

}

dataTblCollection = null;

sqlAdapter = null;

sqlConn = null;

dataSetAuth = null;



So here is my dilemma, remember when I talked about employees belong to many companies? and I mentioned that companies have certain products defined to them available to be sold? So I have all of the work pretty much done, with 2 or 3 work arounds if I can&#39;t get what I am achieving to work. What I would like to do is call a stored proc to get all of the products available via the company Binary(8) primary keys that they belong to. But the problem is one employee could belong to 2 companies so the call would look something like this in code:


***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

where another employee could have 4 companies that they belong to:

***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB,0x0000000000 0000FC,0x00000000000000FD&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

But as you can see the problem here is that I want to use the same stored proc and pass it as many params as I need without the INPUT of it being &#34;hard-coded&#34; within the proc itself. I would like the proc to &#34;figure out&#34; how many params were passed to it &#34;dynamically&#34; and then conduct business as usual. I can&#39;t do any string concatenation and then splitting of them due to the data type being Binary(8). I&#39;ve tried to send just a simple hex string of &#34;0x000000000000000A&#34; and converting it to Binary(8) and then doing a select on that variable but the convert doesn&#39;t convert the hex string properly.

I hope that I explained it well enough....

Any suggestions are greatly appreciated and again thanks for your help!

-- Al



------------
Todd at 7/9/01 8:23:22 AM

In what way does this sort of thing not meet your needs? You simply just have to declare enough params.

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @n=NULL


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

[Albert Davis]

Todd,

This could support my need and I could supply 200 params as input values and set them to null but back to my original quest, I would like the sp to find out dynamically and not have the input params hard coded within the sp and set to null, thus allowing me only pass what I need to pass and run a cursor until I find a null value.... I want to avoid setting up generic input params that contain a null value... I would like the sp to figure it out... I think that I have came to a conclusion that normal user sp&#39;s are not able to support this type of logic...

Thanx,
-- Al


------------
Todd at 7/9/01 1:12:32 PM

I read this. My question remains the same.

In what way does the following not meet your needs

Create proc MyProc
@Var1 binary (8) = null,
@Var2 binary (8) = null,
@Var3 binary (8) = null
...
@VarN Binary (8) = null
as

Select *
from YourProducts_table
Where YourColumn in (@Var1,@Var2,Var3,...@VarN)





------------
Albert Davis at 7/9/01 10:22:09 AM

Thanks for the reply,

Ok let me explain what I am trying to accomplish without &#34;hard-coding&#34; input params in the stored proc. I have a CRM system that uses data type Binary(8) when a new record is inserted for its internal primary key on that record. This 64-bit primary key is used for programmatic tracking purposes. We use ASP.NET as the vehicle that drives the middle-tier integration piece. The program language of choice for this project is C# using the .NET Framework SDK. I am using the System.Data.SqlClient adaptors to natively communicate to SQL Server 7.0 thus providing excellent speed and cached access. There are sales agents that&#39;s belong to several companies which have several products that can be sold per company. So we have a one-to-many for employees-to-companies, and a one-to-many for companies-to-products. I have a stored proc that is used to gather all of the pertinent information about products that are available to companies based upon the companies Binary(8) primary key. I have built numerous utility classes that convert this Binary(8) into a nice hex string for query string purposes over the web in the format of &#34;0x0000000000000000&#34; when retrieving records via a DataSet. I don&#39;t need conversion classes for converting the hex string back to binary when trying to retrieve records based upon this hex string due to the fact that the drivers automatically convert this hex string into a Binary(8) data type for me (kind of like how if you select a Binary data type in SQL Analyzer, the data grid doesn&#39;t show you <binary> [like in Enterprise Manager] but instead it nicely converts the Binary(8) format into a hex string representation. Something like this:

[C#]

string sConn= &#34;server=yourserver;uid=sa;pwd=;database=db_pro ducts&#34;;

string sSql= &#34;SELECT * FROM PRODUCTS WHERE PRODUCT_ID = 0x0000000000000FAC&#34;;

DataSet dataSetAuth = new DataSet(); //Create a data set.

SqlConnection sqlConn = new SqlConnection(sConn); //Create a data connection.

SqlDataAdapter sqlAdapter = new SqlDataAdapter(); //Associate the connection with Sql Server.

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

sqlAdapter.Fill(dataSetAuth); //Fill the data set with the results of the sql statement.

DataTableCollection dataTblCollection = dataSetAuth.Tables; //Grab the collection of tables.

foreach(DataRow oDr in dataTblCollection[0].Rows) {

Console.WriteLine(oDr[&#34;Product_Name&#34;].ToString());

}

dataTblCollection = null;

sqlAdapter = null;

sqlConn = null;

dataSetAuth = null;



So here is my dilemma, remember when I talked about employees belong to many companies? and I mentioned that companies have certain products defined to them available to be sold? So I have all of the work pretty much done, with 2 or 3 work arounds if I can&#39;t get what I am achieving to work. What I would like to do is call a stored proc to get all of the products available via the company Binary(8) primary keys that they belong to. But the problem is one employee could belong to 2 companies so the call would look something like this in code:


***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

where another employee could have 4 companies that they belong to:

***

string sSql= &#34;EXEC sp_products 0x00000000000000FA,0x00000000000000FB,0x0000000000 0000FC,0x00000000000000FD&#34;;

***

sqlAdapter.SelectCommand = new SqlCommand(sSql, sqlConn); //Create a SQL Command.

But as you can see the problem here is that I want to use the same stored proc and pass it as many params as I need without the INPUT of it being &#34;hard-coded&#34; within the proc itself. I would like the proc to &#34;figure out&#34; how many params were passed to it &#34;dynamically&#34; and then conduct business as usual. I can&#39;t do any string concatenation and then splitting of them due to the data type being Binary(8). I&#39;ve tried to send just a simple hex string of &#34;0x000000000000000A&#34; and converting it to Binary(8) and then doing a select on that variable but the convert doesn&#39;t convert the hex string properly.

I hope that I explained it well enough....

Any suggestions are greatly appreciated and again thanks for your help!

-- Al



------------
Todd at 7/9/01 8:23:22 AM

In what way does this sort of thing not meet your needs? You simply just have to declare enough params.

create proc sp_mine @1 varchar(10) = NULL, @2=NULL, @3=NULL, @n=NULL


------------
Al Davis at 7/6/01 1:26:33 PM


Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;

2) calling the stored proc with 7 strings:
exec sp_myproc &#34;a&#34;,&#34;b&#34;,&#34;c&#34;,&#34;d&#34;,&# 34;e&#34;,&#34;f&#34;,&#34;g&#34;

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al