Variables being ignored ?

  • Hi,

    I'm having a crazy time trying to understand a problem I have in a script.

    The script is designed that if someone accidentally runs it twice that it won't error and won't do anything stupid like add the same data twice.

    I can't post the script up but hopefully someone will understand what is happening from the description.

    Basically I create a new table that contains lookup values called LookupPriority

    I end the batch with a GO

    I alter two existing tables (ProductEventConfig and ProductDetails) to reference this table and create a foreign key constraint on it.

    I end the batch with a GO

    I drop and recreate some triggers on the two existing tables.

    I end the batch with a GO

    I declare variables @high, @med, @low

    I populate each variable with the IDs of the High, Med and Low records in the LookupPriority table.

    If @high is null (which it will be if the script is executed the first time) I create the High record and populate @high with scope_identity()

    the same is true of @med and @low.

    I now update the ProductEventConfig and ProductDetails tables, setting the new column to @med and change the new column to be NOT NULL.

    I end the batch with a GO

    I now want to copy all the data in the ProductEvent table and reinsert it but as High and again with Low.

    I declare another set of variables called @high, @med, @low and populate it by selecting the relevant record from the LookupPriority table.

    That's all fine and I can see the values have been set.

    I then put the following code to copy the Med value records in but set as Low:

    IF NOT EXISTS(SELECT * FROM dbo.ProductEventConfig WHERE LookupPriorityID=@low)

    BEGIN

    INSERTdbo.ProductEventConfig

    ( ProductID ,

    PlacementTypeID ,

    AlarmHandlingText ,

    PlayAudibleAlert ,

    LookupPriorityID

    )

    SELECT ProductID ,

    PlacementTypeID ,

    AlarmHandlingText ,

    PlayAudibleAlert ,

    @low

    FROMdbo.ProductEventConfig

    WHERELookupPriorityID = @med

    END

    The first time works perfectly, subsequent executions of the script keep doubling up, almost as if the WHERE clause in both the Exists and the insert statement are being ignore.

    Now here's the crazy bit...

    I've resolved this issue but putting a WHERE clause in the preceding batch so that when I update the ProductEventConfig and ProductDetails tables it only does it WHERE LookupPriorityID IS NULL

    I don't understand why that worked, the variables are in in separate batches and can be read so they contain the correct values.

    I'm using Microsoft SQL Server 2012 (SP1) 11.0.3128.0 (X64) Enterprise Edition (64-bit) in a development environment with 8GB.

    So if anyone can explain why this would happen I can sleep easier at night.

    Thanks

    Giles

  • No way I am trying to muddle through that many words of complex logic and actions/outcomes!! I doubt anyone else will bother either. Even if you can't post your production code, I am SURE that if you really want an answer you can de-identify it sufficiently so that ALL code, objects, etc. act as you have described. But even then I need to add that what you have may still be more than the typical point-and-shoot forum post. Some may still choose to attack it though just because it actually sounds quite interesting. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • A fair comment Kevin, thanks.

    I've taken the time to create a non sensitive version of the script and attached it (Problem Script.zip).

    It might also be worth mentioning that, because the script is designed to withstand executing twice, if you execute just the last batch that always works fine, just when you run the whole script twice or more (regardless of time in between)

    Thanks

    Giles

  • Why not just post the code instead of a zip file that contains a file that contains the code?

    IF NOT EXISTS(SELECT * FROM sysObjects WHERE Name='LookupPriority' AND xType='U')

    BEGIN

    CREATE TABLE dbo.LookupPriority

    (

    LookupPriorityID SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    DESCRIPTION NVARCHAR(256) NOT NULL,

    SortOrder SMALLINT

    )

    END

    GO

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='LookupPriorityID' AND TABLE_NAME='ProductEventConfig')

    BEGIN

    ALTER TABLE dbo.ProductEventConfig

    ADD LookupPriorityID SMALLINT

    ALTER TABLE dbo.ProductEventConfig

    ADD CONSTRAINT FK_ProductEventConfig_LookupPriority FOREIGN KEY (LookupPriorityID) REFERENCES dbo.LookupPriority(LookupPriorityID)

    END

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='LookupPriorityID' AND TABLE_NAME='ProductDetails')

    BEGIN

    ALTER TABLE dbo.ProductDetails

    ADD LookupPriorityID SMALLINT

    ALTER TABLE dbo.ProductDetails

    ADD CONSTRAINT FK_ProductDetails_LookupPriority FOREIGN KEY (LookupPriorityID) REFERENCES dbo.LookupPriority(LookupPriorityID)

    END

    GO

    -- TRIGGER STUFF WOULD GO HERE

    GO

    DECLARE @LookupID INT

    DECLARE @LowID INT

    DECLARE @med INT

    DECLARE @HighID INT

    SELECT @LookupID = (SELECT LookupPriorityID FROM dbo.LookupPriority WHERE Description=N'Low')

    IF @LookupID IS NULL

    BEGIN

    INSERT dbo.LookupPriority

    (

    Description

    )

    VALUES (

    N'Low'

    )

    SET @LookupID=SCOPE_IDENTITY()

    END

    SET@LowID = @LookupID

    SELECT @LookupID = (SELECT LookupPriorityID FROM dbo.LookupPriority WHERE Description=N'Med')

    IF @LookupID IS NULL

    BEGIN

    INSERT dbo.LookupPriority

    (

    Description

    )

    VALUES (

    N'Med'

    )

    SET @LookupID=SCOPE_IDENTITY()

    END

    SET @med = @LookupID

    SELECT @LookupID = (SELECT LookupPriorityID FROM dbo.LookupPriority WHERE Description=N'High')

    IF @LookupID IS NULL

    BEGIN

    INSERT dbo.LookupPriority

    (

    Description

    )

    VALUES (

    N'High'

    )

    SET @LookupID=SCOPE_IDENTITY()

    END

    SET @HighID = @LookupID

    UPDATE dbo.ProductDetails

    SET LookupPriorityID = @med

    WHERE LookupPriorityID IS NULL --WHEN THIS LINE IS IN PLACE IT WORKS

    ALTER TABLE dbo.ProductDetails

    ALTER COLUMN LookupPriorityID SMALLINT NOT NULL

    UPDATE dbo.ProductEventConfig

    SET LookupPriorityID = @med

    WHERE LookupPriorityID IS NULL --WHEN THIS LINE IS IN PLACE IT WORKS

    ALTER TABLE dbo.ProductEventConfig

    ALTER COLUMN LookupPriorityID SMALLINT NOT NULL

    GO

    -- PROBLEM AREA

    DECLARE @LowID INT

    DECLARE @med INT

    DECLARE @HighID INT

    SELECT @LowID = LookupPriorityID FROM LookupPriority WHERE Description ='Low'

    SELECT @med = LookupPriorityID FROM LookupPriority WHERE Description ='Normal'

    SELECT @HighID = LookupPriorityID FROM LookupPriority WHERE Description ='High'

    --SELECT @LowID,@Med,@HighID

    DECLARE @ProductEventConfig TABLE

    (

    AlarmTypeLookupID SMALLINT,

    PlacementTypeLookupID SMALLINT,

    AlarmHandlingText nvarchar(4000),

    PlayAudibleAlert BIT,

    LookupPriorityID SMALLINT

    )

    --SELECT TOP 1 * FROM dbo.ProductEventConfig WHERE LookupPriorityID=@LowID

    --SELECT TOP 1 * FROM dbo.ProductEventConfig WHERE LookupPriorityID=@HighID

    IF NOT EXISTS(SELECT * FROM dbo.ProductEventConfig WHERE LookupPriorityID=@LowID)

    BEGIN

    --PRINT 'get low'+ISNULL(CAST(@LowID AS VARCHAR(100)),' X')

    INSERTdbo.ProductEventConfig

    ( AlarmTypeLookupID ,

    PlacementTypeLookupID ,

    AlarmHandlingText ,

    PlayAudibleAlert ,

    LookupPriorityID

    )

    SELECT AlarmTypeLookupID ,

    PlacementTypeLookupID ,

    AlarmHandlingText ,

    PlayAudibleAlert ,

    @LowID

    FROMdbo.ProductEventConfig

    WHERELookupPriorityID = @med

    END

    IF NOT EXISTS(SELECT * FROM dbo.ProductEventConfig WHERE LookupPriorityID=@HighID)

    BEGIN

    --PRINT 'get high'+ISNULL(CAST(@HighID AS VARCHAR(100)),' Y')

    INSERT dbo.ProductEventConfig

    ( AlarmTypeLookupID ,

    PlacementTypeLookupID ,

    AlarmHandlingText ,

    PlayAudibleAlert ,

    LookupPriorityID

    )

    SELECT AlarmTypeLookupID ,

    PlacementTypeLookupID ,

    AlarmHandlingText ,

    PlayAudibleAlert ,

    @HighID

    FROMdbo.ProductEventConfig

    WHERELookupPriorityID = @med

    END

    GO

    I suspect the problem is because you have what appears to be a logic issue in the last two inserts. Your first one checks to see if there is a row in ProductEventConfig for LowID but then you insert Med. The second insert is similar. You check for High and insert Med. Shouldn't the exists check and the insert be the same thing?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi Sean,

    Thanks for looking, however I think you've misread the code.

    You're correct that the EXISTS is checking to see if @LowID is already in the LookupPriorityID column but then I'm inserting @LowID into that column. The @med is just filtering the records down before they get inserted.

    That aside I really do appreciate you looking over it.

    Giles.

  • I think the issue may be that this format of query:

    SELECT @variable = column FROM table WHERE ...

    leaves the original value of @variable in place if a row is not found; i.e., it does not set the @variable to NULL (as one might logically expect it to). Demonstration code to prove this is below.

    Two possible workarounds:

    1) explicitly set the @variable to NULL before issuing the SELECT

    2) use format SET (or SELECT) @variable = (SELECT column FROM table WHERE ...)

    DECLARE @value int

    SELECT @value = object_id FROM master.sys.objects WHERE name = 'spt_values'

    PRINT CASE WHEN @value is null then 'null' else cast(@value as varchar(10)) end

    SELECT @value = object_id FROM master.sys.objects WHERE name = 'table_does_not_exist'

    PRINT CASE WHEN @value is null then 'null' else cast(@value as varchar(10)) end

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks Scott.

    I did check the values are set (there is a bit commented out) plus it doesn't explain how it runs in isolation but not when the whole script it executed.

    However I really like your suggestion about setting the variables and will incorporate that in future scripts.

    Giles

  • I noticed strange behavior using out args with defaults and out arg does not get set.

    create proc dbo.BLAH(@ClassId int = -1 out)

    if(isnull(@NavClassId, 0) <= 0) set @NavClassId = -1 -- tom foolery to get the value back in the caller

    -- it sees the value I assign going in but if the value is not explicitly set the value out will not be set

Viewing 8 posts - 1 through 7 (of 7 total)

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