Author |
Topic |
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2013-02-05 : 08:50:42
|
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)ASBEGINif 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_TAXCREATE 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 Distinctpro.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_xtypWHEN 'ANCI' THEN pro.pro_officeWHEN 'HOSP' THEN pro.pro_officeELSE (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' ENDFROM impact.dbo.pro proJOIN 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_pcsEnd |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 09:07:18
|
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/ |
|
|
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2013-02-05 : 09:19:04
|
This worked Perfectly James, thanks! I will also take a look at the other site you sent me too. Thanks again! :) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 09:59:22
|
You are very welcome - glad to help. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2013-03-05 : 10:58:43
|
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/
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-05 : 11:12:16
|
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 expectedDROP TABLE #tmp; |
|
|
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2013-03-05 : 12:17:27
|
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 Distinctpro_tax1from impact.dbo.proDECLARE @TIN VARCHAR(32)----DROP TABLE #tmp;BEGINif 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_TAXCREATE 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 Distinctpro.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_xtypWHEN 'ANCI' THEN pro.pro_officeWHEN 'HOSP' THEN pro.pro_officeELSE (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' ENDFROM impact.dbo.pro proJOIN 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_pcquote: 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 expectedDROP TABLE #tmp;
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-05 : 12:28:58
|
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 expectedDROP TABLE #tmp; |
|
|
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2013-03-05 : 13:21:23
|
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 Distinctpro_tax1from proDECLARE @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 expectedDROP TABLE #tmp;
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-05 : 13:29:23
|
Change the declaration of @TIN to be long enough:....DECLARE @TIN VARCHAR(8000) = .... |
|
|
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2013-03-05 : 14:25:52
|
Thanks, that worked. |
|
|
|
|
|