CODEWITHSUNDEEP
sqlsql-server
DotNetBeginner
DotNetBeginner

Reputation: 439

conditional insert sql

My procedure

 ALTER PROCEDURE [dbo].[spMyProc]
 @uniqueid  uniqueidentifier,
 @y int

DECLARE @ID uniqueidentifier
 SELECT TOP 1 @ID = uniqueid FROM tbl_x WHERE y= @y

INSERT INTO tbl_x
(   
    otherfield
    ,uniqueid   
)
VALUES
(
   @otherfields
   ,if @ID == null then @uniqueid else @ID -- this is what i want to happen how i do not know
)

Now i can do this using if else block like this but i do not want to do it. I want some nice way of coding this

this is what i do not want to do

if(@id IS NULL)
BEGIN
INSERT INTO tbl_x
(   
    otherfield
    ,uniqueid   
)
VALUES
(
   @otherfields
   ,@uniqueid 
)
END
ELSE
BEGIN
INSERT INTO tbl_x
(   
    otherfield
    ,uniqueid   
)
VALUES
(
   @otherfields
   ,@id 
)
END

is there any suggestion

Upvotes: 0

Views: 66

Answers (3)

eavom
eavom

Reputation: 1097

you can use CASE while inserting values

INSERT INTO tbl_x
(   
    otherfield
    ,uniqueid   
)
VALUES
(
   @otherfields
   ,CASE WHEN @ID IS NULL THEN @uniqueid ELSE @ID END
)

or you can also use ISNULL function.

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6771

You can put your IF into the SELECT where you assign the value:

ALTER PROCEDURE [dbo].[spMyProc]
 @uniqueid  uniqueidentifier,
 @y int

DECLARE @ID uniqueidentifier
 SELECT TOP 1 @ID = ISNULL(uniqueid,@uniqueid) FROM tbl_x WHERE y= @y

INSERT INTO tbl_x
(   
    otherfield
    ,uniqueid   
)
VALUES
(
   @otherfields
   ,@ID
)

Upvotes: 0

radar
radar

Reputation: 13425

you can use insert into select like this

INSERT INTO tbl_x
(   
    otherfield
    ,uniqueid   
)
SELECT 
  @otherfields,
  ISNULL(@ID , @uniqueid)

Upvotes: 4

Related Questions