I'd like to create a stored procedure where it involves multiple inserts depending on some conditions. But unfortunately this could not be compiled or incorrect syntax. Any thoughts on where i need to change?
CREATE PROCEDURE SP1
(SELECT Count(*) FROM Tbl1 WHERE MyID=@MyID) = 0
INSERT INTO MyTable ---This table has an Identity column which is required on the next insert!!!
(Select Col1, Col2, MyID FROM Tbl2 WHERE MyID=@MyID)
INSERT INTO FinalTable
(Id---Identity value created by previous insert statement above!!!
(SELECT b.Id, a.Col31, a.Col32, a.Col33 FROM Tbl3 a INNER JOIN MyTable b ON a.MyID=b.MyID
SELECT COUNT(*) FROM FinalTable