Creating Stored Procedure problem with a union in it.

Question

Creating Stored Procedure problem with a union in it.

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE

If you were to save the string beginning 'CREATE PROCEDURE...' to a variable then examine it using PRINT, you would see where it all goes terribly wrong, at the single quote pairs designating empty strings: '' as MGCTag, '' as Description
I'd recommend you do exactly that, because it can be fiendishly difficult figuring out exactly how many escape quotes to use.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

anthonylinhardt 9768 SSC Veteran Points: 201 More actions · Answer 2

ChrisM@Work - Wednesday, July 19, 2017 8:55 AM
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE
If you were to save the string beginning 'CREATE PROCEDURE...' to a variable then examine it using PRINT, you would see where it all goes terribly wrong, at the single quote pairs designating empty strings: '' as MGCTag, '' as Description
I'd recommend you do exactly that, because it can be fiendishly difficult figuring out exactly how many escape quotes to use.

That didn't even occur to me that the single quotes were the problem. Solved it by doing it as: NULL as MGCTag, etc. Thanks for the heads up, I really appreciated you getting my brain going. 🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

anthonylinhardt 9768 - Wednesday, July 19, 2017 11:06 AM
ChrisM@Work - Wednesday, July 19, 2017 8:55 AM
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE
If you were to save the string beginning 'CREATE PROCEDURE...' to a variable then examine it using PRINT, you would see where it all goes terribly wrong, at the single quote pairs designating empty strings: '' as MGCTag, '' as Description
I'd recommend you do exactly that, because it can be fiendishly difficult figuring out exactly how many escape quotes to use.

That didn't even occur to me that the single quotes were the problem. Solved it by doing it as: NULL as MGCTag, etc. Thanks for the heads up, I really appreciated you getting my brain going. 🙂

But selecting an empty string ('') is not the same as selecting a NULL, so the queries in the stored procedure have changed.

SELECT CASE WHEN NULL = '' THEN 'Match' ELSE 'No Match' END;

It's no problem to query NULLs; just make sure this is really what you want to do.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions