Pasar XML a stored procedure en SQLServer 2005 o superior

Hola, hace tiempo vimos como pasar un XML a un sp en SQL2005, hoy les traigo una forma mas eficiente de pasar un xml por parámetro que funciona en 2005 o superior.

La diferencia principal radica que ahora si mandamos un tipo de datos XML propio del manejador de base de datos.
Ejemplo:


CREATE PROC spPrueba
@sXML XML
AS
BEGIN 
-- Logica
END


Si mal no recuerdo el tipo de dato XML esta disponible a partir de la version 2005 de SQLServer y por lo tanto pueden aplicar la lectura de ese xml de la siguiente forma:



SELECT t.value('./@Clave','INT'),
t.value('./@Descripcion','VARCHAR(30)')
FROM @sXML.nodes('//row') x(t)


Donde @sXML es nuestro parametro, y "row" es nuestro nodo de cada elemento de nuestro xml, y por medio de "t" y la funcion "value" obtenemos nuestro valor de cada propiedad de nuestro nodo.

Les dejo aquí un ejemplo completo:


CREATE PROC spPrueba
@sXML XML
AS
BEGIN 
SELECT t.value('./@Clave','INT'),
t.value('./@Descripcion','VARCHAR(30)')
FROM @sXML.nodes('//row') x(t) 
END

go

--Ejecuto sp
EXEC dbo.spPrueba @sXML = '<row Clave="1" Descripcion="Dato 1" />
<row Clave="2" Descripcion="Dato 2" />' -- xml

go

DROP PROC spPrueba

De igual manera como lo hicimos en el ejemplo pasado podrian ejecutar un insert select para guardar directo en su tabla o en unta tabla temporal para procesar la información.

Espero que les sea de utilidad....
---------------------------------------
Lo que se puede imaginar... se puede programar

Nota: Articulo anterior: http://ganda-csharp.blogspot.mx/2012/07/pasar-un-xml-por-parametro-un-stored.html

Pasar un XML por parámetro a un Stored Procedure en SQLServer 2005

Hola, hoy les traigo algo muy interesante y que me a ayudado mucho a la hora de guardar un conjunto de información que traigo en memoria dentro de mi aplicación. Con esto podran guardar toda su información en una sola llamada al sp, sin necesidad de hacer un ciclo y mandar a llamar el sp n veces por cada registro a insertar.

Ejemplo:

