CODEWITHSUNDEEP
c#asp.netsql-server-2008stored-procedures
Mahdi_Nine
Mahdi_Nine

Reputation: 14751

insert into a table and return a value with procedure

I want to create a procedure that inserts into a table and returns it's primary key. The primary key of table is uniqueidentyfire and it's guid is true

This is the stored procedure:

ALTER PROCEDURE [dbo].[pr_Tbl_Erae_Insert]
    @sfk_Dars varchar(20),
    @sfk_Ostad varchar(20),
    @byfk_Gerayesh tinyint,
    @ifk_term int,
    @guidErae_ID uniqueidentifier,
    @byGroup_Number tinyint,

AS
-- INSERT a new row in the table.
INSERT [dbo].[Tbl_Erae]
(
    [fk_Dars],
    [fk_Ostad],
    [fk_Gerayesh],
    [fk_term],
    [Erae_ID],
    [Group_Number]
)
VALUES
(
    @sfk_Dars,
    @sfk_Ostad,
    @byfk_Gerayesh,
    @ifk_term,
    ISNULL(@guidErae_ID, (newid())),
    @byGroup_Number
)

I need to return Erae_Id when it inserted into table how can I do that?

Can I use from output variables? How?

Upvotes: 0

Views: 209

Answers (3)

Lochan Rao Pawar
Lochan Rao Pawar

Reputation: 23

at the end of your stored procedure just add one more line like this

return select Erae_Id WHERE [fk_Dars] = @sfk_Dars
    AND
    [fk_Ostad] = @sfk_Ostad
    AND
    [fk_Gerayesh] = @byfk_Gerayesh
    AND
    [fk_term] = @ifk_term
    AND
    [Group_Number] = @byGroup_Number

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41539

If you need to return the value in the @guidErae_ID parameter that you've included, then you need to mark the parameter for output, and then set it in the proc:

ALTER PROCEDURE [dbo].[pr_Tbl_Erae_Insert]
    @sfk_Dars varchar(20),
    @sfk_Ostad varchar(20),
    @byfk_Gerayesh tinyint,
    @ifk_term int,
    @guidErae_ID uniqueidentifier output,
    @byGroup_Number tinyint,

AS

If @guidErae_ID is null set @guidErae_ID = newid()

-- INSERT a new row in the table.
INSERT [dbo].[Tbl_Erae]
(
    [fk_Dars],
    [fk_Ostad],
    [fk_Gerayesh],
    [fk_term],
    [Erae_ID],
    [Group_Number]
)
VALUES
(
    @sfk_Dars,
    @sfk_Ostad,
    @byfk_Gerayesh,
    @ifk_term,
    @guidErae_ID,
    @byGroup_Number
)

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062650

There is an OUTPUT clause available, depending on your version; however, in this case it is probably easier (since you only have one row) to handle this separately:

IF @guidErae_ID IS NULL SET @guidErae_ID = NEWID()

then use @guidErae_ID "as is" in the INSERT (no ISNULL), and then either end with:

SELECT @guidErae_ID

and use var result = (Guid)command.ExecuteScalar(), or mark @guidErae_ID as OUTPUT in the parameter definition, and query it after calling command.ExecuteNonQuery().

Upvotes: 2

Related Questions