stored procedure not return value | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

stored procedure not return value

hi all
can some one help me on this
set nocount on
declare @Id bigint
declare @mID bigint
select @id = max(TransId) from tbltransaction if (@id is null)
set @mid = 1
end if ( @id is not null)
set @mid = @id +1

@mid is only a local variable, so there is no input/output mechanism. Move the @mid declaration to the procedure parameters like this: CREATE PROCEDURE [dbo].[mMaxId]
(@mID bigint OUTPUT)
set nocount on
declare @Id bigint

thanks Adriaan but when i exec the procedure like this
declare @ff bigint
exec mmaxid @ff
print @ff
on the sql query analyzer
nothing come to me
You must include the OUTPUT keyword with the call too: declare @ff bigint
exec mmaxid @ff OUTPUT
print @ff

<s>By the way, your procedure will return 2 if MAX(TransId) is actually NULL – you’re probably expecting it to return 1 in that case.</s><br />EDIT: My fault, it works just fine (unless you were expecting it to return 2, of course[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]).
thanks Adriaan
You don’t need IF constructs inside your stored procedure.
SELECT @mid = ISNULL(MAX(TransId)+1,1) FROM tbltransaction WITH (NOLOCK) should also do the trcik just fine. —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V.