SQL UPDATE , IF based on variable passed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL UPDATE , IF based on variable passed

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-11 : 06:44:24

I am trying to set [StatusCompletedDate] column to current date and time if passed variable @Status
is set to "Completed". If @Status variable is NOT set to "Completed" the SQL update code should execute as depicted below.
There should be also a check if [StatusCompletedDate] was already set previously in a table. If date was already there [StatusCompletedDate] update
would not happen but the code below would still execute updating other columns. Any ideas on how to do this in most clean way. Any help is deeply apreciated.

UPDATE [KPITbl] SET [Lead] = @Lead, [WRM] = @WRM, [PTDB] = @PTDB, [PAR] = @PAR, [PM] = @PM,
[RequestingLOB] = @RequestingLOB, [LOB] = @LOB, [StartLocation] = @StartLocation,
[FinishLocation] = @FinishLocation, [Description] = @Description, [ProjectType] = @ProjectType,
[ServerName] = @ServerName, [ServerType] = @ServerType, [DCOorSTANDALONE]=@DCOorSTANDALONE,
[Responsible] = @Responsible, [Status] = @Status, [RAG] = @RAG, [StartDates] = @StartDates,
[EndDates] = @EndDates, [TreeOrDomainImpacted] = @TreeOrDomainImpacted,
[NumOfSites] = @NumOfSites, [NumOfUsers] = @NumOfUsers, [GBdatamoved] = @GBdatamoved,
[GBdatadeleted] = @GBdatadeleted, [NumOfSrvrsAdded] = @NumOfSrvrsAdded,
[NumOfSrvrsDecommed] = @NumOfSrvrsDecommed, [NumOfAppsDeployed] = @NumOfAppsDeployed,
[EUTEngineeringConsult] = @EUTEngineeringConsult, [Comments] = @Comments,
[TimeSpend] = @TimeSpend, [Complexity] = @Complexity, [ECM]=@ECM,
[LastUpdated]=(getdate() ) WHERE [TaskID] = @TaskID

CREATE TABLE [dbo].[KPITbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDates] [smalldatetime] NULL,
[EndDates] [smalldatetime] NULL,
[TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSites] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfUsers] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GBdatamoved] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GBdatadeleted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSrvrsAdded] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSrvrsDecommed] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfAppsDeployed] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL,
[DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCompletedDate] [smalldatetime] NULL,
[StatusCancelledDate] [smalldatetime] NULL,
CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

kishore_pen
Starting Member

49 Posts

Posted - 2008-01-11 : 06:53:17

you can use Case statement in your update statement, for more information see SQL BOL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 06:59:03

Hope this is what you need :-

UPDATE [KPITbl] SET [Lead] = @Lead, [WRM] = @WRM, [PTDB] = @PTDB, [PAR] = @PAR, [PM] = @PM,
[RequestingLOB] = @RequestingLOB, [LOB] = @LOB, [StartLocation] = @StartLocation,
[FinishLocation] = @FinishLocation, [Description] = @Description, [ProjectType] = @ProjectType,
[ServerName] = @ServerName, [ServerType] = @ServerType, [DCOorSTANDALONE]=@DCOorSTANDALONE,
[Responsible] = @Responsible, [Status] = @Status, [RAG] = @RAG, [StartDates] = @StartDates,
[EndDates] = @EndDates, [TreeOrDomainImpacted] = @TreeOrDomainImpacted,
[NumOfSites] = @NumOfSites, [NumOfUsers] = @NumOfUsers, [GBdatamoved] = @GBdatamoved,
[GBdatadeleted] = @GBdatadeleted, [NumOfSrvrsAdded] = @NumOfSrvrsAdded,
[NumOfSrvrsDecommed] = @NumOfSrvrsDecommed, [NumOfAppsDeployed] = @NumOfAppsDeployed,
[EUTEngineeringConsult] = @EUTEngineeringConsult, [Comments] = @Comments,
[TimeSpend] = @TimeSpend, [Complexity] = @Complexity, [ECM]=@ECM,
[LastUpdated]=(getdate() ),
[StatusCompletedDate]=CASE
WHEN @Status='Completed' THEN ISNULL([StatusCompletedDate],GETDATE()) END WHERE [TaskID] = @TaskID

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-11 : 10:07:33

visakh16:
Thank you so much! Works like a charm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources