Manual SQL Deployment Script

Question

Manual SQL Deployment Script

SSCarpal Tunnel

Points: 4122

June 15, 2021 at 11:57 am

I'm writing a manual SQL deployment script that is re-runnable. Everything works however one use case has now come up & has got me stumped.

Scenario 1 - This works every time

In this scenario, when I run it the 1st time it works as the column has not been dropped yet

-- update values first
IF EXISTS(
SELECT* 
FROMsys.columns 
    WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,'_','.'),2)
FROM [tbl_Reference]
END


GO

-- drop column now that values are updated.
IF EXISTS(
SELECT* 
FROMsys.columns 
    WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
ALTER TABLE [dbo].[tbl_Reference]
DROP COLUMN ReferenceName;
END

Scenario 2 - throws error "Invalid column" every time

In this scenario, ReferenceName no longer exists.

IF EXISTS(
SELECT* 
FROMsys.columns 
    WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,'_','.'),2)
FROM [tbl_Reference]
END

I've tried everything I can to think of a way not perform the update but I can't get passed it.

Any help/ideas would be appreciated.

This topic was modified 3 years, 5 months ago by Tava.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

I suspect that the problem does not occur when the update is performed, instead, the column does not exist and therefore the error occurs at parse time, just before execution.

There are probably other solutions, but one way you could avoid this is by using dynamic SQL, just for the UPDATE statement,

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

IF EXISTS(
SELECT* 
FROMsys.columns 
    WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
    EXEC ('UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,''_'',''.''),2)
FROM dbo.[tbl_Reference]')
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".

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

Slightly more concise way to test if a column is present:

IF COL_LENGTH(N'[dbo].[tbl_Reference]', N'ReferenceName') IS NOT NULL BEGIN

    PRINT 'UPDATING [dbo].[tbl_Reference] SET ClientReferenceNumber '
    EXEC ('UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,''_'',''.''),2)
FROM dbo.[tbl_Reference]')

END

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 4

Thanks all, didn't think of these ways. I'll update these later today and let you know how it goes.

Thanks again

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 5

Tava

SSCarpal Tunnel

Points: 4122

June 16, 2021 at 1:59 pm

#3896985

Thanks all, this has worked