how to convert the following udf function into a stored procedure?

Hi Experts,
Can someone help me convert the following function into a stored procedure?

CREATE FUNCTION [dbo].[fnOrientLastNoteID]
(
	@Orientationid	AS	int
)
RETURNS int
AS
BEGIN
	DECLARE @Result AS int 

	select @Result =   MAX(ID) 
FROM         dbo.OrientationNotes
where OrientationID = @OrientationID;
--GROUP BY OrientationID  -- you don't need to group
	RETURN @Result;
END

GO

Open in new window

Thanks
LVL 5
bfuchsAsked:
Who is Participating?
 
Mark ElySenior Coldfusion DeveloperCommented:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_OrientLastNoteID
	
	@OrientationID int
AS
BEGIN
	
	SET NOCOUNT ON;

   select MAX(ID) AS MaxID 
FROM     dbo.OrientationNotes
where OrientationID = @OrientationID;

END
GO

Open in new window

0
 
Scott PletcherSenior DBACommented:
CREATE PROCEDURE [dbo].[procOrientLastNoteID]
(
      @Orientationid      AS      int
)
AS
SET NOCOUNT ON;

SELECT MAX(ID)
FROM      dbo.OrientationNotes
WHERE OrientationID = @OrientationID

GO
0
 
Scott PletcherSenior DBACommented:
DOH, just saw someone had already posted the code.  Btw, the original function can be made slightly more efficient by getting rid of the local variable:

CREATE FUNCTION [dbo].[fnOrientLastNoteID]
(
      @Orientationid      AS      int
)
RETURNS int
AS
BEGIN
RETURN (
    SELECT MAX(ID)
    FROM         dbo.OrientationNotes
    WHERE OrientationID = @OrientationID
)
END
GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
Thanks you guys,
Perhaps you may as well know how can I get that to work in my Access App form?

See following.
http://www.experts-exchange.com/Database/MS_Access/Q_28659574.html#a40735042
0
 
Mark ElySenior Coldfusion DeveloperCommented:
No worries... however just curious why did you choose ScottPletcher answer over mine.. Our answers were identical and I posted first?
0
 
bfuchsAuthor Commented:
Sorry, I meant to assign 400 for your answer
Already requested attention on this matter.
0
 
bfuchsAuthor Commented:
Corrected, Apologize for that..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.