martes, 31 de marzo de 2009

Ejemplo de User Defined Function

Ejemplo de User Function en SQL Server 2005

USE [name_db]
GO
/****** Object: UserDefinedFunction [dbo].[getAccounts] Script Date: 03/31/2009 15:31:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[getAccounts]
(@kchecks varchar(10))
RETURNS varchar(max)
AS
BEGIN
declare @Return varchar(max)
DECLARE @temp table (
[tmp_field] [varchar] (max)
)

set @Return = ''

INSERT INTO @temp SELECT Account.account_id
from advocate_mig_final.dbo.checks
inner join advocate_mig_final.dbo.chckjrnl on chckjrnl.kchecks = checks.kchecks
inner join advocate_mig_final.dbo.journal on journal.kjournal = chckjrnl.kjournal
inner join advocate_db_final.dbo.Account on Account.leg_kaccounts = journal.kaccounts
where journal.djournal > '12/31/1999' and checks.kvictims != '111111'
and checks.kchecks =@kchecks


select @Return = @Return +','+ tmp_field
from @temp

If len(@Return) > 0 Select @Return = right(@Return, len(@Return) - 1)


return @return
end


--select advocate_mig_final.dbo.getAccounts('AFCAYP')

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

Mis fotos en TrekEarth