Stored Procedure como función con valor de retorno

Un ejemplo de como hacer que un Stored Procedure retorne un valor como una funcion



USE [name_db]
GO
/****** Object: StoredProcedure [dbo].[GetLastLocationRecipt] Script Date: 03/31/2009 11:03:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: March 18th, 2009
-- Description: ....
-- =============================================
ALTER PROCEDURE [dbo].[GetLastLocationRecipt] (
@location_id int
) WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @location_str varchar(100)
SET @location_str = 'last_receipt_'+cast(@location_id as varchar);

DECLARE @cant int;
DECLARE @new_cant varchar(100);

-- Begin transaction
BEGIN TRANSACTION

SELECT @cant = count(*)
FROM SysPreferences
WHERE preference = @location_str

IF (@cant > 0) -- Exist a previous recipt for the location
BEGIN
-- obtain the last value
SELECT @new_cant = SysPreferences.value
FROM SysPreferences
WHERE preference = @location_str

SET @new_cant = cast( (cast(@new_cant as int)+1) as varchar);
-- update the last value
UPDATE SysPreferences SET SysPreferences.value = @new_cant WHERE preference = @location_str;
END
ELSE
BEGIN
SET @new_cant = cast( '1' as varchar);
INSERT INTO SysPreferences ([preference], [value]) values ('last_receipt_'+cast(@location_id as varchar), @new_cant);
END

-- End transaction
COMMIT TRAN

SELECT @new_cant
END