Crear funciones almacenadas en SQL SERVER

[[sitrico]]

Buenas.

Tengo que dar soporte a una aplicación comercial que usa SQL Server y me seria muy util poder incorporar algunas funciones adicionales. me explico:

Tengo un campo CHAR(70) que almacena varios valores separados por ";": (esto resultado de una adaptación especial solicitada al productor del software)

algo así:

1;3.5;2;B4

donde el primer dato representa el nro de piezas, el segundo y tercero las medidas (largo x alto) y el tercero la Ubicación.

En Firebird puedo definir funciones en dll y aplicarselas a la BDD (aun no he escrito ninguna pero si he incorporado las adicionales desde UDF).

Necesito crear/Incorporar las funciones al SQL SERVER:

(pueden ser externas (via dll u otra) ó internas (¿funciones almacenadas SQL?) o cualquier otra forma

Código Delphi [-]
// Funcion para extraer la cantidad del piezas de aux02
// escribo de "memoria" no importa si funciona realmente este codigo
// ya que nececito hacerlo dentro de SQL
Function Piezas(aux02:string):Integer;
Var
s : String;
Begin
s := copy(Aux02,1,pos(';')); 
Result := StrToInt(s);
End;

function Largo(aux02:string):Currency;...
function Alto(aux02:string):Currency;...
function Ubicacion(aux02:string):String;...

La intención es poder ejecutar un QUERY desde SQL con la sintaxis:

Código SQL [-]
SELECT Piezas(Aux02) as piezas, largo(aux02) as largo, 
           Alto(Aux02) as Alto, Ubicacion(Aux02) as ubicacion
FROM Tabla

Uso SQL SERVER 2005

Gracias

[ContraVeneno]

Código SQL [-]
Declare @Cadena varchar(20)
Declare @Piezas varchar(20)
Declare @Largo varchar(20)
Declare @Alto varchar(20)
Declare @Ubicacion varchar(20)

Select @Cadena = '1;3.5;2;B4'
Select @PIezas = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Largo = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Alto = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Ubicacion = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))

Select @Piezas as Piezas, @Largo as Largo ,@Alto as Alto, @Ubicacion as Ubicacion

[[sitrico]]

Gracias por responder tan pronto.

Ahora con el código que muestras puedo separar la cadena (aunque voy a probar usarlo con el campo aux02).

ahora bien,¿ como pongo ese código dentro de una función ?

¿ Existen las funciones en SQL Server ?

[ContraVeneno]

si, tienes que crear un "Stored Procedure" o Procedimiento almacenado

En teoría, a este procedimiento le mandas como parámetro la cadena que quieres revisar y te regresa los datos ya separados, los cuales puedes usar en delphi, en otro procedimiento o donde quieras...

para crear el procedimiento, podrías hacer lo siguiente:

Código SQL [-]
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

-- Para ejecutar el procedimiento utilizas:
--Exec ObtenerDatos '1;3.5;2;B4'

CREATE PROCEDURE ObtenerDatos
@Cadena varchar(20)
AS

Declare 
@Piezas integer,
@Largo float,
@Alto float,
@Ubicacion varchar(20)


Select @PIezas = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Largo = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Alto = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Ubicacion = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))

Select @Piezas as Piezas, @Largo as Largo ,@Alto as Alto, @Ubicacion as Ubicacion

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[[sitrico]]

duplicado.....

[[sitrico]]

Ok, ya +- tengo la idea, revisando por aqui veo que tambien se pueden crear funciones almacenadas.


Que con una sintaxsis similar a la que muestras se puede definir una función.

¿ Las funciones almacenadas pueden devolver usarse dentro de un select ?

Código SQL [-]
SELECT ObtenerPiezas(Aux02) as piezas, largo(aux02) as largo, 
           Alto(Aux02) as Alto, Ubicacion(Aux02) as ubicacion
FROM Tabla

creando una función así:

Código SQL [-]
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION ObtenerPiezas
@Cadena varchar(20)
AS

Declare 
@Piezas integer,
@Largo float,
@Alto float,
@Ubicacion varchar(20)


Select @PIezas = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Largo = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Alto = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Ubicacion = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))

RETURN @PIezas

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Claro que lo lógico sería definir 5 funciones, una global para obtener los 4 campos separados y luego, 4 funciones separadas, que llaman a la global y devuelven sólo el campo en cuestión.

Disculpa la lata pero no me atrevo a tocar la BDD hasta estar bien claro.

[ContraVeneno]

En lugar de hacer 5 funciones, puedes hacer una sola que reciba dos parámetros, el primero sería la cadena a revisar y el segundo parámetro sería un número u opción de dato a regresar.

Luego solo utilizas un "Case when @opcion = 1 then @Piezas when @Opcion = 2 then @Largo..." etc.

[[sitrico]]

Ok, Gracias

[[sitrico]]

Logré llegar hasta aquí :

Código SQL [-]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- Para ejecutar el procedimiento utilizas:
--Exec ObtenerDatos '1;3.5;2;B4'

ALTER FUNCTION [dbo].[SepararPiezas](@Cadena varchar(30))

RETURNS INTEGER
AS
BEGIN
DECLARE @Piezas varchar(20)
DECLARE @Largo varchar(20)
DECLARE @Alto varchar(20)
DECLARE @Ubicacion varchar(20)

Select @PIezas = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Largo = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Alto = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Ubicacion = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))

RETURN CAST(@Piezas As INTEGER)
END

Que funciona bien y puedo aplicar para los demás caso.

Pero si trato de hacer una funcion global para no tener que repetir todo el código para cada campo, me sale:

Msg 102, Level 15, State 31, Procedure SepararAux02, Line 27
Incorrect syntax near 'BEGIN'.

Código SQL [-]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[SepararAux02](@Cadena varchar(30))

RETURNS TABLE
AS
BEGIN
DECLARE @Piezas varchar(20)
DECLARE @Largo varchar(20)
DECLARE @Alto varchar(20)
DECLARE @Ubicacion varchar(20)

Select @PIezas = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Largo = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Alto = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Ubicacion = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))

RETURN
Select cast(@Piezas as integer) as Piezas, 
       cast(@Largo as numeric(9,4)) as Largo ,
     cast(@Alto as numeric(9,4)) as Alto, 
       @Ubicacion as Ubicacion 
END

[ContraVeneno]

Código SQL [-]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[SepararAux02](@Cadena varchar(30))

RETURNS @Resultados TABLE (Piezas integer, Largo Float, Alto Float, Ubicacion varchar(70))
AS
BEGIN
DECLARE @Piezas integer
DECLARE @Largo float
DECLARE @Alto float
DECLARE @Ubicacion varchar(70)

Select @PIezas = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Largo = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Cadena  = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))
Select @Alto = SubString(@Cadena, 0, CharIndex(';', @Cadena))
Select @Ubicacion = SubString(@Cadena, CharIndex(';', @Cadena)+1,len(@Cadena))

INSERT @Resultados
Select @Piezas, @Alto, @Largo, @Ubicacion

RETURN 
END