T - SQL statement IF EXIST SELECT and INSERT

Question

How can I make this possible..really need advice? I want to get the id where my condition is met, then used it in my queries.

   IF EXISTS (Select sn_id as snid FROM device.sn WHERE dname_id = 62 and sn_value = '123415')
    BEGIN

    SELECT MAX(id) AS maxid FROM device.list 

    INSERT INTO parts (sn_id,device_id) VALUES (snid, maxid)

    END
    ELSE
    BEGIN
      PRINT 'id does not exist'
    return 
    END

Solution 1

You can use variables to store the results from the two queries and then use those values in your INSERT statement.

If you're using Microsoft SQL Server then the following may work (but there may be superficial syntax errors as it hasn't been tested). Note that I've assumed the type of your columns is int.

DECLARE @snid int
SET @snid = NULL
Select @snid = sn_id FROM device.sn WHERE dname_id = 62 and sn_value = '123415'

IF @snid IS NULL
BEGIN
  PRINT 'id does not exist'
END
ELSE
BEGIN
  DECLARE @maxid int
  SELECT @maxid = MAX(id) AS maxid FROM device.list 
  INSERT INTO parts (sn_id,device_id) VALUES (@snid, @maxid)
END

Solution 2

In SQLServer. This script at first insert records and after checks count of the inserted rows

  INSERT INTO parts (sn_id, device_id)    
  SELECT sn_id, (SELECT MAX(id) FROM device.list)
  FROM device.sn 
  WHERE dname_id = 62 and sn_value = '123415'  

  IF @@ROWCOUNT = 0 PRINT 'id does not exist'