April 12, 2016 at 3:57 am
Hi
I have a stored proc which doesn't seem to work properly, and I can's see why. Can someone cast a fresh set of eyes over it, and tell me what is wrong? The problem seems to be that the merge statement, and indeed anything after the dynamic sql execution, just doesn't run. Aplogies if I am missing something really obvious.
CREATE PROCEDURE [BBM].[usp_CalculateBBMs]
(@W_LoadId INT,@W_ErrMessage NVARCHAR(4000) OUTPUT)
AS
SET NOCOUNT ON
BEGIN TRY
SET @W_ErrMessage = ' '
DECLARE @W_SQL NVARCHAR(MAX)
DECLARE @W_NoOfBBMs INT
DECLARE @W_BBMId INT
DECLARE @W_BBMLogic NVARCHAR(50)
DECLARE @W_BBM_Version_Id INT
DECLARE @W_BBM_Owner NVARCHAR(20)
CREATE TABLE #BBMDetail
(BBM_Id INT
,BBM_Date DATETIME
,BBM_Value DECIMAL(18,2)
,LoadId INT
,BBM_Version_Id INT
,BBM_Owner NVARCHAR(20)
)
DECLARE @BBMList TABLE
(ListId INT IDENTITY(1,1)
,BBM_Id INT
,BBM_Function NVARCHAR(50)
,BBM_Version_Id INT
,BBM_Owner NVARCHAR(20)
)
-- Get List of Active BBMs to be Calculated
INSERT INTO @BBMList(BBM_Id,BBM_Function,BBM_Version_Id,BBM_Owner)
SELECT BBM_ID,BBM_Function,BBM_Version_Id,BBM_Owner
FROM BBM.BBM_Version WHERE Active = 1
-- Get the count of BBMs to be calculated
SET @W_NoOfBBMs = (SELECT MAX(ListId) from @BBMList)
-- Calculate Each BBM
WHILE (@W_NoOfBBMs > 0)
BEGIN
-- Initialize Variables
SET @W_BBMId = -1
SET @W_BBMLogic = ' '
SET @W_SQL = ''
-- Set Variables
SET @W_BBMId = (SELECT BBM_Id FROM @BBMList WHERE ListId = @W_NoOfBBMs)
SET @W_BBMLogic = (SELECT BBM_Function FROM @BBMList WHERE ListId = @W_NoOfBBMs)
SET @W_BBM_Version_Id = (SELECT BBM_Version_Id FROM @BBMList WHERE ListId = @W_NoOfBBMs)
SET @W_BBM_Owner = (SELECT BBM_Owner FROM @BBMList WHERE ListId = @W_NoOfBBMs)
SET @W_SQL = 'INSERT INTO #BBMDetail
([BBM_Id]
,[BBM_Version_Id]
,[BBM_Owner]
,[BBM_Date]
,[BBM_Value]
,[LoadId]) '
SET @W_SQL = @W_SQL
+ 'SELECT '
+ CAST (@W_BBMId AS VARCHAR(10))
+','
+ CAST (@W_BBM_Version_Id AS VARCHAR(10))
+','
+''''
+ CAST (@W_BBM_Owner AS VARCHAR(20))
+''''
+ ', * ,'
+ CAST(@W_LoadId AS VARCHAR(10))
+ ' FROM '
+ @W_BBMLogic
+ '(' + CAST(@W_LoadId AS VARCHAR(10)) +')'
PRINT @W_SQL --For Debugging
--Execute SQL
EXEC (@W_SQL)
SET @W_NoOfBBMs = @W_NoOfBBMs - 1
END
-- MERGE Routine to Update / Insert BBM_detail
--Debug
PRINT 'Merge into BBM table'
MERGE BBM.BBM_Detail AS TARGET
USING
(SELECT BBM_Id,BBM_Date,BBM_Value,LoadId,BBM_Version_Id,BBM_Owner FROM #BBMDetail) AS SOURCE
ON TARGET.BBM_id = SOURCE.BBM_Id
AND TARGET.BBM_Date = SOURCE.BBM_Date
AND TARGET.BBM_Version_Id = SOURCE.BBM_Version_ID
WHEN MATCHED
AND TARGET.BBM_Value <> SOURCE.BBM_Value
THEN UPDATE SET TARGET.BBM_Value = SOURCE.BBM_Value, TARGET.LoadId = SOURCE.LoadId
WHEN NOT MATCHED
THEN INSERT
(BBM_Id,BBM_Date,BBM_Value,LoadId,BBM_Version_Id,BBM_Owner)
VALUES
(SOURCE.BBM_Id,SOURCE.BBM_Date,SOURCE.BBM_Value,SOURCE.LoadId,SOURCE.BBM_Version_Id,SOURCE.BBM_Owner)
;
END TRY
BEGIN CATCH
-- Capture the Error Message
SELECT @W_ErrMessage = @W_SQL + ' ' + ERROR_MESSAGE()
END CATCH
April 12, 2016 at 4:00 am
The statement before the MERGE statement is required to be terminated with a semi-colon.
John
April 12, 2016 at 4:14 am
thanks for replying. I tried this, but it made no difference:
snip
--Execute SQL
EXEC (@W_SQL)
SET @W_NoOfBBMs = @W_NoOfBBMs - 1
END;
-- MERGE Routine to Update / Insert BBM_detail
MERGE BBM.BBM_Detail AS TARGET
USING
(SELECT BBM_Id,BBM_Date,BBM_Value,LoadId,BBM_Version_Id,BBM_Owner FROM #BBMDetail) AS SOURCE
ON TARGET.BBM_id = SOURCE.BBM_Id
AND TARGET.BBM_Date = SOURCE.BBM_Date
AND TARGET.BBM_Version_Id = SOURCE.BBM_Version_ID
WHEN MATCHED
AND TARGET.BBM_Value <> SOURCE.BBM_Value
THEN UPDATE SET TARGET.BBM_Value = SOURCE.BBM_Value, TARGET.LoadId = SOURCE.LoadId
WHEN NOT MATCHED
THEN INSERT
(BBM_Id,BBM_Date,BBM_Value,LoadId,BBM_Version_Id,BBM_Owner)
VALUES
(SOURCE.BBM_Id,SOURCE.BBM_Date,SOURCE.BBM_Value,SOURCE.LoadId,SOURCE.BBM_Version_Id,SOURCE.BBM_Owner)
;
April 12, 2016 at 4:56 am
What's happened to your PRINT statement?
If I were you, I would take this opportunity to terminate all statements with a semi-colon - there'll come a day where unterminated statements are unsupported, so it's a good idea to future-proof your code now.
Do you get any error messages? If not, how do you know the MERGE isn't running? What happens if you SET NOCOUNT OFF - do you see any messages that suggest something may be happening?
Finally, if you're interested, you don't need that WHILE loop - you can concatenate your dynamic SQL in one go using a SELECT statement with a FOR XML clause.
John
April 12, 2016 at 5:29 am
Hi
Thanks again. I took the print statement out, as it wasn't even getting that far.
I set no count too off, and see row counts for the inserts t the temp table, then nothng afterwards.
I know that the merge statement is not firing because I have data in my source for 2016 which does not ever get into the target, and also the load numbers are all really old.
Yes, the code could be rewritten, but I can't do that just now, I need to fix this for production. Will put it on the list for future though, thanks 🙂
April 12, 2016 at 6:01 am
I don't really know what to suggest, then. Are you running the stored procedure with parameters, and if so, what parameters? Have you tried running the code in the stored procedure instead (you'd need to declare the parameter variables for that to work)? What happens if you substitute your MERGE statement for something trivial like SELECT @@VERSION? Have you run a trace or extended events session to capture exactly what happens when you run the stored procedure?
John
April 12, 2016 at 6:10 am
Change your CATCH block to include a THROW rather than a SELECT ...
I suggest you add this before creating the temp table, just in case:
if Object_Id('tempdb..#BBMDetail','U') is not null
drop table #BBMDetail;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 12, 2016 at 6:25 am
Maybe @W_NoOfBBMs returns Null ?
April 12, 2016 at 6:38 am
Hi,
Thanks for all of your replies. I executed the code outside of a stored proc, replacing the input param with a known value. and managed to get the error returned.
It was a data conversion within one of the functions, creating a date from a string.
So, the proc is okay, just need to find out how the errant dates are getting in 🙂
Thank you all again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply