CONversion of an input parameter for a SP in the desired form.

hi All ,

I am getting one param for a SP as list of states from the Front End as :

@.states = 'NY,NJ,CA,Fl,MA' . Now i have to convert this param in the form :

@.states_for_SP = 'NY','NJ','CA','Fl','MA' . Is there any efficient way to do it except using REPLACE function. As this portion in our SP is taking a lot of time in converting in the desired form.

Plz suggest to do this.

Thanks.

I don't understand why a simple replace, e.g.

Code Snippet

declare @.a varchar(100)
set @.a = '''NY,NJ,CA,Fl,MA'''

declare @.b varchar(100)
set @.b = replace(@.a, ',', ''',''')

select @.a, @.b

Gives 'NY,NJ,CA,Fl,MA' > 'NY','NJ','CA','Fl','MA'

Should be slow. Is this similar to what you're trying to do?

Greg.

|||

Mohit,

This is a slightly different approach.

Using the function below you can convert the list into a table and then join the table into your query.

Code Snippet

IFEXISTS(

SELECT*FROMsys.objects

WHEREobject_id=OBJECT_ID(N'[dbo].[list2set]')

ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')

)

DROPFUNCTION [dbo].[list2set];

GO

CREATEFUNCTION dbo.list2set( @.list nvarchar(max), @.delim nvarchar(10))

RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))

AS

BEGIN

IFlen(@.list)<1 RETURN;

DECLARE @.xList XML;

-- no validity tests are performed, depending on input this could fail

SET @.xList =Convert(XML,''+REPLACE(@.list, @.delim,'')+'')

INSERTINTO @.resultset

SELECT data.listitem.value('.','nvarchar(max)')as item

FROM @.xList.nodes('/list/item') data(listitem)

RETURN

END

GO

You'd then use it as such:

Code Snippet

SELECT adr.state

FROM Address adr

innerjoin dbo.list2set(@.states, N',') sel

on adr.state = sel.item

|||

here the code..

Code Snippet

Create Table #Numbers(

Number Int

);

Declare @.I as int;

Set @.I = 1

While @.I<100

Begin

Insert Into #Numbers values(@.I);

Set @.I = @.I + 1;

End

Declare @.states varchar(100)

Set @.states = 'NY,NJ,CA,Fl,MA'

Declare @.StatesTable Table

(

State Varchar(100)

)

Insert Into @.StatesTable

Select Substring(',' + @.states + ',', Number, CharIndex(',',',' + @.states + ',',Number) - Number)

From

#Numbers

Where

Number<=Len(',' + @.states + ',')

And Substring(',' + @.states + ',',Number-1 ,1) = ','

--As you wise for concatination..

Set @.states = ''

Select @.states = @.states + ',''' + State + '''' From @.StatesTable

Select Substring(@.states,2,8000)

--Now You can use this @.StatesTable on any query for IN operator..

--Select * From SomeTable Where States in (Select State From @.StatesTable)