Passing multiple selections to a stored proc parameter

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@.OrderList varchar(500)
)
RETURNS
@.ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @.OrderID varchar(10), @.Pos int

SET @.OrderList = LTRIM(RTRIM(@.OrderList))+ ','
SET @.Pos = CHARINDEX(',', @.OrderList, 1)

IF REPLACE(@.OrderList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.OrderID = LTRIM(RTRIM(LEFT(@.OrderList, @.Pos - 1)))
IF @.OrderID <> ''
BEGIN
INSERT INTO @.ParsedList (OrderID)
VALUES (CAST(@.OrderID AS int)) --Use Appropriate conversion
END
SET @.OrderList = RIGHT(@.OrderList, LEN(@.OrderList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.OrderList, 1)

END
END
RETURN
END
GO

I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_criI see you parsing the list, but I don't see where you select any records based upon it. So where would your ALL logic be incorporated?sql

Passing a schema to a cube via AMO

I'm having a problem passing the Schema name to the cube when building it using AMO.
Oh, i'm using SQL Server 2k5.

When i build the cube through Dev Studio, it will process dimensions as expected. So, when pulling data, i can look at the SQL and see (for example):

"Select blah from Schema.TableName"

Which is what i want. However, when i build the cube using AMO i can not process the dimension, and it's failing because the SQL reads:

"Select blah from TableName"

A quick fix for me is to remove the Schema from my tables.. so, dbo.TableName works fine. But that's not exactly what i want. Or, i can create stored procs that pull the data from any table/schema, but that's not what i want either..

So, i guess i'm asking if there's a way to pass a schema to a dataset and have it read by the cube.

Any help would be appreciated..
Thanks,

Chris.

The following snippet is from the AMOAdventureWorks sample that is part of the SQL Server 2005 samples. It comes from the part of the program where they are building the DSV. Note that there are two points where the dbo schema is hard coded. I am hoping that by passing in your own schema name will resolve your issue.

staticvoid AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)

{

OleDbDataAdapter adapter = newOleDbDataAdapter(

"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",

connection);

DataTable[] dataTables = adapter.FillSchema(dsv.Schema,

SchemaType.Mapped, tableName);

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add("TableType", "Table");

dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");

dataTable.ExtendedProperties.Add("DbTableName", tableName);

dataTable.ExtendedProperties.Add("FriendlyName", tableName);

}

|||excellent, thanks so much!

dataTable.ExtendedProperties.Add("DbSchemaName", "dbo"); worked exactly as i needed it to, by replacing it with my own schema name.