Manual SQL Deployment Script

Question

Manual SQL Deployment Script

SSCarpal Tunnel

Points: 4114

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 2 years, 10 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: 246686 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,

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100942 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) A socialist is someone who will give you the shirt off *someone else's* back.

Jonathan AC Roberts SSCoach Points: 18257 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: 4114 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: 4114 More actions · Answer 5

Tava

SSCarpal Tunnel

Points: 4114

June 16, 2021 at 1:59 pm

#3896985

Thanks all, this has worked