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)

conversion *= to left outer joins not yielding desired results

Hi

I have a query which works fine on sql 2000 with *= but doesn't yield the same when converted to 2005 using joins

Here is the original query

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c, product_family pf,
ship_to_country sc, dealcountry_tatsla dcts, tatsla_countrydefaults tscd, #TAT_EXPORT_DEALVALUES tmpex
WHERE
c.id_country = sc.id_country
AND sc.id_deal = tmpex.id_deal

AND sc.id_country *= dcts.id_country

AND sc.id_deal *= dcts.id_deal
AND pf.id_product_family *= dcts.id_product_family
AND sc.id_country = tscd.id_country
AND pf.id_product_family = tscd.id_product_family

The Converted one is below

select tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
from
ship_to_country sc INNER JOIN country c
ON sc.id_country = c.id_country
INNER JOIN #TAT_EXPORT_DEALVALUES tmpex
ON (sc.id_deal = tmpex.id_deal )
INNER JOIN tatsla_countrydefaults tscd1
ON (sc.id_country = tscd1.id_country)
LEFT OUTER JOIN
dealcountry_tatsla dcts
ON (sc.id_country = dcts.id_country and sc.id_deal=dcts.id_deal )
LEFT OUTER JOIN product_family pf
ON (pf.id_product_family = dcts.id_product_family)
INNER JOIN tatsla_countrydefaults tscd
ON (pf.id_product_family = tscd.id_product_family)

Please let me know whether I am missing something

The join query results in cartesian product

If I remember old style syntax correctly, the new version will looks like this:

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c
inner join ship_to_country sc on c.id_country = sc.id_country
inner join #TAT_EXPORT_DEALVALUES tmpex on sc.id_deal = tmpex.id_deal
inner join tatsla_countrydefaults tscd on sc.id_country = tscd.id_country
inner join product_family pf on pf.id_product_family = tscd.id_product_family
left join dealcountry_tatsla dcts on sc.id_country = dcts.id_country
and sc.id_deal = dcts.id_deal and pf.id_product_family = dcts.id_product_family

Not sure it is equivalent, though, so thorough testing is highly recommended.|||Your Query seems to be correct. Have you checked the result set. Is there any flaw in that.|||No, I've not checked this - I have no underlying data to test on. If I had, I'd have no doubt

But since you have both the tables and the data, you always may test its correctness - either on MSSQL 2000 or, if all that you have is MSSQL 2005, on a separate database with compatibility level set to 80. In the latter case, both queries will work even on 2005.sqlsql