CREATE PROC spTestXML
@psXML NVARCHAR(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @nIdXml INT
DECLARE @tblRegistros TABLE (Indice INT IDENTITY(1,1), ClaEmpleado INT, Nombre VARCHAR(100))

IF ISNULL(@psXML,'') <> ''
BEGIN
exec dbo.sp_xml_preparedocument @nIdXml OUTPUT, @psXML

INSERT INTO @tblRegistros (ClaEmpleado, Nombre)
SELECT ClaEmpleado, Nombre
FROM OPENXML (@nIdXml, '/Empleados/Empleado', 3)
WITH (ClaEmpleado INT, Nombre VARCHAR(100))

EXEC dbo.sp_xml_removedocument @nIdXml
END

SELECT * FROM @tblRegistros

SET NOCOUNT OFF 
END

Ejecución:

EXEC spTestXML @psXML = '<Empleados>
<Empleado>
<ClaEmpleado>2001</ClaEmpleado>
<Nombre>Hugo</Nombre>
</Empleado>
<Empleado>
<ClaEmpleado>5214</ClaEmpleado>
<Nombre>Paco</Nombre>
</Empleado>
<Empleado>
<ClaEmpleado>7841</ClaEmpleado>
<Nombre>Luis</Nombre>
</Empleado>
</Empleados>'

Puntos importantes:

• El parámetro del sp sera de tipo VARCHAR o NVARCHAR
• Hay que ejecutar el sp " sp_xml_preparedocument " el cual te devuelve un ID (sobre este valor se trabaja para obtener los datos)
• La función  "OPENXML"  y  "WITH"  nos ayudaran a obtener nuestros datos, digamos que nos convierte el xml a una tabla. Esta función recibe como parámetro el ID antes obtenido y un string especificándole la estructura de nuestro xml. En la función  WITH  se especifican los atributos que va a contener los valores del xml, digamos que aquí especificamos el nombre y tipo de dato que recibirá los valores que contiene nuestro xml.
• " sp_xml_removedocument  ", al final sólo removemos nuestro xml cargado en memoria con ayuda del ID generado con el primer sp.

Como pueden ver el manejo de xml en SQLServer es muy facil y nos puede ayudar en performance, para evitar ciclos inicesarios, si lo que queremos es insertar un conjunto de registro, en lugar de insertar uno por uno sólo sera necesario un INSERT SELECT.

Actualización (chequen esta forma que es mas sencilla de utilizar):
http://ganda-csharp.blogspot.mx/2013/03/pasar-xml-stored-procedure-en-sqlserver.html

Espero que esto les sea de utilidad
----------------------------------------------------------
Lo que se puede imaginar... se puede programar

Errores manejados en SQLServer, TRY ... CATCH, RAISERROR

Hola, hoy veremos como manejar los errores en SQLServer 2005, y tambien como lanzar errores personalizados con la instrucción RAISERROR


El Try.. Catch funciona de manera similar como en C#, intenta la ejecución de un bloque de codigo y si marca algún error lo cacha.


Sintaxis en SQL:

BEGIN TRY

     -- Instrucciones

END TRY

BEGIN CATCH

-- Manejo del error

END CATCH

A continuación muestro un ejemplo de como tratar un error que nos arroje SQL. La función ERROR_MESAGE propia de SQLServer contendrá la descripción del error.

Ejemplo, error división entre cero:

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
-----------------------------
Error manejado por usuario
(1 row(s) affected)

Ahora, para lanzar un error manejado por el usuario utilizaremos la funcion RAISERROR, se utiliza para devolver mensajes a las aplicaciones con el mismo formato que un error del sistema o un mensaje de advertencia generado por SQL Server Database Engine.
Ejemplo:

IF (SELECT 10/2) = 5
BEGIN
RAISERROR('Error manejado por usuario',16,1)
END
-----------------------------
Error manejado por usuario

Para mas información de la funcion RAISERROR:
http://msdn.microsoft.com/es-es/library/ms177497(SQL.90).aspx

Espero que esto les sea de utilidad, hasta la próxima publicación.
------------------------------------------
Lo que se puede imaginar... se puede programar

Concatenar valores por comas sin validar si es el primero o ultimo elemento

Para concatenar elementos en SQLServer ya sea separadas por coma u otro carácter de separación podemos utilizar la función COALESCE, esta función es parecido a un CASE, si la primera expresión es nula tomara la siguiente expresión y así de esta manera nos evitaremos validar si es la primera vez para no ponerle el carácter de separación ",".

Ejemplo:
-- Tabla temporal
DECLARE @tblNombres TABLE(Nombre VARCHAR(30))
-- Inserto sus valores
INSERT INTO @tblNombres
        ( Nombre )
SELECT 'Jose'
UNION
SELECT 'Juan'
UNION
SELECT 'Pedro'
UNION
SELECT 'Paco'
UNION
SELECT 'Luis'
SELECT * FROM @tblNombres

-- Aquí la solución sencialla de concatenar sin case o if
DECLARE @sNombres VARCHAR(100)
SELECT @sNombres = COALESCE(@sNombres + ',', '') + Nombre
FROM @tblNombres
SELECT @sNombres AS Nombres


Resultado:
(5 row(s) affected)
Nombre
------------------------------
Jose
Juan
Luis
Paco
Pedro
(5 row(s) affected)

Nombres
----------------------------------------------------------------------------------------------------
Jose,Juan,Luis,Paco,Pedro
(1 row(s) affected)

No se les olvide comentar y darle un +1.

----------------------------------------------------------------------------------------------------
Lo que se puede imaginar... se puede programar

Redondear o truncar decimales en SQLServer

Después de bastante tiempo por fin me doy el tiempo de hacer otra publicación sobre programación, hoy veremos como redondear o truncar decimales en sql.

Con redondeo:

SELECT ROUND(100.4658, 2)

---------------------------------------

100.4700

Truncado:

SELECT ROUND(100.4658, 2, 1)

---------------------------------------

100.4600

Truncar y quitar ceros a la derecha

SELECT CONVERT(FLOAT, ROUND(100.4658, 2, 1))

----------------------

100.46

Espero que estoy les sea de utilidad, y no se olviden comentar y dar un +1.

---------------------------------------------------------------

Lo que se puede imaginar... se puede programar

Rellenar un valor con ceros a la izquierda en SQLServer

Como sabrán en SqlServer no existe ninguna función que te permita rellenar un valor numerico con ceros a la izquierda, bueno aunque no se si el la versión 2008 ya exista, hasta el momento sólo he manejado hasta la 2005.

Para completar una cifra con ceros a la izquierda utilizaremos un truco muy sencillo que he utilizado, esto se hace con la instrucción Right.

Ejemplo:


DECLARE @nMiCampo INT
SET @nMiCampo = 20
SELECT RIGHT('00000' + CONVERT(VARCHAR(10), @nMiCampo), 5)

Lo que hacemos con nuestro valor es concatenar una cadena de ceros a su derecha y después con la instrucción Right sólo tomamos nuestro tamaño de caracteres que queremos.

Ejemplo, si queremos mostrar un consecutivo de 6 dígitos, concatenamos 5 ceros, tomando en cuenta que siempre tendremos un dígito en nuestro valor y así con la instrucción Right tomaríamos los primero 6 dígitos de derecha a izquierda.

Buenos, espero que les sea de utilidad, hasta la próxima publicación.

---------------------------------------

Lo que se puede imaginar ... se puede programar

Formatear número a tipo moneda en SQLServer

Hoy veremos como formatear un valor numérico a tipo moneda (###,###.00), las siguientes instrucciones nos permitirá darle ese formato a nuestro dato.

Ejemplo 1:
SELECT CONVERT(VARCHAR(30), CONVERT(MONEY, 1250.86), 1)
Resultado:
1,250.86

Ejemplo 2:

SELECT CONVERT(VARCHAR(30), CONVERT(MONEY, 1250), 1)
Resultado:
1,250.00

Ejemplo 3: Si queremos que no aparezcan los dos decimales
SELECT LEFT(CONVERT(VARCHAR(30), CONVERT(MONEY, 1250), 1), LEN(CONVERT(VARCHAR(30), CONVERT(MONEY, 1250), 1))-3)
Resultado:
1,250

Bueno esto fue algo rápido y sencillo que no necesita mas explicación, espero que les sirva.

-------------------------------------------------------------------------------------------
Lo que se puede imaginar ... se puede programar