CODEWITHSUNDEEP
sqlt-sql
Wachburn
Wachburn

Reputation: 2939

if construction in sql

I'm not good at t-sql, so I need help.

I have this code I wrote myself, but how write that I want I don't know (and sorry for English)

declare @questionid int
set @questionid = 22

declare @providerid int
set @providerid = 189

select [closed] into #question from [Data].[dbo].[Question] where [questionid] = @questionid  

-- and here, if [closed] is null I must write in the question table current date GETDATE()
UPDATE [Data].[dbo].[Question] SET [closed] = GETDATE() WHERE [questionid] = @questionid and [providerid] = @providerid

-- or, if [closed] is not null I must write there null
UPDATE [Data].[dbo].[Question] SET [closed] = null WHERE [questionid] = @questionid and [providerid] = @providerid

drop table #question

How can I do that?

Upvotes: 0

Views: 149

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176896

Make use of case when you can easily achieve your task..

UPDATE [Data].[dbo].[Question] 
SET [closed] = CASE
          WHEN [closed]  is null THEN GetDate()
          ELSE null
          END
WHERE [questionid] = @questionid 
   and [providerid] = @providerid

Upvotes: 1

Alejandro B.
Alejandro B.

Reputation: 5082

You don't need the temp table for this

declare @questionid int
set @questionid = 22

declare @providerid int
set @providerid = 189

declare @closed datetime
select @closed = [closed] from [Data].[dbo].[Question] where [questionid] = @questionid  

-- and here, if [closed] is null I must write in the question table current date GETDATE()
IF @closed IS NULL
    UPDATE [Data].[dbo].[Question] SET [closed] = GETDATE() WHERE [questionid] = @questionid and [providerid] = @providerid
ELSE
-- or, if [closed] is not null I must write there null
    UPDATE [Data].[dbo].[Question] SET [closed] = null WHERE [questionid] = @questionid and [providerid] = @providerid

I must say, I don't understand the logic you are trying to achieve...

Upvotes: 1

Related Questions