Stored Procedures: Returning values

[Airslash]

Hello,

I'm currently using stored procedures in my C# application to speed up the process of retrieving and manipulating data in the database. However I was wondering if the following is possible:

I have an assignment which has an ID, Year, Number, Description and Notes. I'm currently using a stored procedure for inserting new records in the database. But now I'm wondering if it is possible that when I insert a Year, Description and Notes in a new row, my stored procedure can automaticly return the ID and the Number.

The ID is a primary key which auto increments on each insert, and the Nr is beeing calculated in the following way:

SELECT ISNULL(MAX(Nr), 0) + 1 FROM Assignments WHERE Year = @Year;

This results that everytime a new year begins, the number counts from zero again while the ID keeps incrementing regardless of year. But it would be nice if the Inserted ID & Number could be automaticly returned.


Its for display stuff in the application to make it eaiser for the users to continue with that data instead of pulling a seperate query to retrieve that specific information.


I hope its clear what I'm trying to achieve

[pankajthebest]

Hi,
If your id is auto increment then you get its value directly using keyword
@@identity or @@scope_identit y

[Airslash]

ALTER PROCEDURE [dbo].[AssignmentNew] 
	-- Add the parameters for the stored procedure here
	@id int output,
	@year int,
	@number int output,
	@company int,
	@description nvarchar(150),
	@people int,
	@status int,
	@startdate datetime,
	@enddate datetime,
	@phuso int,
	@remarks nvarchar(max),
	@offer int,
	@notes nvarchar(max)
AS
DECLARE @statement nvarchar(max)
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Calculate the Number.
	SELECT @number = ISNULL(MAX(Nr),0) + 1 FROM Assignments WHERE Year = @year

    -- Insert statements for procedure here
	SELECT @statement = N'INSERT INTO Assignments(Year,Nr,Company,Description,People,Status,StartDate,EndDate,Phuso,Remarks,Offer,Notes) VALUES (@year,@nr,@company,@description,@people,@status,@startdate,@enddate,@phuso,@remarks,@offer,@notes)'

	-- Execute the statement.
	EXEC sp_executesql @statement,N'@year int,@nr int,@company int,@description nvarchar(150), @people int, @status int, @startdate datetime, @enddate datetime, @phuso int, @remarks nvarchar(max), @offer int, @notes nvarchar(max)',@year,@number,@company,@description,@people,@status,@startdate,@enddate,@phuso,@remarks,@offer,@notes

	-- Configure the Output parameters
	SELECT @id = @@IDENTITY
	
END

I think this should do the trick. Using output parameters to get the values I need. I'm just not really sure about using the calculated output parameter as a parameter in my statement. Hope it works.

[Jim Doherty]

ALTER PROCEDURE [dbo].[AssignmentNew] 
	-- Add the parameters for the stored procedure here
	@id int output,
	@year int,
	@number int output,
	@company int,
	@description nvarchar(150),
	@people int,
	@status int,
	@startdate datetime,
	@enddate datetime,
	@phuso int,
	@remarks nvarchar(max),
	@offer int,
	@notes nvarchar(max)
AS
DECLARE @statement nvarchar(max)
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	-- Calculate the Number.
	SELECT @number = ISNULL(MAX(Nr),0) + 1 FROM Assignments WHERE Year = @year
 
-- Insert statements for procedure here
	SELECT @statement = N'INSERT INTO Assignments(Year,Nr,Company,Description,People,Status,StartDate,EndDate,Phuso,Remarks,Offer,Notes) VALUES (@year,@nr,@company,@description,@people,@status,@startdate,@enddate,@phuso,@remarks,@offer,@notes)'
 
	-- Execute the statement.
	EXEC sp_executesql @statement,N'@year int,@nr int,@company int,@description nvarchar(150), @people int, @status int, @startdate datetime, @enddate datetime, @phuso int, @remarks nvarchar(max), @offer int, @notes nvarchar(max)',@year,@number,@company,@description,@people,@status,@startdate,@enddate,@phuso,@remarks,@offer,@notes
 
	-- Configure the Output parameters
	SELECT @id = @@IDENTITY
 
END

I think this should do the trick. Using output parameters to get the values I need. I'm just not really sure about using the calculated output parameter as a parameter in my statement. Hope it works.

Use the @@SCOPE_IDENTIT Y. It runs within the scope of the procedure in which it is run. @@IDENTITY will return the last identity value irrespective which in a multi user environment 'could' cause you problems with split second grabbing of last values if you get me

Jim :)

[Airslash]

Use the @@SCOPE_IDENTIT Y. It runs within the scope of the procedure in which it is run. @@IDENTITY will return the last identity value irrespective which in a multi user environment 'could' cause you problems with split second grabbing of last values if you get me

Jim :)

ok changed, and thanks for the pointer