SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Execute multiple parameters in Store Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 02/05/2013 :  08:50:42  Show Profile  Reply with Quote
I have created this store procedure through SQL and it is working great. The only thing is, I am having problems figuring out how to enter in multple TIN parameters. I want to be ablet to enter in 2 tins and have it execute those two tins. How can I add multiple values in the Tin parameter.
Here is my procedure below....

ALTER Procedure [dbo].[P_Providers_TAX]

@TIN varchar (40)

AS
BEGIN

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[Providers_TAX]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[Providers_TAX]

--Select Distinct* from Providers_TAX
CREATE TABLE Providers_TAX
(
---Version uniqueidentifier NOT NULL,
[PRO ID] varchar (40) NULL,
[PCS Number] varchar (40) NULL,
[Update Date] varchar(40) NULL,
[Effective Date] varchar(40) NULL,
[Termination Date] varchar(40) NULL,
[AHS] varchar(40) NULL,
[DoctorID] varchar(40) NULL,
[UPIN] varchar(40) NULL,
Product varchar(5) NULL,
[PROVIDERNAME] varchar(60) NULL,
[Last Name] varchar(40) NULL,
[First Name] varchar(40) NULL,
[Middle Name] varchar(40) NULL,
[Ext] varchar(40) NULL,
[Degree] varchar(40) NULL,
[Social Security] varchar(40) NULL,
[Mental Health] varchar(40) NULL,
[Practice] varchar(60) NULL,
[Address 1] varchar(55) NULL,
[Address 2] varchar(55) NULL,
[City] varchar(40) NULL,
[Zip] varchar(40) NULL,
[St] varchar(40) NULL,
[COUNTY] varchar(40) NULL,
[Phone] varchar(40) NULL,
[Fax #] varchar(40) NULL,
[Primary/Specialty] varchar(40) NULL,
[DOB] varchar(40) NULL,
[NPI] varchar(10) NULL,
[Spec 1] varchar(40) NULL,
[Spec 2] varchar(40) NULL,
[Spec 3] varchar(40) NULL,
[Tax ID] varchar(40) NULL,
[email] varchar(255) NULL,
[URL] varchar(255) NULL,
[pro_sex] varchar(1) NULL,
[pro_eff] varchar(20) NULL,
[pro_trm] varchar(20) NULL,
[pro_xtyp] varchar(4) NULL,
[pro_bref] varchar(8) NULL,
[pro_btag] varchar(1) NULL,
[pro_pcs] varchar(8) NULL,
[pro_phone] varchar(24) NULL,
prop_pcp varchar(1) NULL,
prop_net varchar(5) NULL,
[Fee Schedule] varchar(40) NULL,
[Fee Type]varchar(40) NULL,
prop_prc varchar(6) NULL,
prop_uac varchar(5) NULL,
prop_cont varchar(8) NULL,
prop_disc decimal(5,2) NULL,
prop_disr decimal(5,2) NULL,
prop_eff varchar(20) NULL,
prop_trm varchar(20) NULL,
prop_chgdt varchar(20) NULL,
prop_id1 varchar(8) NULL,
prop_id2 varchar(4) NULL,
[PROVIDERTYPECODE] varchar(40) NULL,
[IPA_PHO] varchar(40) NULL,
[Billing Name] varchar(60) NULL,
[Billing Address] varchar(55) NULL,
[Billing City] varchar(40) NULL,
[Billing Zip] varchar(40) NULL,
[Billing St] varchar(40) NULL,
[Billing Phone] varchar(40) NULL

)



------------------------------------------------------------------------------------------------------------
INSERT INTO Providers_TAX(
[PRO ID],
[PCS Number],[Update Date], [Effective Date],[Termination Date],[AHS],[DoctorID], [UPIN],[Product],PROVIDERNAME,[Last Name],
[First Name],[Middle Name], [Ext],[Degree],[Social Security], [Practice], [Address 1], [Address 2],[City],
[St], [Zip],[COUNTY],[Phone],[Fax #], [Primary/Specialty], [DOB], [NPI],[Spec 1],[Spec 2], [Spec 3],[Tax ID],
[email], [URL], pro_sex,pro_eff,pro_trm,pro_xtyp,prop_id1,prop_id2, pro_bref,pro_btag, pro_pcs,pro_phone,prop_net,prop_pcp,
prop_prc,prop_uac,prop_cont,prop_disc, prop_disr, prop_eff,prop_trm,prop_chgdt, [PROVIDERTYPECODE]

)

SELECT Distinct

pro.pro_id1 as [PRO ID],
pro.PRO_PCS as [PCS Number],
[Update Date]=
Case CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
End,
[Effective Date] =
Case CONVERT(VARCHAR(10), (prop.prop_eff), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_eff), 101)
End,
[Termination Date] =
Case when prop.prop_trm
in (NULL,'1/1/1900','01/01/1900') then ''
else
CONVERT(VARCHAR(10), (prop.prop_trm), 101)
End,
[AHS] = CASE WHEN prop.prop_trm is NULL THEN 'X'
WHEN prop.prop_trm = NULL THEN 'X'
WHEN prop.prop_trm = '01/01/1900' THEN 'X'
WHEN prop.prop_trm = '' THEN 'X'
WHEN GetDate() BETWEEN prop.prop_eff AND prop.prop_trm THEN 'X'

ELSE 'T' ---NULL--''
END,


[DoctorID] =
CASE
WHEN prop.prop_cont is NULL THEN RIGHT(prop.prop_id1,6) + RIGHT(prop.prop_id2,2)
ELSE prop.prop_cont
END,

pro.pro_upin AS [UPIN],

prop.prop_prd as Product,

[PROVIDERNAME] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN pro.pro_office
WHEN 'HOSP' THEN pro.pro_office
ELSE pro.pro_lname
END,


[Last Name] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN pro.pro_office
WHEN 'HOSP' THEN pro.pro_office
ELSE (left(pro.pro_lname,
case when charindex(',', pro.pro_lname)>0

then charindex(',',pro.pro_lname)-1
else LEN(pro.pro_lname) end))--as LASTNAME,
end,
pro.pro_fname AS [First Name],
pro.pro_minit AS [Middle Name],
CASE WHEN charindex(',',pro.pro_lname)>0
THEN ltrim(substring(pro.pro_lname,charindex(',',pro.pro_lname) + 1,LEN(pro.pro_lname))) ELSE NULL END AS EXT,

pro.pro_degree AS [Degree],
pro.pro_ssn AS [Social Security],
pro.pro_office AS [Practice],
pro.pro_addr1 AS [Address 1],
pro.pro_addr2 AS [Address 2],
pro.pro_city AS [City],
pro.pro_state AS [St],
replace(pro.pro_zip,'-','') AS [Zip],
replace(pro.pro_county,' COUNTY','') + ' COUNTY' AS 'County', --impact.dbo.pro.pro_county AS [COUNTY],
pro.pro_phone AS [Phone],
pro.pro_fax AS [Fax #],

[Primary/Specialty] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN 'A'
WHEN 'BILL' THEN 'X'
WHEN 'EOTF' THEN 'X'
WHEN 'HOSP' THEN 'H'
WHEN 'PRIM' THEN 'P' --'P' KateZ: The only thing that should have a "P" is Family Practice, General Practice, Internal Medicine, & Pediatrics.
WHEN 'PROV' THEN 'S' --'P'
WHEN 'SPEC' THEN 'S'
ELSE 'X'
END,
DOB =
Case CONVERT(VARCHAR(10), (pro.pro_dob), 101)
WHEN NULL THEN ''
WHEN NULL THEN ''
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (pro.pro_dob), 101)
End,

pro.pro_npi AS NPI,
pro.pro_spec1 AS [Spec 1],
pro.pro_spec2 AS [Spec 2],
pro.pro_spec3 AS [Spec 3],
pro.pro_tax1 AS [Tax ID],
pro.pro_email AS [email],
pro.pro_www AS [URL],
pro.pro_sex,

[pro_eff] =
Case CONVERT(VARCHAR(10), (pro.pro_eff), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (pro.pro_eff), 101)
End,
[pro_trm] =
Case CONVERT(VARCHAR(10), (pro.pro_trm), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (pro.pro_trm), 101) end,
pro_xtyp,
prop_id1,
prop_id2,
pro_bref,
pro.pro_btag,
pro.pro_pcs,
pro.pro_phone,

prop.prop_net,

prop.prop_pcp,
prop.prop_prc,
prop.prop_uac,
prop.prop_cont,
prop.prop_disc,
prop_disr ,
[prop_eff] =
Case CONVERT(VARCHAR(10), (prop.prop_eff), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_eff), 101)
End,

