Stored Procedure with an Output Variable for other Insert Queries

Question

I am trying to run a Sql stored procedure from VS2010 that will insert a record into a table and then output the auto-increment, Identity # it assigns to that newly inserted record so I can run two more insert queries using that ID.

This is the stored procedure I have right now and it only runs the first insert query and doesn't seem to be assigning the @CompID parameter.

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[POToSparts]    Script Date: 12/12/2012 06:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@CompID int = NULL Output
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand)
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')
END

And this is how I am running this procedure from within VS2010.

Using connection As New SqlConnection(My.Settings.MTConnectionString)
                                'Dim command As SqlCommand = New SqlCommand()
                                Using command As New SqlCommand("POToSparts", connection)
                                    command.CommandType = CommandType.StoredProcedure
                                    command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
                                    command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
                                    command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
                                    command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
                                    command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
                                    command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
                                    command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

                                    connection.Open()
                                    command.ExecuteNonQuery()
                                End Using
                            End Using

I am completely confused as to why it won't run those other two queries or assign the ID to the @CompID parameter.

Thanks for any help you can provide.

Stacy

Answer 1

Is the first insert completing successfully and inserting the rows or does it return an error? Does this work with the same parameters if you run the procedure in SSMS?

Answer 2

The first insert statement does run successfully from within VS2010.  Pretty new to Sql so I'm not sure how to assign the parameter values in SSMS and run it directly from within there.

Basically it doesn't assign a value to the @CompID and so those last two insert queries just don't run - but it doesn't show an error.

Thanks for your reply.

Stacy

Answer 3

Your procedure looks OK to me. How do you read the value of output parameter? In your code I don't see it.

I also suggest you to take a look at this blog post that provides you with all the code you need

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

After the command.ExecuteNonQuery() I tried adding this line Dim CompID = command.Parameters("@CompID").Value

but it still doesn't get a value.  Why do I need to read that value when it is assigned right before I run the next to insert queries?

I will look at the article and see if that gets me anywhere.

Thanks for your help.

Stacy

P.S.  Ok tried taking out the top part of my procedure - it looks like this

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[POToSparts]    Script Date: 12/12/2012 10:48:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[Test]
(

@CompID int = NULL Output
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES ('Test Component', '3','1');
SET @CompID = SCOPE_IDENTITY()
PRINT @CompID
END

and  then I just executed it from within SSMS and this is what it is returning.

The top of the screen shows this:

USE [MT]
GO

DECLARE    @return_value int,
        @CompID int

EXEC    @return_value = [dbo].[Test]
        @CompID = @CompID OUTPUT

SELECT    @CompID as N'@CompID'

SELECT    'Return Value' = @return_value

GO

And then on the bottom it shows:

@CompID = 3125 (which is right)

and then it shows Return Value = 0

Not sure what is happening here.  I don't understand it.  If you could make sense of this that would be great.

Thanks,

Stacy

Answer 5

It all makes perfect sense. Output parameters and return values are two different things. You return your new ID using OUTPUT parameter. Return value usually is used to indicate success (usually 0) or failure (I use negative codes for that, but you can use anything).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

I'm glad it makes sense to you  :) - but I am still confused.  Why is it returning the Return Value to VS? 

So why don't the other queries run if it is assigning the @CompID?

Thanks,

Stacy

So this is what my query looks like:

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[POToSparts]    Script Date: 12/12/2012 12:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@CompID int = NULL Output
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand);
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')
END

and this is what it looks like when I execute from within SSMS:

USE [MT]
GO

DECLARE	@return_value int,
		@CompID int

EXEC	@return_value = [dbo].[POToSparts]
		@Component = N'Testing!',
		@QtyonHand = 1,
		@EquipmentID = 1,
		@VendorID = 1,
		@PartNum = N'1',
		@Price = 2.99,
		@CompID = @CompID OUTPUT

SELECT	@CompID as N'@CompID'

SELECT	'Return Value' = @return_value

GO

Where does that SELECT 'Return Value" = @return_Value come from? Frankly where does the SELECT @CompID as N'CompID' come from?

Answer 7

Perhaps you need to take a step back and attempt to test your "solution" at a finer level, before you test the entire 2-tier package of logic. 

To answer that last question ("where does SELECT 'Return..."), it came from the tool that you used to "just executed it from within SSMS and this is what it is returning".  That is, it was a script generated (and presumably executed) by SSMS.  This is pretty basic tsql; if you do not understand this simple script then you might want to get some help from whatever resources are available locally.  Those 2 select statements are there to simply show you the values of your output variable and the return value of the stored procedure itself (which is set to zero by default if your procedure does not contain any logic to return a different value) after executing the stored procedure.

Based on your previous statements, it appears that your stored procedure works just fine (though it lacks proper error handling).  So the difficulty you are having is not related to tsql; rather it appears to be your visual studio code.  A little searching finds the following that may answer your original question:

http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/2600f2b1-658e-4ac1-8fba-39244caf57b4

Answer 8

I already have code in VS that is supposed to be getting the value of that output parameter.  However, something isn't right or it isn't working - because it is only getting the Return Value not the @CompID Value.

Thanks for the advise about resources available locally for getting help, but not everyone is able to just go take classes.  Sometimes you just have to teach yourself or ask for help until you get it.  I haven't done a query like that where I run a query that is not returning data so I didn't know what was going on.  Since I have only been working with VS2010 and SQL Server 2008 for 3 months and have learned everything I know so far by tutorials and trial & error and help online - I have an almost built winforms app - I'd say I haven't been doing to bad with this method so far.   If you don't want to help, then don't reply.

My VS code is as follows: (Which shows that I do in fact try to get that output variable.

Using connection As New SqlConnection(My.Settings.MTConnectionString)
                                Using command As New SqlCommand("POToSparts", connection)
                                    command.CommandType = CommandType.StoredProcedure
                                    command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
                                    command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
                                    command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
                                    command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
                                    command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
                                    command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
                                    command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.InputOutput))

                                    connection.Open()
                                    command.ExecuteNonQuery()
                                    Dim zCompID = command.Parameters("@CompID").Value
                                    MsgBox(zCompID)
                                End Using
                            End Using

The Sql Stored Procedure looks like this:

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[POToSparts]    Script Date: 12/12/2012 13:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@CompID int = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand);
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')
END

What I don't understand is - WHY do I need to return that variable to VS2010?  All I need to do is use it in the two insert queries below it in the Stored Procedure.  Can't those two queries see that variable and use it's value in their INSERT INTO queries?  As far as I can tell I don't need to give the variable back to VS.

Thanks.

Answer 9

What I don't understand is - WHY do I need to return that variable to VS2010?  All I need to do is use it in the two insert queries below it in the Stored Procedure.  Can't those two queries see that variable and use it's value in their INSERT INTO queries?  As far as I can tell I don't need to give the variable back to VS.

If you don't use the output in the program you don't need to. It also wouldn't need to be an output parameter if nothing that calls that procedure would receive it. When you ran your procedure in SSMS and it returned @CompID and your return value, did it still not insert into the next two tables?

Answer 10

Basically, you don't have to return that new Id to your client application if you don't want to use it later in the code. A while ago I had ASP.NET application where I did need to return it to be able to edit that newly created person I just added.

I think I have a blog post where I showed some pieces of that logic, it may be helpful to you:

Setting SQLDataSource parameter from the code-behind

and my other blog which shows stored procedure very similar to yours but with error handler implemented:

How to insert information into multiple related tables and return ID using SQLDataSource

In that application I was working on several years ago, I came when the application have been already written by another developer, who used SQLDataSource extensively. I didn't know then that it is not a very best way, but I later converted lots of inline SQL code into stored procedures although still using SQLDataSource for existing pages.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Hello,

You didn't mention what was the output, i.e. what @CompID=??

Before you re-run the query, modify the  insert statements to output the inserted rows.

ALTER PROC [dbo].[POToSparts]
(
	@Component Varchar(150),
	@QtyonHand numeric,
	@EquipmentID Integer,
	@VendorID Integer,
	@PartNum VarChar(150),
	@Price decimal,
	@CompID int = NULL Output
)
AS
BEGIN
SET NOCOUNT ON;

	INSERT INTO sparts (Component, Priority, QtyonHand) 
	OUTPUT INSERTED.*
	VALUES (@Component, '3',@QtyonHand);
	SET @CompID = SCOPE_IDENTITY()

	INSERT INTO compequip (ComponentID, EquipmentID ) 
	OUTPUT INSERTED.*
	VALUES (@CompID, @EquipmentID)

	INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) 
	OUTPUT INSERTED.*
	VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')

END

let us know what you get/see.

--an

Answer 12

>WHY do I need to return that variable to VS2010?  

Your code says to pop up a message box with the newly inserted CompID.

Dim zCompID = command.Parameters("@CompID").Value
MsgBox(zCompID)

Answer 13

>WHY do I need to return that variable to VS2010?  

Your code says to pop up a message box with the newly inserted CompID.

Dim zCompID = command.Parameters("@CompID").Value
MsgBox(zCompID)

Yes the code says that because I was checking what value it was returning to VS2010, and it is not returning the right value.

Answer 14

Hello,

You didn't mention what was the output, i.e. what @CompID=??

Before you re-run the query, modify the  insert statements to output the inserted rows.

ALTER PROC [dbo].[POToSparts]
(
	@Component Varchar(150),
	@QtyonHand numeric,
	@EquipmentID Integer,
	@VendorID Integer,
	@PartNum VarChar(150),
	@Price decimal,
	@CompID int = NULL Output
)
AS
BEGIN
SET NOCOUNT ON;

	INSERT INTO sparts (Component, Priority, QtyonHand) 
	OUTPUT INSERTED.*
	VALUES (@Component, '3',@QtyonHand);
	SET @CompID = SCOPE_IDENTITY()

	INSERT INTO compequip (ComponentID, EquipmentID ) 
	OUTPUT INSERTED.*
	VALUES (@CompID, @EquipmentID)

	INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) 
	OUTPUT INSERTED.*
	VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'Yes')

END

let us know what you get/see.

--an

anphu,

The CompID was 3157 - which is right that would be the next ID for that table.

I finally figured out what the problem was I had changed the one of the fields type in that table but some of the records didn't conform to that datatype so when I would load the form some of the records were loading but other's weren't so I thought the other insert queries weren't working when in fact they were.  Taking your advice I had the query show me the inserted rows, which it did show so I knew the query had worked but why the records weren't showing up was what I had to figure out.  Finally did get it working!  Yeah!

Thank you everyone who gave ideas of how to get this working!  Guess this sort of stuff comes with being a newbie.  Sorry for all of the trouble.  Too bad I wasted two days working on this.

Stacy

Answer 15

Basically, you don't have to return that new Id to your client application if you don't want to use it later in the code. A while ago I had ASP.NET application where I did need to return it to be able to edit that newly created person I just added.

I think I have a blog post where I showed some pieces of that logic, it may be helpful to you:

Setting SQLDataSource parameter from the code-behind

and my other blog which shows stored procedure very similar to yours but with error handler implemented:

How to insert information into multiple related tables and return ID using SQLDataSource

In that application I was working on several years ago, I came when the application have been already written by another developer, who used SQLDataSource extensively. I didn't know then that it is not a very best way, but I later converted lots of inline SQL code into stored procedures although still using SQLDataSource for existing pages.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Naomi,

Thank you for your post - I will search for your blog post and see if that will explain what is happening here. 

Just can't seem to understand if that @CompID is being assigned after the first query why those other insert queries can't just get that value and use it.

Is the way I have the procedure set up ok?  I mean in the beginning of the procedure I pass all of the parameters for all of the insert queries.  The first one uses only the @Component & @QtyonHand.  Then the next query uses the assigned @CompID and @EquipmentID, and so on. It just seems to me that those secondary insert queries aren't seeing those parameters or something. Just a thought!

Thanks for all your help with this.  I really appreciate everyones help.

Stacy

Answer 16

Your procedure looks OK to me. How do you read the value of output parameter? In your code I don't see it.

I also suggest you to take a look at this blog post that provides you with all the code you need

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Naomi,

I still can't get sql to send the code to VS2010 or maybe it's getting VS to read the value from SQL.  I DO in fact need to return that value to VS.  This is the code I'm running

Using connection As New SqlConnection(My.Settings.MTConnectionString)
                                    Using command As New SqlCommand("POToSparts", connection)
                                        command.CommandType = CommandType.StoredProcedure
                                        command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
                                        command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
                                        command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
                                        command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
                                        command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
                                        command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
                                        command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

                                        connection.Open()
                                        command.ExecuteNonQuery()
                                            Dim zCompID As String = command.Parameters("@CompID").Value.ToString
                                            MsgBox(zCompID)
                                    End Using
                                End Using

So as you can see after I run the ExecuteNonQuery() I try to get that value (just like the examples on your blogs you recommended).  But my MsgBox always comes up empty.  I even tried to simplify it by just doing the first query because I know slq has the @CompID set after that query, but it is still empty in VS. 

Any ideas why VS isn't getting that value?

Thanks for all your help!

Stacy

Answer 17

I am not sure why it's not working for you.

Try this change:

command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int))
Command.Parameters("@CompID").Direction = ParameterDirection.Output

Also, remove = NULL in the SP parameter declaration.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 18

I am not sure why it's not working for you.

Try this change:

command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int))
Command.Parameters("@CompID").Direction = ParameterDirection.Output

Also, remove = NULL in the SP parameter declaration.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Nope, still no go.  It seems no matter what I change VS nevers gets that code from SQL.

Answer 19

Have you tried the other way in that article using Execute_Scalar and select @CompID at the end of the procedure?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 20

Have you tried the other way in that article using Execute_Scalar and select @CompID at the end of the procedure?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Yes I tried the Execute_Scalar and my little msgbox I have popping up after it executes says the value is 0 and my @CompID value is 3157.  It seems to be returning the ReportValue and not the @CompID value.  Does that sound right?  How could that be?  @CompID is in there as an OUTPUT.

Stacy

P.S. here is that code as I ran it in Visual Studio 2010

Try
                                Using connection As New SqlConnection(My.Settings.MTConnectionString)
                                    Using command As New SqlCommand("POToSparts", connection)
                                        command.CommandType = CommandType.StoredProcedure
                                        command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
                                        command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
                                        command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
                                        command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
                                        command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
                                        command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
                                        command.Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int, Data.ParameterDirection.Output))

                                        connection.Open()                                
                                        Dim zCompID As Integer
                                        zCompID = command.ExecuteScalar()  
                                        MsgBox(zCompID)
                                    End Using
                                End Using

                            Catch ex As Exception

                            End Try

Answer 21

You need to make your mind - either use OUTPUT parameter or SELECT @NewID. In other words, now instead of using OUTPUT parameter try returning it via SELECT instead as shown in that blog post.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 22

You need to make your mind - either use OUTPUT parameter or SELECT @NewID. In other words, now instead of using OUTPUT parameter try returning it via SELECT instead as shown in that blog post.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

You told me to try execute_Scalar in the example he is still using the Output variable with the execute_scalar.  When I take it out it doesn't work at all.

Answer 23

Have you fixed your stored procedure also? E.g. removed the OUTPUT parameter from parameter declaration and changed it to the local declaration of the variable in the body of the stored procedure?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 24

Have you fixed your stored procedure also? E.g. removed the OUTPUT parameter from parameter declaration and changed it to the local declaration of the variable in the body of the stored procedure?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

I believe so this is what I have right now - it is doing the inserts but the @CompID variable is still showing as 0.

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[POToSpartsOrig]    Script Date: 12/13/2012 11:58:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CompID INT
INSERT INTO sparts (Component, Priority, QtyonHand) VALUES (@Component, '3',@QtyonHand);
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'True')
END

Answer 25

Have you fixed your stored procedure also? E.g. removed the OUTPUT parameter from parameter declaration and changed it to the local declaration of the variable in the body of the stored procedure?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Thought I had it - I do have it returning the @CompID now finally. But now it doesn't do the last two insert queries.  And all I changed in the stored procedure was this /*SET @CompID = SCOPE_IDENTITY()*/ - turned this off
SELECT SCOPE_IDENTITY () - using this now.  Well I'm close but now I need to figure out why my other insert queries aren't working anymore.

When I execute from within SSMS it gives the following errors:

Msg 515, Level 16, State 2, Procedure POToSparts, Line 20
Cannot insert the value NULL into column 'ComponentID', table 'MT.dbo.compequip'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure POToSparts, Line 21
Cannot insert the value NULL into column 'ComponentID', table 'MT.dbo.spartven'; column does not allow nulls. INSERT fails.
The statement has been terminated.

So now I guess my other two queries can't see that @CompID value when I use the SELECT SCOPE_IDENTITY instead of the SET @CompID = SCOPE_IDENTITY.  Can I use both?

Answer 26

I GOT IT!  YEAH!  I had to use both the SET SCOPE_IDENTITY and the SELECT!  Now it does both - all my insert queries and returns my @CompID variable!

Thank you sooooooo much!  Have a great Holiday!

Stacy

Answer 27

Last line of this SP should be

SELECT @CompID

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 28

Happy Channukah to you too! I haven't seen that you figured this out yourself when I replied and I replied a bit late as I was busy helping our tester.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog