conditional insert sql

Question

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

radar · Accepted Answer · 2014-12-15 21:25:46Z

4

you can use insert into select like this

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

answered Dec 15, 2014 at 21:25

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hogan Over a year ago

you can combine the two select statements into this to have one statement. with this you need a table lock to be sure there are no deadlocks or other problems. With one statement (by combining) the server will take care of it.

DotNetBeginner Over a year ago

Cool it is working fine. Thanks. I did discover this works as well CASE WHEN .. = null THEN .. else .. END. I am adding dots coz comments does not let me enter @

Dave.Gugg · Accepted Answer · 2014-12-15 21:27:53Z

0

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
)

answered Dec 15, 2014 at 21:27

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Comments

eavom · Accepted Answer · 2014-12-16 03:46:52Z

0

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.

answered Dec 16, 2014 at 3:46

eavom

1,1051 gold badge9 silver badges27 bronze badges

Collectives™ on Stack Overflow

conditional insert sql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related