CODEWITHSUNDEEP
sqlsql-serverstored-procedures
No1Lives4Ever
No1Lives4Ever

Reputation: 6883

Save Exec results in variable

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)

Upvotes: 0

Views: 148

Answers (2)

marc_s
marc_s

Reputation: 754478

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.

Upvotes: 1

mehdi lotfi
mehdi lotfi

Reputation: 11571

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

Upvotes: 1

Related Questions