T-SQL IF EXIST SELECT and INSERT statement

Question

How can I make this possible ... need advice? I want to get the identifier where my condition is satisfied, and then use 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 

Answer 1

You can use variables to store the results from two queries, and then use these values ​​in an INSERT .

If you are using Microsoft SQL Server, then the following may work (but there may be superficial syntax errors since they have not been tested). Note that I assumed that 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 

Answer 2

In SQLServer. This script first inserts records and after checking the number of rows inserted

  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' 

Answer 3

 Declare @snid int=null Declare @maxid int=1 -- if no value exists in device.list table set @snid = (select sn_id from device.sn WHERE dname_id = 62 and sn_value = '123415') set @maxid = (select MAX(id) AS maxid FROM device.list) if @snid is not null Begin insert into parts(sn_id,device_id) values(@snid,@maxid) End else Begin print 'ID does not exist' End