June 6, 2014 at 4:43 am
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
June 6, 2014 at 6:11 am
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
June 6, 2014 at 8:35 am
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
June 6, 2014 at 9:07 am
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/
June 6, 2014 at 9:29 am
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.
June 6, 2014 at 3:17 pm
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".
June 9, 2014 at 1:51 am
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
June 9, 2014 at 12:49 pm
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