I am looking for advice on how to query missing commas using the IN clause. For example I would like to locate any instance of the following:
select MyField from MyDB where MyField IN ('abc','def' 'ghi', '123')
I can get text of the stored procedure from sys.object, but I'm not sure how to proced since the strings in the IN clause will vary in length and are alphanumeric.
The missing comma between 'def' 'ghi' is my problem. I have found at least one of these queries that have made it all the way to production and they are causing major issues. What I'm looking for is a way to query the entire text of a stored procedure
and find any places that may have a missing comma.
Script all your stored procedures to a text file. (Right-click database: Tasks > Generate Scripts... follow instructions in wizard)
Use ctrl+F in the text file to search for all occurances of ' ' (single-quote, space, single-quote). Check each one to see if it's due to a missing comma and correct it.
I wouldn't trust any procedure that blindly "corrects" all sql code without a human verifying each change. You could make the problem much worse than it is.
-Tab Alleman
Marked as answer by Chen Yu - MSFT on Dec 20, 2012 05:59 AM
KLB
Member
307 Points
76 Posts
Query for missing comma using IN
Dec 18, 2012 01:47 AM|LINK
Hello,
I am looking for advice on how to query missing commas using the IN clause. For example I would like to locate any instance of the following:
select MyField from MyDB where MyField IN ('abc','def' 'ghi', '123')
I can get text of the stored procedure from sys.object, but I'm not sure how to proced since the strings in the IN clause will vary in length and are alphanumeric.
Any advice?
Thanks
jsiahaan
Contributor
2304 Points
588 Posts
Re: Query for missing comma using IN
Dec 18, 2012 02:20 AM|LINK
Hi,
Is this the answer to your problem:
select MyField from MyDB where MyField IN ('abc','def', 'ghi', '123')
You miss a comma between 'def' 'ghi'
Hope can help
Indonesian Humanitarian Foundation
KLB
Member
307 Points
76 Posts
Re: Query for missing comma using IN
Dec 18, 2012 02:33 AM|LINK
jsiahann,
The missing comma between 'def' 'ghi' is my problem. I have found at least one of these queries that have made it all the way to production and they are causing major issues. What I'm looking for is a way to query the entire text of a stored procedure and find any places that may have a missing comma.
Thanks,
KLB
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Query for missing comma using IN
Dec 18, 2012 01:33 PM|LINK
this is how I would do it.
Script all your stored procedures to a text file. (Right-click database: Tasks > Generate Scripts... follow instructions in wizard)
Use ctrl+F in the text file to search for all occurances of ' ' (single-quote, space, single-quote). Check each one to see if it's due to a missing comma and correct it.
I wouldn't trust any procedure that blindly "corrects" all sql code without a human verifying each change. You could make the problem much worse than it is.
vendan
Participant
856 Points
293 Posts
Re: Query for missing comma using IN
Dec 19, 2012 04:47 AM|LINK
Hi,
Try below query, it will be return list fo procedure which have '(space)'
SELECT name FROM SYS.PROCEDURES WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'' ''%'
Kalaivendan
Please Mark as Answer if this post helps you!