Return values of newly inserted row

[rickjamesb]

Lets say you have a stored procedure INSERT a new row into a table. In this very same stored procedure what type of SELECT statement could you use just to display the values in the newly created row and not all the rows in that table?

For Example:

CREATE PROCEDURE up_Insert_Region
AS
INSERT INTO Region (RegionID, RegionDescription)
VALUES (@RegionID, @RegionDescription)

 

[jbenson001]

select @ from Region where Region = @RegionID

 

[jbenson001]

Make that select *

 

[gmmastros]

There's more to the story isn't there?

Where does @RegionId and @RegionDescription come from?
Are they parameters to the stored procedure?
Are there identity columns?
Are there primary keys?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

Where does @RegionId and @RegionDescription come from?
User Inputs from VB

Are they parameters to the stored procedure?
I dont know what this means.

Are there identity columns?
Are there primary keys?
This is just a basic table with two attributes (RegionID & RegionDescription). RegionID = primary key

 

[gmmastros]

In order for the user to enter the values through VB, then they must be parameters to the stored procedure, like so...

CREATE PROCEDURE up_Insert_Region
  [!]@RegionId Integer,
  @RegionDescription VarChar(50)[/!]
AS
INSERT INTO Region (RegionID, RegionDescription)
VALUES (@RegionID, @RegionDescription)

I don't know why you need it, but if you want to return the values you just entered:

CREATE PROCEDURE up_Insert_Region
  @RegionId Integer,
  @RegionDescription VarChar(50)
AS
[!]SET NOCOUNT ON[/!]
INSERT INTO Region (RegionID, RegionDescription)
VALUES (@RegionID, @RegionDescription)

Select @RegionId As RegionId, 
       @RegionDescription As RegionDescription

The, Set NoCount On is important. Without it, you get 2 recordsets, the first saying soemthing like... I record affected, and the 2nd with the data you really want. By setting no count on, the first recordset is suppressed and you only get 1 recordset returned to vb that contains the data you want.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

Thanks, that helps!... It was a bad example. I'm trying to solve more difficult problems, but I'm trying to give very simple examples when i ask for help.

 

[gmmastros]

I suspect that this was still valuable because you learned about parameters and the set nocount on command. Both of these things are important when working with SQL Server.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom