SQL Server Performance Forum – Threads Archive

stored procedure not return value

hi all
can some one help me on this
CREATE PROCEDURE [dbo].[mMaxId]
AS
set nocount on
declare @Id bigint
declare @mID bigint
select @id = max(TransId) from tbltransaction if (@id is null)
begin
set @mid = 1
end if ( @id is not null)
begin
set @mid = @id +1
end
return
GO

---

@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)
AS
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
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

]]>