February 9, 2017 at 12:30 pm
I've tried escaping the '/' forward slash in various ways I found by googling my problem but nothing is working so far. This works if I use in in a query but not in a stored procedure (just the snippet with the error; the whole sproc code follows). select substring(mbr_dob, len(mbr_dob) - patindex('%/%', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex('/',mbr_dob) -1) as numeric),'00') + works
format(cast(SUBSTRING(mbr_dob,CHARINDEX('/',mbr_dob)+1, CHARINDEX('/',mbr_dob,CHARINDEX('/',mbr_dob)+1) -CHARINDEX('/',mbr_dob)-1) as numeric), '00') from myTable
but this doesn't:CREATE PROCEDURE [dbo].[InsertImportedMemberDataToEligibleMembersTableFromBCBSM_MA]
@YYYYMMOfData nvarchar(6)
,@TableName nvarchar(150)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX);
set @sql =N'insert into eligiblemember(Memtype, Division, MemberID, FirstName, MiddleName, LastName, Suffix, Addr1, Addr2, City, State, Zip, SSN4, Phone, County, BirthDate, Gender, MemberEffectiveDate, MemberEndDate, PCP, PCPNPI, PCPFirstName, PCPLastName, PCPSpecialty, BenefitPackage, AreaPPG, PullDate, Payer, PayerSubGroup, YYYYMMOfData)
select distinct NULL, NULL,Contract_MBR,left([MBR_First_NM],20), NULL,left([MBR_Last_NM],20), NULL, left(Addr1,36), NULL, left(City,24), left(BCBSMMA.[State],2), left([Zip],10),NULL,left(BCBSMMA.Phone,10),NULL
,substring(mbr_dob, len(mbr_dob) - patindex('%/%', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex('/',mbr_dob) -1) as numeric),'00') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX('/',mbr_dob)+1, CHARINDEX('/',mbr_dob,CHARINDEX('/',mbr_dob)+1) -CHARINDEX('/',mbr_dob)-1) as numeric), '00')
, left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12), left(ampi.First,20), left(ampi.Last,20), left(ampi.Specialty,2),NULL,NULL
,PULL_ID
,''BCBSMMA''
, NULL
,@YYYYMMOfData
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from' + QUOTENAME(@TableName) + N'bcbsmMA'
inner join All_MPP_Prov_Info ampi
on bcbsmMA.NPI=ampi.NPI
EXECUTE sp_executesql @sql
END
The error is: Incorrect syntax near '/'. Intellisense shows the red squiggly line here: '%/%', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex('
If I replace the forward slash with an integer the error goesaway but that's obviously not the delimiter I'm looking for to split the column's data. I'm taking a pseudodate column from an external source that's like this (slashes included) MM/DD/YYYY where MM can be M and DD can be D and reformat is as a string like this (YYYYMMDD with the single digit month or day left zero padded (e.g. 03/05/1999).
The methods of escaping the forward slash I tried: using a \ backslash in front of the slash, enclosing the slash in square brackets, adding " 'escape '\' " to the query without the double quotes.
I've been trying to sort this out and google for hours to no avail.
Thanks.
February 9, 2017 at 12:43 pm
The problem is you are using dynamic sql in your procedure and the text isn't quoted properly.
Try this:
set @sql =N'insert into eligiblemember(Memtype, Division, MemberID, FirstName, MiddleName, LastName, Suffix, Addr1, Addr2, City, State, Zip, SSN4, Phone, County, BirthDate, Gender, MemberEffectiveDate, MemberEndDate, PCP, PCPNPI, PCPFirstName, PCPLastName, PCPSpecialty, BenefitPackage, AreaPPG, PullDate, Payer, PayerSubGroup, YYYYMMOfData)
select distinct NULL, NULL,Contract_MBR,left([MBR_First_NM],20), NULL,left([MBR_Last_NM],20), NULL, left(Addr1,36), NULL, left(City,24), left(BCBSMMA.[State],2), left([Zip],10),NULL,left(BCBSMMA.Phone,10),NULL
,substring(mbr_dob, len(mbr_dob) - patindex(''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''/'',mbr_dob) -1) as numeric),''00'') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX(''/'',mbr_dob)+1, CHARINDEX(''/'',mbr_dob,CHARINDEX(''/'',mbr_dob)+1) -CHARINDEX(''/'',mbr_dob)-1) as numeric), ''00'')
, left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12), left(ampi.First,20), left(ampi.Last,20), left(ampi.Specialty,2),NULL,NULL
,PULL_ID
,''BCBSMMA''
, NULL
,@YYYYMMOfData
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from' + QUOTENAME(@TableName) + N'bcbsmMA'
There were six locations in your @sql variable that needed the single quote escaped with another single quote. So normally you might write a query such asSELECT * FROM myTable WHERE myColumn LIKE 'Smi%'
but if you try to make it into a string it will think the end of that string is the space before LIKE because that's where the next quote is.
So that would become@sql = 'SELECT @ FROM myTable WHERE MyColumn LIKE ''Smi%'''
February 9, 2017 at 12:46 pm
The problem is, you've got you're code inside a "SET @sql=" block, so what's happening is when it hits the single quote around your /, it thinks that's the end of the block.
Try doubling up the single quotes around the slashes, so something like:''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''
Possibly, you might even need to quadruple quote those:''''%/%'''', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''''
February 9, 2017 at 12:53 pm
Y.B. - Thursday, February 9, 2017 12:43 PMThe problem is you are using dynamic sql in your procedure and the text isn't quoted properly.Try this:
set @sql =N'insert into eligiblemember(Memtype, Division, MemberID, FirstName, MiddleName, LastName, Suffix, Addr1, Addr2, City, State, Zip, SSN4, Phone, County, BirthDate, Gender, MemberEffectiveDate, MemberEndDate, PCP, PCPNPI, PCPFirstName, PCPLastName, PCPSpecialty, BenefitPackage, AreaPPG, PullDate, Payer, PayerSubGroup, YYYYMMOfData)
select distinct NULL, NULL,Contract_MBR,left([MBR_First_NM],20), NULL,left([MBR_Last_NM],20), NULL, left(Addr1,36), NULL, left(City,24), left(BCBSMMA.[State],2), left([Zip],10),NULL,left(BCBSMMA.Phone,10),NULL
,substring(mbr_dob, len(mbr_dob) - patindex(''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''/'',mbr_dob) -1) as numeric),''00'') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX(''/'',mbr_dob)+1, CHARINDEX(''/'',mbr_dob,CHARINDEX(''/'',mbr_dob)+1) -CHARINDEX(''/'',mbr_dob)-1) as numeric), ''00'')
, left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12), left(ampi.First,20), left(ampi.Last,20), left(ampi.Specialty,2),NULL,NULL
,PULL_ID
,''BCBSMMA''
, NULL
,@YYYYMMOfData
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from' + QUOTENAME(@TableName) + N'bcbsmMA'
Thanks Y.B.! So double single quotes on everything inside the opening single quote on @sql and the closing single quote at from (before the QUOTENAME(@TableName) + N'bcbsmMA'. Lesson learned. I appreciate it.
Now the error that I'm getting is with the join "Incorrect syntax at 'inner'". Does this mean I still need to do something else with quoting?
February 9, 2017 at 12:58 pm
pharmkittie - Thursday, February 9, 2017 12:53 PMY.B. - Thursday, February 9, 2017 12:43 PMThe problem is you are using dynamic sql in your procedure and the text isn't quoted properly.Try this:
set @sql =N'insert into eligiblemember(Memtype, Division, MemberID, FirstName, MiddleName, LastName, Suffix, Addr1, Addr2, City, State, Zip, SSN4, Phone, County, BirthDate, Gender, MemberEffectiveDate, MemberEndDate, PCP, PCPNPI, PCPFirstName, PCPLastName, PCPSpecialty, BenefitPackage, AreaPPG, PullDate, Payer, PayerSubGroup, YYYYMMOfData)
select distinct NULL, NULL,Contract_MBR,left([MBR_First_NM],20), NULL,left([MBR_Last_NM],20), NULL, left(Addr1,36), NULL, left(City,24), left(BCBSMMA.[State],2), left([Zip],10),NULL,left(BCBSMMA.Phone,10),NULL
,substring(mbr_dob, len(mbr_dob) - patindex(''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''/'',mbr_dob) -1) as numeric),''00'') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX(''/'',mbr_dob)+1, CHARINDEX(''/'',mbr_dob,CHARINDEX(''/'',mbr_dob)+1) -CHARINDEX(''/'',mbr_dob)-1) as numeric), ''00'')
, left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12), left(ampi.First,20), left(ampi.Last,20), left(ampi.Specialty,2),NULL,NULL
,PULL_ID
,''BCBSMMA''
, NULL
,@YYYYMMOfData
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from' + QUOTENAME(@TableName) + N'bcbsmMA'Thanks Y.B.! So double single quotes on everything inside the opening single quote on @sql and the closing single quote at from (before the QUOTENAME(@TableName) + N'bcbsmMA'. Lesson learned. I appreciate it.
Now the error that I'm getting is with the join "Incorrect syntax at 'inner'". Does this mean I still need to do something else with quoting?
No problem
The other problem is because the rest of your statement needs to be part of the string.
QUOTENAME(@TableName) + N'bcbsmMA
inner join All_MPP_Prov_Info ampi
on bcbsmMA.NPI=ampi.NPI'
February 9, 2017 at 1:01 pm
Great. Thanks very much.
February 9, 2017 at 1:03 pm
jasona.work - Thursday, February 9, 2017 12:46 PMThe problem is, you've got you're code inside a "SET @sql=" block, so what's happening is when it hits the single quote around your /, it thinks that's the end of the block.
Try doubling up the single quotes around the slashes, so something like:''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''
Possibly, you might even need to quadruple quote those:''''%/%'''', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''''
Thanks for the help jasona.
February 9, 2017 at 1:06 pm
pharmkittie - Thursday, February 9, 2017 12:53 PMY.B. - Thursday, February 9, 2017 12:43 PMThe problem is you are using dynamic sql in your procedure and the text isn't quoted properly.Try this:
set @sql =N'insert into eligiblemember(Memtype, Division, MemberID, FirstName, MiddleName, LastName, Suffix, Addr1, Addr2, City, State, Zip, SSN4, Phone, County, BirthDate, Gender, MemberEffectiveDate, MemberEndDate, PCP, PCPNPI, PCPFirstName, PCPLastName, PCPSpecialty, BenefitPackage, AreaPPG, PullDate, Payer, PayerSubGroup, YYYYMMOfData)
select distinct NULL, NULL,Contract_MBR,left([MBR_First_NM],20), NULL,left([MBR_Last_NM],20), NULL, left(Addr1,36), NULL, left(City,24), left(BCBSMMA.[State],2), left([Zip],10),NULL,left(BCBSMMA.Phone,10),NULL
,substring(mbr_dob, len(mbr_dob) - patindex(''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''/'',mbr_dob) -1) as numeric),''00'') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX(''/'',mbr_dob)+1, CHARINDEX(''/'',mbr_dob,CHARINDEX(''/'',mbr_dob)+1) -CHARINDEX(''/'',mbr_dob)-1) as numeric), ''00'')
, left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12), left(ampi.First,20), left(ampi.Last,20), left(ampi.Specialty,2),NULL,NULL
,PULL_ID
,''BCBSMMA''
, NULL
,@YYYYMMOfData
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from' + QUOTENAME(@TableName) + N'bcbsmMA'Thanks Y.B.! So double single quotes on everything inside the opening single quote on @sql and the closing single quote at from (before the QUOTENAME(@TableName) + N'bcbsmMA'. Lesson learned. I appreciate it.
Now the error that I'm getting is with the join "Incorrect syntax at 'inner'". Does this mean I still need to do something else with quoting?
I think that is because SQL thinks your "SET" command has completed and is looking for the next keyword and is getting confused about it being "INNER". SQL statements cannot start with "INNER", so you need to include that inner join as part of your SET.
TL;DR - yes, missing some quotes:
CREATE PROCEDURE [dbo].[InsertImportedMemberDataToEligibleMembersTableFromBCBSM_MA]
@YYYYMMOfData nvarchar(6)
,@TableName nvarchar(150)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX);
set @sql =N'insert into eligiblemember(Memtype, Division, MemberID, FirstName, MiddleName, LastName, Suffix, Addr1, Addr2, City, State, Zip, SSN4, Phone, County, BirthDate, Gender, MemberEffectiveDate, MemberEndDate, PCP, PCPNPI, PCPFirstName, PCPLastName, PCPSpecialty, BenefitPackage, AreaPPG, PullDate, Payer, PayerSubGroup, YYYYMMOfData)
select distinct NULL, NULL,Contract_MBR,left([MBR_First_NM],20), NULL,left([MBR_Last_NM],20), NULL, left(Addr1,36), NULL, left(City,24), left(BCBSMMA.[State],2), left([Zip],10),NULL,left(BCBSMMA.Phone,10),NULL
,substring(mbr_dob, len(mbr_dob) - patindex(''%/%'', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex(''/'',mbr_dob) -1) as numeric),''00'') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX(''/'',mbr_dob)+1, CHARINDEX(''/'',mbr_dob,CHARINDEX(''/'',mbr_dob)+1) -CHARINDEX(''/'',mbr_dob)-1) as numeric), ''00'')
, left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12), left(ampi.First,20), left(ampi.Last,20), left(ampi.Specialty,2),NULL,NULL
,PULL_ID
,''BCBSMMA''
, NULL
,@YYYYMMOfData
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from' + QUOTENAME(@TableName) + N'bcbsmMA
inner join All_MPP_Prov_Info ampi
on bcbsmMA.NPI=ampi.NPI'
EXECUTE sp_executesql @sql
END
I THINK that is what you want. I was just eye-balling the code and making the changes others had noted.
EDIT - had some typos in the query... I missed some 's
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 9, 2017 at 1:57 pm
Here is an alternative. You will notice I also reformatted your code. Formatting is not for the computer, it is to make the code more readable and understandable to the human trying to read it.
I found this trick here on ssc, not sure who from as I believe I have seen several use it. I found it very good when I found myself counting tick marks to get code right. When you are doing this, ''''''N'''''', to get code right you are working too hard.
go
CREATE PROCEDURE [dbo].[InsertImportedMemberDataToEligibleMembersTableFromBCBSM_MA]
@YYYYMMOfData nvarchar(6)
,@TableName nvarchar(150)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX);
set @sql = "
insert into eligiblemember(
Memtype,
Division,
MemberID,
FirstName,
MiddleName,
LastName,
Suffix,
Addr1,
Addr2,
City,
State,
Zip,
SSN4,
Phone,
County,
BirthDate,
Gender,
MemberEffectiveDate,
MemberEndDate,
PCP,
PCPNPI,
PCPFirstName,
PCPLastName,
PCPSpecialty,
BenefitPackage,
AreaPPG,
PullDate,
Payer,
PayerSubGroup,
YYYYMMOfData)
select distinct
NULL,
NULL,
Contract_MBR,left([MBR_First_NM],20),
NULL,
left([MBR_Last_NM],20),
NULL,
left(Addr1,36),
NULL,
left(City,24),
left(BCBSMMA.[State],2),
left([Zip],10),
NULL,
left(BCBSMMA.Phone,10),
NULL,
substring(mbr_dob, len(mbr_dob) - patindex('%/%', reverse(mbr_dob)) + 2, len(mbr_dob))
+ format(cast(LEFT(mbr_dob, charindex('/',mbr_dob) -1) as numeric),'00')
+ format(cast(SUBSTRING(mbr_dob,CHARINDEX('/',mbr_dob) + 1, CHARINDEX('/',mbr_dob,CHARINDEX('/',mbr_dob) + 1) - CHARINDEX('/',mbr_dob)-1) as numeric), '00'), left(MBR_Gender,2), NULL, NULL,NULL,left(ampi.NPI,12),
left(ampi.First,20), left(ampi.Last,20),
left(ampi.Specialty,2),
NULL,
NULL,
PULL_ID,
'BCBSMMA',
NULL,
" + @YYYYMMOfData + "
--from Patient_Eligibility_BCBSM_Medicare_Advantage BCBSMMA --(this line for testing)
from
" + QUOTENAME(@TableName) + " bcbsmMA
inner join All_MPP_Prov_Info ampi
on bcbsmMA.NPI = ampi.NPI;
";
EXECUTE sp_executesql @sql
END
GO
set quoted_identifier on;
go
February 9, 2017 at 4:11 pm
Thank Lynn Pettis,
I didn't even think of the formatting though I do sometimes take the part of the code that's causing errors and put in on a separate line. I will reformat my stored procedure now. I will also try out your less single-quote-heavy solution tomorrow when I get back to the project. Thanks again!
February 9, 2017 at 4:42 pm
pharmkittie - Thursday, February 9, 2017 4:11 PMThank Lynn Pettis,
I didn't even think of the formatting though I do sometimes take the part of the code that's causing errors and put in on a separate line. I will reformat my stored procedure now. I will also try out your less single-quote-heavy solution tomorrow when I get back to the project. Thanks again!
Just remember that this is needed before the create procedure:
set quoted_identifier off;
go
and this is needed after the stored procedure:
set quoted_identifier on;
go
February 10, 2017 at 2:01 am
-- Original date expression
SELECT substring(mbr_dob, len(mbr_dob) - patindex('%/%', reverse(mbr_dob)) + 2, len(mbr_dob))+format(cast(LEFT(mbr_dob, charindex('/',mbr_dob) -1) as numeric),'00') +
format(cast(SUBSTRING(mbr_dob,CHARINDEX('/',mbr_dob)+1, CHARINDEX('/',mbr_dob,CHARINDEX('/',mbr_dob)+1) -CHARINDEX('/',mbr_dob)-1) as numeric), '00')
FROM (SELECT mbr_dob = '25/11/1958') d
-- Alternative date expression
SELECT RIGHT(REPLACE(mbr_dob,'/',''),4) + LEFT(REPLACE(mbr_dob,'/',''),4)
FROM (SELECT mbr_dob = '25/11/1958') d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 11, 2017 at 9:49 am
Lynn Pettis - Thursday, February 9, 2017 4:42 PMpharmkittie - Thursday, February 9, 2017 4:11 PMThank Lynn Pettis,
I didn't even think of the formatting though I do sometimes take the part of the code that's causing errors and put in on a separate line. I will reformat my stored procedure now. I will also try out your less single-quote-heavy solution tomorrow when I get back to the project. Thanks again!Just remember that this is needed before the create procedure:
set quoted_identifier off;
goand this is needed after the stored procedure:
set quoted_identifier on;
go
Just to ensure the future of the proc during modifications, I'd add a note stating that in the header comments so that people aren't surprised after they make a future modification.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply