Learn to Output new row id

Question

User-1641868886 posted

I have a stored procedure for an "AddOrEdit" and to obtain an output of a new row id to be used in the Json result of my MVC controller action. So far, I am ALWAYS getting the new id = 0 at the controller breakpoint for the id parameter. Here is the stored proc.:

  PROCEDURE [dbo].[spVendors_AddOrEdit]
	
	@VendorName nvarchar(100),
	@Address nvarchar(100),
	@City nvarchar(100),
	@State char(10),
	@ZipCode nvarchar(20),
	@Vend_Id int Output
	
	
	As

	IF @Vend_Id = 0
	INSERT INTO Vendors(VendorName, Address, City, State, ZipCode, MetrosId)
	OUTPUT INSERTED.Vend_Id	
	VALUES (@VendorName, @Address, @City, @State, @ZipCode,
	(SELECT MetroZips.MetrosId FROM MetroZips WHERE MetroZips.ZipCode=@ZipCode))
	
	

	Else
	UPDATE Vendors
	SET
	VendorName=@VendorName,
	Address=@Address,
	City=@City,
	State=@State,
	ZipCode=@ZipCode
	WHERE Vend_Id = @Vend_Id

I am not sure if I have this correct. The two highlighted entries may be redundant, or wrong syntax. If I try to include "INSERT SCOPE_IDENTITY()" at the end of the insert/new section, I get a syntax error at the "Else" line for the "Update" section.

Finally, here is the controller parameter where I'm attempting to capture the output (via Dapper ORM):

param.Add("@Vend_Id", Vend.Vend_Id, direction:ParameterDirection.Output);

Thanks for looking and any suggestions.

RC

Answer 1

User475983607 posted

IMHO, Add/Edit is a poor design.  I understand the idea but it requires conditional logic which adds to the complexity.  It's also what tripping you up.

Anyway, there are several issues with your code.   Vend_Id as defined as an OUTPUT but you are using it as an INPUT.  The code writes to an INSERT table but never use the table again.  There's a questionable SELECT that can certainly produce multiple records and an error if run.

I recommend crafting two stored procedures.  One for INSERT and another for UPDATE.  This will simplify your code.  The UPDATE should return the records affected and the INSERT should return the Identity.  Very simple and very clean.

Answer 2

User-1641868886 posted

Thanks for reviewing and the info.

I agree on the "AddOrEdit" so I am planning to replace those and segregate the operations.  For now, I changed the query a little:

IF @Vend_Id = 0
	INSERT INTO Vendors(VendorName, Address, City, State, ZipCode, MetrosId)
	OUTPUT INSERTED.[Vend_Id]	
	VALUES (@VendorName, @Address, @City, @State, @ZipCode,
	(SELECT MetroZips.MetrosId FROM MetroZips WHERE MetroZips.ZipCode=@ZipCode))

...and did some testing and I am obviously generating an output "Vend_Id" in the storedProc. So it seems like my problem must be in this declaration in the MVC controller action:

param.Add("@Vend_Id", Vend.Vend_Id, direction:ParameterDirection.Output);

...which I am trying to give me a retrievable "Vend_Id" in this return at the end:

            return Json(new { success = true, message = "Vendor Added Successfully.", option = Vend.VendorName, optionvalue = Vend.Vend_Id }, JsonRequestBehavior.AllowGet);

I have a knowledge gap...have not done this before. This is all so that a jquery function can 1) create a new "Vendor" using a popup, and 2) append the dropdownlist of "Vendors" to include the new created "Vendor". I need the "Vend_Id" because the form that includes the dropdownlist needs to capture "Vend_Id, not "VendorName". 

Please advise if I am correct that my issue is now the param.Add and if there is an example I can review of the proper syntax for that.

Thanks again,

RC

Answer 3

User77042963 posted

Use two stored procedure: one for insert, one for update. It will simplify your code.

Answer 4

User475983607 posted

This construct...

OUTPUT INSERTED.[Vend_Id]

Writes to the INSERTED table.  You never use the table again in the code.  https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

There's no reason to have the IF since you separated the INSERT and UPDATE procedures.

The following query will throw an exception of more than one record is returned which seems likely.  On the other hand, if there is a one-to-one relationship between MetrosId and zipcode then you do not need both. This seems like a deign issue that should be addressed either way.

IF @Vend_Id = 0
	INSERT INTO Vendors(VendorName, Address, City, State, ZipCode, MetrosId)
	OUTPUT INSERTED.[Vend_Id]	
	VALUES (@VendorName, @Address, @City, @State, @ZipCode,
	(SELECT MetroZips.MetrosId FROM MetroZips WHERE MetroZips.ZipCode=@ZipCode))

Just simplify your code.  you're making this this logic far to complicated.

Example that uses the OUTPUT clause.  Rather than an OUTPUT parameter the Identity is returned.  you can use an OUTPUT parameter if you like. https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15

CREATE PROCEDURE dbo.InsertVendor (
	@VendorName nvarchar(100),
	@Address nvarchar(100),
	@City nvarchar(100),
	@State char(10),
	@ZipCode nvarchar(20)
)
AS 
BEGIN

	DECLARE @VendorTable TABLE  
	(  
		VendorId INT 
	); 

	DECLARE @VendorId INT;
	DECLARE @MetrosId INT;
	SELECT TOP 1 @MetrosId = MetrosId FROM MetroZips WHERE ZipCode=@ZipCode;

	INSERT INTO [dbo].[Vendor]
	OUTPUT INSERTED.VendorId INTO @VendorTable 
	VALUES(@VendorName, @Address, @City, @State, @ZipCode, @MetrosId);

	SELECT @VendorId = VendorId FROM @VendorTable;

	RETURN @VendorId;
END
GO

Answer 5

User-474980206 posted

you misunderstand the output clause. its returns a result set (just like you did a select). the @Vend_Id is and output parameter (and unrelated to the output clause, which must be read after reading the result set. But your sql code onver set the variable, so it will always be the passed value  (0).

to set the output 

	INSERT INTO Vendors(VendorName, Address, City, State, ZipCode, MetrosId)
	VALUES (@VendorName, @Address, @City, @State, @ZipCode,
	         (SELECT MetroZips.MetrosId 
                  FROM MetroZips 
                  WHERE MetroZips.ZipCode=@ZipCode));

        set @Vend_ID = scope_identity();
	

to read the output clause:

var command = new SqlCommand(queryString, connection);
var vendorId = "";

using(var reader = command.ExecuteReader())
{
    // read output clause output
    while (reader.Read())
    {
        vendorId = reader[0].ToString(); 
    }
}

Answer 6

User-1641868886 posted

Thanks to all for reviewing and suggestions. I took mgebhard and limno suggestions and separated the insert and update. However, I consistently got back "0" for "Vend_Id" in my controller until I used bruce suggested "SCOPE_IDENTITY()" in the query. Now I have this return stmt in the controller:

return Json(new { success = true, message = "Vendor Added Successfully.", option = Vend.VendorName, optionvalue = Vend_Id }, JsonRequestBehavior.AllowGet);

...so when I hover over Vend_Id above, I now get the correct newly inserted "Vend_Id". You can see how I am trying to save it for use in the "success" function of the script, and here is the script part that tries to use it:

success: function (data) {                   
         if (data.success) {
             $("#Vend_Id").append($("<option selected=true>" + data.option + "</option>")
             .val($("#Vend_Id").val()));

...so I am having the final issue that when I return to the dropdownlist, I have the new "VendorName" but apparently not the associated "Vend_Id" . Can anyone suggest where I'm going wrong in getting the "Vend_Id" into the .append command? On debugging, the "data" includes the new id as "optionValue: 155" being the last one I tried. So it is IN the data, but I'm not getting it into the appended option.

Thanks again,

RC

Answer 7

User475983607 posted

You never set the name and value.

$("#Vend_Id").append($("<option selected=true value='" + data.optionvalue + "'>" + data.option + "</option>"));

Answer 8

User-1641868886 posted

Of course I thought I was setting the value with the declaration after the <option> element:

.val($("#Vend_Id").val()));

...but now I can see I wasn't really sending it anything. What you suggested works fine. Thanks again!

RC