Stored proc problem

  • 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

  • The statement before the MERGE statement is required to be terminated with a semi-colon.

    John

  • 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)

    ;

  • 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

  • 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 🙂

  • 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

  • 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;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Maybe @W_NoOfBBMs returns Null ?

  • 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