CODEWITHSUNDEEP
sql-serverstored-procedures
yosboss
yosboss

Reputation: 3

SQL Server : a strange thing happens with an Update inside a stored procedure

I have a stored procedure in SQL Server 2005 that the code inside is something like this :

select Unique_ID as ID
into tmp_table 
from table1

Then, after some other statements, I do this update :

Update table1 
set Flag = ‘Y’
Where Unique_ID in (select Unique_ID from tmp_table)

As you notice, I've purposely mistaken ID by Unique_ID (the column name in tmp_table) when I wanted to update.

Now the bizarre thing that happens, is that SQL Server doesn’t show an error when I execute the stored procedure.

It ignores the line where the error is :

Where Unique_ID in (select Unique_ID from tmp_table)

And runs just this :

Update table1 set
Flag = ‘Y’

Any ideas?

Thanks a lot.

UPDATE:

Here is the stored procedure code :

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[MyProc]
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @date VARCHAR(20)

    SET @date = REPLACE(CONVERT(VARCHAR(8), GETUTCDATE(), 3), '/', '')
            + REPLACE(CONVERT(VARCHAR(8), GETUTCDATE(), 108), ':', '')

    DECLARE @PATH AS VARCHAR(500)
    SET @PATH = 'C:\MyPath\' 

    BEGIN TRY
            EXEC master.dbo.xp_create_subdir @PATH
    END TRY
    BEGIN CATCH
            PRINT 'FOLDER NOT CREATED'
    END CATCH

    IF EXISTS (SELECT name FROM sys.tables WHERE name = 'TMP_TABLE')
       DROP TABLE TMP_TABLE

    SELECT  
        UNIQUE_ID [Unique ID] ,
        'col1' COL1,
        'col2' COL2,
        'col3' COL3,
        'col4' COL4
    INTO    
        TMP_TABLE
    FROM    
        TABLE1
    WHERE 
        Flag IN 'N'

    --Try to generate file, If there was any problem, return from the SP => no update
    BEGIN TRY
        DECLARE @QUERY VARCHAR(MAX)

        SET @QUERY = 'EXEC master..xp_cmdshell ''sqlcmd -E -s"," -W -h-1 -Q "SET NOCOUNT ON;SELECT * FROM dbo.TMP_TABLE" | findstr /V /C:"-" /B > '
            + @PaTH + '\FileName_' + @date + '.csv'',no_output;'

        EXEC(@QUERY)
    END TRY
    BEGIN CATCH
        PRINT 'Exception during file generation'
        RETURN
    END CATCH

    PRINT 'File Generated'

    UPDATE TABLE1 
    SET Flag = 'Y',
        MODIFICATION_DATE = GETDATE()
    WHERE 
        UNIQUE_ID IN (SELECT UNIQUE_ID FROM TMP_TABLE)

    BEGIN TRY
        DROP TABLE dbo.TMP_TABLE
        PRINT 'TMP table dropped'
    END TRY
    BEGIN CATCH
        PRINT 'TMP table not dropped'
    END CATCH
END
GO

The problem is on this line: 

WHERE UNIQUE_ID IN (SELECT **UNIQUE_ID** FROM TMP_TABLE)

I should replace UNIQUE_ID with [UNIQUE ID]

But SQL Server doesn't throw an error, it simply updates all records.

And here is the structure of table1 :

CREATE TABLE [dbo].[TABLE1]
(
    UNIQUE_ID int NOT NULL IDENTITY(1, 1), --Primary key
    Flag [varchar] (1) ,
    MODIFICATION_DATE datetime,
)

Upvotes: 0

Views: 119

Answers (2)

Dan
Dan

Reputation: 10680

Try to explicitly refer to the column to which you want to compare, by using the table name as well:

UPDATE TABLE1
SET Flag = 'Y',
    MODIFICATION_DATE = GETDATE()
WHERE 
    UNIQUE_ID IN (SELECT TMP_TABLE.UNIQUE_ID FROM TMP_TABLE)

Confusion arises because there's a column named UNIQUE_ID on both tables, so by fully-qualifying it with the table name in the subselect, you can be 100% sure that you are referring to the right column.

Upvotes: 1

A ツ
A ツ

Reputation: 1267

I assume you had a typo in your query. Your tables look like that?

create table1 
( Unique_ID int  
, Flag char(1)
, ...
);

create tmp_table
( [Unique ID] int primary key
, ...
);

in that case your query is:

Update table1 t1 set
Flag = ‘Y’
Where t1.Unique_ID in (select t1.Unique_ID from tmp_table)

which is true except your id is null. you just compare the Unique_ID from table1 with itself.

Upvotes: 1

Related Questions