Passing multiple selections to a stored proc param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing multiple selections to a stored proc param

Author

Topic

yoggi123
Starting Member

29 Posts

Posted - 2005-01-11 : 13:14:04

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/jobsh...work.search_cri

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-11 : 13:47:21

Instead of using an actual list, why not use a bitmap scheme?

E.g. if you have 20 columns, you could have the app pass in a 20-character "bitmap":

NNNNNNNNNNNNNNNNNNNN --> This means select no columns
NNYNNYNNNNNNNNNNNNNN --> This means select columns 3 and 6

...

It's kind of a hack... Well, more than kind of :) -- but it keeps the app from having to know too much about the database implementation.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-11 : 14:42:24

We have this same type of logic in our system. We implemented it using an IF-ELSE construct.

Ex.

if @Param = 'All'
select blah
from MyTable
else
begin
-- Massage input

<Your logic here>

-- Use Massaged data

select blah
from MyTable
where <Your logic here>
end

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

yoggi123
Starting Member

29 Posts

Posted - 2005-01-11 : 14:59:04

Hi,

Thanks for the sugestions.

Would the same be true if multiple fields are being passed to the stored procedure all the same time, and some may have 'All' selected, others may have only one selected and others still may have more then one selected?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources