0

I already have a stored procedure GET_ROW that is doing a select, according to input ID:

SELECT TOP 1 * 
FROM MyTable 
WHERE ID = @ID

Now, I want to create another stored procedure that checks if an @ID exists. If it exists, return the existing row. Otherwise, create a new row with the requested @ID and return it.

So, I'm thinking of something like this:

Declare ResRow
begin tran
    ResRow = Exec GET_ROW @ID
    if exists (ResRow)
        return ResRow
    else
        Insert into ...
        return Exec GET_ROW @ID
commit

After executing this code, I want to be sure that only one row with @ID exists in the database (no duplicated row with same ID)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
No1Lives4Ever
  • 6,430
  • 19
  • 77
  • 140
  • @MartinSmith: That's a weird duplicate close? This question is about using the result of a stored procedure in another. The suggested original was about inserting a row if it does not exist. – Andomar Aug 16 '14 at 11:18
  • @Andomar there is no restriction on implementation in this question that the existing stored procedure has to be used in the new stored procedure. The actual requirement is to only insert a row that does not exist. The answers in the duplicate cover this topic well. – Martin Smith Aug 16 '14 at 12:21

2 Answers2

1

You must create table variable in first and then use following query:

Declare @T Table(Col1 int, Col2 int ,...)

Insert Into @T
Exec Get_Row @ID

Select * From @T
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
1

How about this:

CREATE PROCEDURE dbo.InsertOrFetch @ID INT
AS 
BEGIN 
   -- check if it doesn't exist yet
   IF NOT EXISTS (SELECT * FROM dbo.MyTable WHERE ID = @ID)
      INSERT INTO dbo.MyTable(ID)
      VALUES (@ID)

   -- now return the row
   SELECT TOP (1) *
   FROM dbo.MyTable
   WHERE ID = @ID
END

If you first check if the row doesn't exist it, and if so, insert the new data - then the SELECT afterwards will always return the row (pre-existing or newly inserted) to you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459