|
|
Posted by Uri Dimant on 06/11/06 04:48
Hi
Another way is to write an UDF that does split on your parameters
Take a look at Erland's example
CREATE PROCEDURE get_company_names_inline
@customers nvarchar(2000)
AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@customers) s ON C.CustomerID = s.Value
go
---Usage
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
---The function does split
CREATE FUNCTION inline_split_me (@param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @param + ',', Number + 1,
charindex(',', ',' + @param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @param + ',') - 1
AND substring(',' + @param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"news.sbcglobal.net" <groomeryNOSPAM@MAPSONsbcglobal.net> wrote in message
news:IvJig.48794$Lm5.24532@newssvr12.news.prodigy.com...
> Here's my problem:
>
> I'm developing an ASP.NET 2.0 application that has a user select one or
> more
> auto manufacturers from a listbox ("lstMakes"). Once they do this,
> another
> listbox ("lstModels") should be filled with all matching models made by
> the
> selected manufacturers. If lstMakes was not multi-select, I'd have no
> problem. But in this case it has to be multi-select. The database is SQL
> Server 2005 which does not accept arrays as parameters. I've been told
> that
> I have to create an XML document that will act as a filtered Manufacturers
> table that I can join to my Models table in my stored procedure. Problem
> is
> I don't have the foggiest idea how to do this. I've seen some examples
> that
> just leave me scratching my head so I was hoping someone could look at
> what
> I'm trying to do and show me how to do this. Thanks!
>
>
>
Navigation:
[Reply to this message]
|