Getting Identity Back

[Tina]

The following insert stored procedure was generated by the 2.0 tableadapter
wizard (I have applied the latest service pack to vs2005)
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'NewInsertCommand' AND
user_name(uid) = 'dbo')
DROP PROCEDURE dbo.NewInsertCommand
GO

CREATE PROCEDURE dbo.NewInsertCommand
(
@FranchiseID nvarchar(16),
@PrimaryVendorID int,
@Status nvarchar(15),
@DateEntered datetime,
@Customer nvarchar(50)
)
AS
SET NOCOUNT OFF;
INSERT INTO [Order] ([FranchiseID], [PrimaryVendorID], [Status],
[DateEntered], [Customer]) VALUES (@FranchiseID, @PrimaryVendorID, @Status,
@DateEntered, @Customer);

SELECT FranchiseID, PrimaryVendorID, Status, DateEntered, Customer, OrderID
FROM [Order] WHERE (OrderID = SCOPE_IDENTITY())
GO

If I execute this SP in SQL Server Management Studio it returns zero
USE [mydb]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[NewInsertCommand]
@FranchiseID = N'19930601',
@PrimaryVendorID = 1,
@Status = N'Pending',
@DateEntered = N'1/12/07',
@Customer = N'myCustomer'

SELECT 'Return Value' = @return_value

GO

I think it is supposed to return the fields from the new record. Why
doesn't it? Does this wizard still not generate working stored procedures?

Thanks,
T

 

[Mark Rae]

If I execute this SP in SQL Server Management Studio it returns zero

Do you mean it returns zero records, or you it returns you a value of
zero...?