Stored Procedure en MySQL

Ejemplo de como crear un Stored Procedure en MySQL

DROP PROCEDURE IF EXISTS trafico.datos_calculo_circuitos_corresp;

delimiter '/';
CREATE PROCEDURE trafico.datos_calculo_circuitos_corresp(valor varchar(20))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE val_corresponsal VARCHAR(6);
DECLARE val_minutos INT;

DECLARE cur1 CURSOR FOR SELECT b.corresponsal, sum(a.minutos) AS Minutos FROM trafico.rutas b INNER JOIN trafico.trafico a ON a.ruta = b.ruta WHERE substring(a.fecha,1,4) = valor AND b.habilitada = TRUE GROUP BY b.corresponsal;

DROP TABLE IF EXISTS tmp_set;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_set (corresponsal varchar(6), minutos int);

OPEN cur1;
IF (Select FOUND_ROWS()>0) THEN
dependency_loop: LOOP
FETCH cur1 INTO val_corresponsal, val_minutos;
IF NOT done THEN
INSERT INTO tmp_set (corresponsal, minutos) values (val_corresponsal, val_minutos);
ELSE
LEAVE dependency_loop;
END IF;
END LOOP dependency_loop;
END IF;
CLOSE cur1;

SELECT * FROM tmp_set;
END/
delimiter ';'/

Para invocar al procedimiento hacer:
call trafico.datos_calculo_circuitos_corresp('algo');


Nota: Para poder crear el stored procedure/función es necesario redefinir el delimitador de fin de linea que tiene por defecto MySQL para evitar que corte la definición por la mitad. Por eso se utiliza el comando delimeter y se termina el script asignando nuevamente el caracter de fin de linea abitual.

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

Generar Inserts automaticamente en SQL Server 2005

Para obtener los inserts de cualquier tabla que se tenga en una base de SQL Server 2005 se puede usar un stored procedure que se encuentra instalado en la base Master dentro de System Databases. El stored procedure se llama "sp_generate_inserts" y se utiliza de la siguiente manera

exec sp_generate_inserts 'nombre_de_tabla'

y la salida van a ser los inserts correspondientes para usarlos en donde se desee. Muy util si la tabla posee muchos campos o si la cantidad de registros supera un tamaño manejable para hacerlo a mano.

Ejemplo de como crear un Stored Procedure en SQL Server 2005

Ejemplo de como crear un Stored Procedure en SQL Server 2005:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER|CREATE PROCEDURE [dbo].[NombreStoredProcedure] (
@param1 varchar(50) = NULL,
@param2 int = NULL,
....

) WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @where_string varchar(500)
DECLARE @order_string varchar(100)
DECLARE @join_string varchar(500)

SET @where_string = '';
SET @order_string = '';
SET @join_string = '';


IF (@filtervalCaseNum IS NOT NULL OR @filtervalCaseNum != '' )
BEGIN
SET @where_string = ' ccaseno LIKE '''+@filtervalCaseNum+'%'' AND ';
END
IF (@filtervalJudicialReview IS NOT NULL OR @filtervalJudicialReview != '' )
BEGIN
SET @where_string = @where_string + ' CONVERT(varchar(12),CaseProgram.dt_jreview,101) LIKE '''+@filtervalJudicialReview+'%'' AND ';
END
IF (@filtervalProgType IS NOT NULL OR @filtervalProgType != '' )
BEGIN
SET @where_string = @where_string + ' Program.description LIKE '''+@filtervalProgType+'%'' AND '
SET @join_string = @join_string + ' INNER JOIN Program ON CaseProgram.program_id=Program.program_id ';

END

DECLARE @str_sql varchar(3000)

SET @str_sql = '
SELECT count(CaseProgram.caseprogram_id)
FROM CaseProgram LEFT OUTER JOIN [Case] ON CaseProgram.case_id=[Case].case_id
LEFT OUTER JOIN Client on Client.client_id=[Case].client_id
'+ @join_string +'
WHERE '+ @where_string+' 1=1 '


PRINT @str_sql ;
EXEC (@str_sql)
END