[prop_trm] =
Case CONVERT(VARCHAR(10), (prop.prop_trm), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_trm), 101)
End,
[prop_chgdt] =
Case CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
End,
[PROVIDERTYPECODE] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN 'ANC'
WHEN 'BILL' THEN 'XX1'
WHEN 'EOTF' THEN 'XX2'
WHEN 'HOSP' THEN 'HOSP'
WHEN 'PRIM' THEN 'PHYS'
WHEN 'PROV' THEN 'PHYS'
WHEN 'SPEC' THEN 'PHYS'
ELSE 'XX3'
END

FROM impact.dbo.pro pro
JOIN impact.dbo.prop prop
ON pro.pro_id1 = prop.prop_id1 AND prop.prop_prd = 'DGH'---Not IN('PPO','PTN','PRI')
LEFT JOIN impact.dbo.pcsa pcsa
ON pro.pro_id1 = pcsa.pcsa_id1 and pcsa.pcsa_Stat NOT IN ('NOGOOD')
Left join impact.dbo.pcsl pcsl
on pcsl.PCSL_id1 = pcsa.pcsa_id1

WHERE
pro.pro_xtyp NOT IN ('BILL', 'EOTF') and
(LEFT(prop.prop_cont,2) <> 'NP' or prop.prop_cont is null) and
(prop.prop_trm is NULL or prop.prop_trm = NULL or prop.prop_trm = '01/01/1900') and
(pro_tax1 IN (@TIN))
order by pro_pcs
End

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/05/2013 :  09:07:18  Show Profile  Reply with Quote
The quick way is to send the multiple values comma-separated in the @TIN parameter and change the WHERE clause to this:

