July 19, 2017 at 7:47 am
I am trying to create a stored procedure that has a union in it and it gives me the error "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." when I execute it to get it created. If you take the code within the Stored Procedure and execute it separately it works. It only has the issue when you are trying to execute the create stored procedure.
This produces the error:
/****** Object: StoredProcedure [dbo].[spSearchAdminInventoryMaintenance] Script Date: 2/15/2017 9:36:17 AM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSearchAdminInventoryMaintenance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].spSearchAdminInventoryMaintenance
GO
/****** Object: StoredProcedure [dbo].[spSearchAdminInventoryMaintenance] Script Date: 2/15/2017 9:36:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSearchAdminInventoryMaintenance]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spSearchAdminInventoryMaintenance]
AS
DECLARE @CNCTID as int;
SET @CNCTID = 210
DECLARE @TempTable TABLE
(
ID int,
MGCTag nvarchar(100),
Description nvarchar(250)
)
INSERT INTO @TempTable (ID, MGCTag, Description)
SELECT T1.ID, T1.MGCTag, T1.Description
FROM AdminInventoryMaintenance T1
WHERE T1.UserCNCTID = @CNCTID
UNION
SELECT T2.AdminInvID AS ID, T2.MGCTag, T2.Description
FROM AdminInventoryMaintenanceHistory T2
WHERE T2.UserCNCTID = @CNCTID
UNION
SELECT T3.AdminInvMaintID AS ID, '' as MGCTag, '' as Description
FROM AdminInventoryNotes T3
WHERE T3.UserCNCTID = @CNCTID
UNION
SELECT T4.AdminInvMaintID AS ID, '' as MGCTag, '' as Description
FROM AdminInvMaintUploadedDocs T4
WHERE T4.EnteredByCNCTID = @CNCTID
UNION
SELECT T5.AdminInventoryMaintenanceID AS ID, '' as MGCTag, '' as Description
FROM AdminInvVerification T5
WHERE T5.CNCTID = @CNCTID
SELECT * FROM AdminInventoryMaintenance
WHERE ID IN (SELECT ID FROM @TempTable)
ORDER BY ID ASC
--END
--COMMIT;
'
END
GO
But this works by itself:
DECLARE @CNCTID as int;
SET @CNCTID = 210
DECLARE @TempTable TABLE
(
ID int,
MGCTag nvarchar(100),
Description nvarchar(250)
)
INSERT INTO @TempTable (ID, MGCTag, Description)
SELECT T1.ID, T1.MGCTag, T1.Description
FROM AdminInventoryMaintenance T1
WHERE T1.UserCNCTID = @CNCTID
UNION
SELECT T2.AdminInvID AS ID, T2.MGCTag, T2.Description
FROM AdminInventoryMaintenanceHistory T2
WHERE T2.UserCNCTID = @CNCTID
UNION
SELECT T3.AdminInvMaintID AS ID, '' as MGCTag, '' as Description
FROM AdminInventoryNotes T3
WHERE T3.UserCNCTID = @CNCTID
UNION
SELECT T4.AdminInvMaintID AS ID, '' as MGCTag, '' as Description
FROM AdminInvMaintUploadedDocs T4
WHERE T4.EnteredByCNCTID = @CNCTID
UNION
SELECT T5.AdminInventoryMaintenanceID AS ID, '' as MGCTag, '' as Description
FROM AdminInvVerification T5
WHERE T5.CNCTID = @CNCTID
SELECT * FROM AdminInventoryMaintenance
WHERE ID IN (SELECT ID FROM @TempTable)
ORDER BY ID ASC
July 19, 2017 at 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.
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
July 19, 2017 at 11:06 am
ChrisM@Work - Wednesday, July 19, 2017 8:55 AMEXEC dbo.sp_executesql @statement = N'CREATE PROCEDUREIf 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. 🙂
July 19, 2017 at 11:25 am
anthonylinhardt 9768 - Wednesday, July 19, 2017 11:06 AMChrisM@Work - Wednesday, July 19, 2017 8:55 AMEXEC dbo.sp_executesql @statement = N'CREATE PROCEDUREIf 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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply