assigning values by select within a procedure

 
Set @nextID = Select (currentNumber + 1) From IDGenerator Where tableName = @tableName

Create Function GetNextID (@tableName as varchar(40) = null)

Returns Integer
As
Begin
	
	Declare @nextID integer

	Set @tableName = rtrim(ltrim(@tableName))

	if (@tableName is null or @tableName = '')
		raiserror('isValidStatus - You must pass in a player status',16,1)
	
	Begin Transaction
		--obtain the (MAX id  +1) of the specified table
		Select @nextID = (currentNumber + 1) From IDGenerator Where tableName = @tableName

		--insert the new
		Update IDGenerator Set currentNumber = @nextID

		--catch error
		if (@@error <> 0)
		begin
			Rollback Transaction
			raiserror('GetNextID - Error upon updating the ID table',16,1)
		end
		else
		Begin
			Commit Transaction
			return @nextID
		End
End

create function dbo.fGreatest(@num1 numeric, @num2 numeric)
returns numeric
as
begin
    if @num1 >= @num2
        return @num1
    else
        return @num2
end
go

select dbo.fGreatest( 3, 4 )
go

create procedure dbo.sprocGreatest( @num1 numeric, @num2 numeric )
as
begin
    if @num1 >= @num2
         select @num1
    else
         select @num2
end
go

exec dbo.fGreatest 3, 4

create procedure sprocInsertFooBar( @firstName varchar(10), @lastName varchar(10) )
as
begin
    declare @nid int
    exec dbo.nextval( 'FooBar', @nid output )
    
    insert into dbo.FooBar ( id, firstName, lastName )
        values( @nid, @firstName, @lastName )

    select @nid
end

Create Procedure GetNextID2 (@tableName as varchar(40) = null, @nextID int output)
As
Begin
		--insert the new
		Update IDGenerator
		Set @nextID = currentNumber = currentNumber + 1
		Where tableName = @tableName

End
Return @nextID
-----------------------------
declare @nextID integer
Exec GetNextID2 'hockeyPlayers', @nextID
select @nextID

Create Procedure GetNextID2 (@tableName as varchar(40) = null, @nextID int output)
As
Begin
		--insert the new
		Update IDGenerator
		Set @nextID = currentNumber = currentNumber + 1
		Where tableName = @tableName

End
Return @nextID
-----------------------------
declare @nextID integer
Exec GetNextID2 'hockeyPlayers', @nextID output
select @nextID

CREATE TABLE dbo.SEQUENCES
(
	/* Simulates and Oracle sequence.
	   This is the prefered method to generate unique integer keys
	   compared to identity.  Identity fields cannot be updated and can
	   cause serious issues with replication and data movement.
	   
	   This table will be pinned to memory when the db starts
	   
	   See Stored Procedures
		dbo.CREATE_SEQUENCE
		dbo.NEXTVAL
	*/
	SEQ	varchar(100)	not null	primary key,
	SEQ_SEED	int	not null	default 1,
	SEQ_INCREMENT	int	not null	default 1,
	SEQ_ID	int	not null	default 1
)
GO

CREATE Procedure dbo.CREATE_SEQUENCE
	@sequence varchar(100),
    @seed INT,
    @increment INT
AS
INSERT INTO dbo.SEQUENCES ( SEQ, SEQ_SEED, SEQ_INCREMENT, SEQ_ID )
	VALUES( @sequence, @seed, @increment, @seed - @increment )

GO

CREATE Procedure dbo.DROP_SEQUENCE
	@sequence varchar(100)
AS
DELETE FROM dbo.SEQUENCES
	WHERE SEQ = @sequence

GO

CREATE Procedure dbo.NEXTVAL
	@sequence varchar(100),
    @sequence_id INT OUTPUT
AS
SET @sequence_id = null
              
UPDATE dbo.SEQUENCES
SET    @sequence_id = SEQ_ID = SEQ_ID + SEQ_INCREMENT
WHERE  SEQ = @sequence

RETURN @sequence_id

GO

exec dbo.CREATE_SEQUENCE 'MyTest', 1, 1
begin
declare @nid int
exec dbo.NEXTVAL 'MyTest', @nid output
select @nid
end