--- (pro_tax1 IN (@TIN)) 
','+@TIN+',' LIKE '%,'+	pro_tax1 + ',%'
That may have some performance implications. So the recommended strategy would be to split the comma-separated string into individual tokens in a virtual (or temp) table and join to that table. There are string splitters available on line - for example here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 02/05/2013 :  09:19:04  Show Profile  Reply with Quote
This worked Perfectly James, thanks! I will also take a look at the other site you sent me too. Thanks again! :)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/05/2013 :  09:59:22  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 02/05/2013 :  11:46:58  Show Profile  Reply with Quote
Or, you could pass a table valued param:
http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 03/05/2013 :  10:58:43  Show Profile  Reply with Quote
This ','+@TIN+',' LIKE '%,'+ pro_tax1 + ',%'
is working great, but when I add more than 2 tax ids it doesn't pull them. I am not sure why or what to do.


quote:
Originally posted by James K

The quick way is to send the multiple values comma-separated in the @TIN parameter and change the WHERE clause to this:

--- (pro_tax1 IN (@TIN)) 
','+@TIN+',' LIKE '%,'+	pro_tax1 + ',%'
That may have some performance implications. So the recommended strategy would be to split the comma-separated string into individual tokens in a virtual (or temp) table and join to that table. There are string splitters available on line - for example here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/05/2013 :  11:12:16  Show Profile  Reply with Quote
It should work for any number of parameters. Compare with the test code below to see what you might be doing differently. You can copy and paste it to an SSMS window to run it.
CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd,xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;
Go to Top of Page

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 03/05/2013 :  12:17:27  Show Profile  Reply with Quote
Oh okay I didn't create a #temp table. Looks like I am still getting an error though.

I can create the temp table, but I dont know how to implement it into my procedure. Here is a little glimps.



CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp (pro_tax1)

Select Distinct
pro_tax1
from impact.dbo.pro

DECLARE @TIN VARCHAR(32)
----DROP TABLE #tmp;

BEGIN

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[Providers_TAX_test]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[Providers_TAX_test]

--Select Distinct* from Providers_TAX
CREATE TABLE Providers_TAX_test
(
---Version uniqueidentifier NOT NULL,
[PRO ID] varchar (40) NULL,
[PCS Number] varchar (40) NULL,
[Update Date] varchar(40) NULL,
[Effective Date] varchar(40) NULL,
[Termination Date] varchar(40) NULL,
[AHS] varchar(40) NULL,
[DoctorID] varchar(40) NULL,
[UPIN] varchar(40) NULL,
Product varchar(5) NULL,
[PROVIDERNAME] varchar(60) NULL,
[Last Name] varchar(40) NULL,
[First Name] varchar(40) NULL,
[Middle Name] varchar(40) NULL,
[Ext] varchar(40) NULL,
[Degree] varchar(40) NULL,
[Social Security] varchar(40) NULL,
[Mental Health] varchar(40) NULL,
[Practice] varchar(60) NULL,
[Address 1] varchar(55) NULL,
[Address 2] varchar(55) NULL,
[City] varchar(40) NULL,
[Zip] varchar(40) NULL,
[St] varchar(40) NULL,
[COUNTY] varchar(40) NULL,
[Phone] varchar(40) NULL,
[Fax #] varchar(40) NULL,
[Primary/Specialty] varchar(40) NULL,
[DOB] varchar(40) NULL,
[NPI] varchar(10) NULL,
[Spec 1] varchar(40) NULL,
[Spec 2] varchar(40) NULL,
[Spec 3] varchar(40) NULL,
[Tax ID] varchar(40) NULL,
[email] varchar(255) NULL,
[URL] varchar(255) NULL,
[pro_sex] varchar(1) NULL,
[pro_eff] varchar(20) NULL,
[pro_trm] varchar(20) NULL,
[pro_xtyp] varchar(4) NULL,
[pro_bref] varchar(8) NULL,
[pro_btag] varchar(1) NULL,
[pro_pcs] varchar(8) NULL,
[pro_phone] varchar(24) NULL,
prop_pcp varchar(1) NULL,
prop_net varchar(5) NULL,
[Fee Schedule] varchar(40) NULL,
[Fee Type]varchar(40) NULL,
prop_prc varchar(6) NULL,
prop_uac varchar(5) NULL,
prop_cont varchar(8) NULL,
prop_disc decimal(5,2) NULL,
prop_disr decimal(5,2) NULL,
prop_eff varchar(20) NULL,
prop_trm varchar(20) NULL,
prop_chgdt varchar(20) NULL,
prop_id1 varchar(8) NULL,
prop_id2 varchar(4) NULL,
[PROVIDERTYPECODE] varchar(40) NULL,
[IPA_PHO] varchar(40) NULL,
[Billing Name] varchar(60) NULL,
[Billing Address] varchar(55) NULL,
[Billing City] varchar(40) NULL,
[Billing Zip] varchar(40) NULL,
[Billing St] varchar(40) NULL,
[Billing Phone] varchar(40) NULL

)



------------------------------------------------------------------------------------------------------------
INSERT INTO Providers_TAX_test(
[PRO ID],
[PCS Number],[Update Date], [Effective Date],[Termination Date],[AHS],[DoctorID], [UPIN],[Product],PROVIDERNAME,[Last Name],
[First Name],[Middle Name], [Ext],[Degree],[Social Security], [Practice], [Address 1], [Address 2],[City],
[St], [Zip],[COUNTY],[Phone],[Fax #], [Primary/Specialty], [DOB], [NPI],[Spec 1],[Spec 2], [Spec 3],[Tax ID],
[email], [URL], pro_sex,pro_eff,pro_trm,pro_xtyp,prop_id1,prop_id2, pro_bref,pro_btag, pro_pcs,pro_phone,prop_net,prop_pcp,
prop_prc,prop_uac,prop_cont,prop_disc, prop_disr, prop_eff,prop_trm,prop_chgdt, [PROVIDERTYPECODE]

)

SELECT Distinct

pro.pro_id1 as [PRO ID],
pro.PRO_PCS as [PCS Number],
[Update Date]=
Case CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
End,
[Effective Date] =
Case CONVERT(VARCHAR(10), (prop.prop_eff), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_eff), 101)
End,
[Termination Date] =
Case when prop.prop_trm
in (NULL,'1/1/1900','01/01/1900') then ''
else
CONVERT(VARCHAR(10), (prop.prop_trm), 101)
End,
[AHS] = CASE WHEN prop.prop_trm is NULL THEN 'X'
WHEN prop.prop_trm = NULL THEN 'X'
WHEN prop.prop_trm = '01/01/1900' THEN 'X'
WHEN prop.prop_trm = '' THEN 'X'
WHEN GetDate() BETWEEN prop.prop_eff AND prop.prop_trm THEN 'X'

ELSE 'T' ---NULL--''
END,


[DoctorID] =
CASE
WHEN prop.prop_cont is NULL THEN RIGHT(prop.prop_id1,6) + RIGHT(prop.prop_id2,2)
ELSE prop.prop_cont
END,

pro.pro_upin AS [UPIN],

prop.prop_prd as Product,

[PROVIDERNAME] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN pro.pro_office
WHEN 'HOSP' THEN pro.pro_office
ELSE pro.pro_lname
END,


[Last Name] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN pro.pro_office
WHEN 'HOSP' THEN pro.pro_office
ELSE (left(pro.pro_lname,
case when charindex(',', pro.pro_lname)>0

then charindex(',',pro.pro_lname)-1
else LEN(pro.pro_lname) end))--as LASTNAME,
end,
pro.pro_fname AS [First Name],
pro.pro_minit AS [Middle Name],
CASE WHEN charindex(',',pro.pro_lname)>0
THEN ltrim(substring(pro.pro_lname,charindex(',',pro.pro_lname) + 1,LEN(pro.pro_lname))) ELSE NULL END AS EXT,

pro.pro_degree AS [Degree],
pro.pro_ssn AS [Social Security],
pro.pro_office AS [Practice],
pro.pro_addr1 AS [Address 1],
pro.pro_addr2 AS [Address 2],
pro.pro_city AS [City],
pro.pro_state AS [St],
replace(pro.pro_zip,'-','') AS [Zip],
replace(pro.pro_county,' COUNTY','') + ' COUNTY' AS 'County', --impact.dbo.pro.pro_county AS [COUNTY],
pro.pro_phone AS [Phone],
pro.pro_fax AS [Fax #],

[Primary/Specialty] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN 'A'
WHEN 'BILL' THEN 'X'
WHEN 'EOTF' THEN 'X'
WHEN 'HOSP' THEN 'H'
WHEN 'PRIM' THEN 'P' --'P' KateZ: The only thing that should have a "P" is Family Practice, General Practice, Internal Medicine, & Pediatrics.
WHEN 'PROV' THEN 'S' --'P'
WHEN 'SPEC' THEN 'S'
ELSE 'X'
END,
DOB =
Case CONVERT(VARCHAR(10), (pro.pro_dob), 101)
WHEN NULL THEN ''
WHEN NULL THEN ''
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (pro.pro_dob), 101)
End,

pro.pro_npi AS NPI,
pro.pro_spec1 AS [Spec 1],
pro.pro_spec2 AS [Spec 2],
pro.pro_spec3 AS [Spec 3],
pro.pro_tax1 AS [Tax ID],
pro.pro_email AS [email],
pro.pro_www AS [URL],
pro.pro_sex,

[pro_eff] =
Case CONVERT(VARCHAR(10), (pro.pro_eff), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (pro.pro_eff), 101)
End,
[pro_trm] =
Case CONVERT(VARCHAR(10), (pro.pro_trm), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (pro.pro_trm), 101) end,
pro_xtyp,
prop_id1,
prop_id2,
pro_bref,
pro.pro_btag,
pro.pro_pcs,
pro.pro_phone,

prop.prop_net,

prop.prop_pcp,
prop.prop_prc,
prop.prop_uac,
prop.prop_cont,
prop.prop_disc,
prop_disr ,
[prop_eff] =
Case CONVERT(VARCHAR(10), (prop.prop_eff), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_eff), 101)
End,

[prop_trm] =
Case CONVERT(VARCHAR(10), (prop.prop_trm), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_trm), 101)
End,
[prop_chgdt] =
Case CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
WHEN NULL THEN ''
else CONVERT(VARCHAR(10), (prop.prop_chgdt), 101)
End,
[PROVIDERTYPECODE] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN 'ANC'
WHEN 'BILL' THEN 'XX1'
WHEN 'EOTF' THEN 'XX2'
WHEN 'HOSP' THEN 'HOSP'
WHEN 'PRIM' THEN 'PHYS'
WHEN 'PROV' THEN 'PHYS'
WHEN 'SPEC' THEN 'PHYS'
ELSE 'XX3'
END

FROM impact.dbo.pro pro
JOIN impact.dbo.prop prop
ON pro.pro_id1 = prop.prop_id1 AND prop.prop_prd = 'DGH'---Not IN('PPO','PTN','PRI')
LEFT JOIN impact.dbo.pcsa pcsa
ON pro.pro_id1 = pcsa.pcsa_id1 and pcsa.pcsa_Stat NOT IN ('NOGOOD')
Left join impact.dbo.pcsl pcsl
on pcsl.PCSL_id1 = pcsa.pcsa_id1

WHERE
pro.pro_xtyp NOT IN ('BILL', 'EOTF') and
(LEFT(prop.prop_cont,2) <> 'NP' or prop.prop_cont is null) and
(prop.prop_trm is NULL or prop.prop_trm = NULL or prop.prop_trm = '01/01/1900') and
(','+@TIN+',' LIKE '%,'+ pro_tax1 + ',%')

order by pro_pc






quote:
Originally posted by James K

It should work for any number of parameters. Compare with the test code below to see what you might be doing differently. You can copy and paste it to an SSMS window to run it.
CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd,xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/05/2013 :  12:28:58  Show Profile  Reply with Quote
You don't need to create a temp table. I was only demonstrating how it would/should work using the temp table because I don't have access to the actual tables you are using.

Examine the string that you are passing in. Subtle issues with that string can cause incorrect behavior. For example, in the example below, which is (almost) the same as what I posted earlier, you will get only 3 values in the result, when in fact you would be expecting four. This is because there is a space (that I deliberately added) before the "xyz" in the @TIN string. It could be issues like that that is causing your code to function incorrectly.
CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd, xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;

Edited by - James K on 03/05/2013 12:29:31
Go to Top of Page

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 03/05/2013 :  13:21:23  Show Profile  Reply with Quote
oh I feel silly now. Sorry.

So I tested my data and it is weird because it is only picking the first 3. Then If I take them out of the equation, then it pick the next 2 tax id. Do I need to change the WHERE ','+@tin+','LIKE '%,'+pro_tax1+',%'?

See what I have below, for just testing of the 23 tins.

CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp (pro_tax1)

Select Distinct
pro_tax1
from pro


DECLARE @TIN VARCHAR(32) = '205253494,210635001,203261266,222522946,222568525,223008680,223361862,223431049,223612265,231352191,231396763,231913910,232325058,232497355,
232515999,232571699,232627944,232794121,261649038,272871206,274357794,462009036,510064326';-----'abcd, xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+','LIKE '%,'+pro_tax1+',%'



quote:
Originally posted by James K

You don't need to create a temp table. I was only demonstrating how it would/should work using the temp table because I don't have access to the actual tables you are using.

Examine the string that you are passing in. Subtle issues with that string can cause incorrect behavior. For example, in the example below, which is (almost) the same as what I posted earlier, you will get only 3 values in the result, when in fact you would be expecting four. This is because there is a space (that I deliberately added) before the "xyz" in the @TIN string. It could be issues like that that is causing your code to function incorrectly.
CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd, xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/05/2013 :  13:29:23  Show Profile  Reply with Quote
Change the declaration of @TIN to be long enough:
....
DECLARE @TIN VARCHAR(8000) = 
....
Go to Top of Page

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 03/05/2013 :  14:25:52  Show Profile  Reply with Quote
Thanks, that worked.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000