Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Dec 19, 2012 04:47 AM by vendan
Dec 18, 2012 01:47 AM|LINK
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.
Dec 18, 2012 02:20 AM|LINK
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
Dec 18, 2012 02:33 AM|LINK
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.
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.
Dec 19, 2012 04:47 AM|LINK
Try below query, it will be return list fo procedure which have '(space)'
SELECT name FROM SYS.PROCEDURES WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'' ''%'