Store Procedure with return different PDF

Preview

Summary

Download Store Procedure with return different PDF

Description

Retornando DataSet pero usando código de retorno SQL Server 2008 R2 Un procedimiento almacenado puede devolver un valor entero llamado un código de retorno para indicar el estado de ejecución de un procedimiento. Se especifica el código de retorno para un procedimiento almacenado mediante la sentencia RETURN. Al igual que con los parámetros de salida, debe guardar el código de retorno en una variable cuando se ejecuta el procedimiento almacenado para utilizar el valor de código de retorno en el programa de llamada. Por ejemplo, la asignación de variables @result del tipo de datos int se utiliza para almacenar el código de retorno del procedimiento almacenado my_proc , tales como: DECLARE @result int; EXECUTE @result = my_proc; Códigos de retorno se utilizan comúnmente en los bloques de control de flujo dentro de los procedimientos almacenados para establecer el valor del código de retorno para cada posible situación de error. Puede utilizar la función @@ ERROR después de una instrucción de Transact-SQL para detectar si se ha producido un error durante la ejecución de la sentencia.

Ejemplos A. La devolución de un código de retorno diferente, basado en el tipo de error El siguiente ejemplo muestra el usp_GetSalesYTD procedimiento con el control de errores que establezca valores especiales código de retorno para varios errores. La siguiente tabla muestra el valor entero que se asigna por el procedimiento almacenado para cada error posible, y el significado correspondiente para cada valor.

valor de código de retorno

Sentido

0

La ejecución exitosa.

1

No se especifica el valor del parámetro requerido.

2

el valor del parámetro especificado no es válido.

3

Ha producido un error al obtener el valor de las ventas.

4

valor de las ventas NULL encontrado para el vendedor.

CST | Negocios y Tecnología

P á g i n a 1 | 24

Tran Transsac act-SQL t-SQL USE AdventureWorks2008R2; GO IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.usp_GetSalesYTD; GO CREATE PROCEDURE Sales.usp_GetSalesYTD @SalesPerson nvarchar(50) = NULL, -- NULL default value @SalesYTD money = NULL OUTPUT AS -- Validate the @SalesPerson parameter. IF @SalesPerson IS NULL BEGIN PRINT 'ERROR: You must specify a last name for the sales person.' RETURN(1) END ELSE BEGIN -- Make sure the value is valid. IF (SELECT COUNT(*) FROM HumanResources.vEmployee WHERE LastName = @SalesPerson) = 0 RETURN(2) END -- Get the sales for the specified name and -- assign it to the output parameter. SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; -- Check for SQL Server errors. IF @@ERROR 0 BEGIN RETURN(3) END ELSE BEGIN -- Check to see if the ytd_sales value is NULL. IF @SalesYTD IS NULL RETURN(4) ELSE -- SUCCESS!! RETURN(0) END -- Run the stored procedure without specifying an input value. EXEC Sales.usp_GetSalesYTD; GO -- Run the stored procedure with an input value. DECLARE @SalesYTDForSalesPerson money, @ret_code int; -- Execute the procedure specifying a last name for the input parameter -- and saving the output value in the variable @SalesYTD EXECUTE Sales.usp_GetSalesYTD N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT; PRINT N'Year-to-date sales for this employee is ' +

CST | Negocios y Tecnología

P á g i n a 2 | 24

CONVERT(varchar(10), @SalesYTDForSalesPerson);

B. Manejo de los diferentes códigos de retorno que se devuelven de un procedimiento almacenado En el siguiente ejemplo se crea un programa para manejar los códigos de retorno que se devuelven de la usp_GetSalesYTD procedimiento.

Tran Transsac act-SQL t-SQL -- Declare the variables to receive the output value and return code -- of the procedure. DECLARE @SalesYTDForSalesPerson money, @ret_code int; -- Execute the procedure with a title_id value -- and save the output value and return code in variables. EXECUTE @ret_code = Sales.usp_GetSalesYTD N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT; -- Check the return codes. IF @ret_code = 0 BEGIN PRINT 'Procedure executed successfully' -- Display the value returned by the procedure. PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson) END ELSE IF @ret_code = 1 PRINT 'ERROR: You must specify a last name for the sales person.' ELSE IF @ret_code = 2 PRINT 'EERROR: You must enter a valid last name for the sales person.' ELSE IF @ret_code = 3 PRINT 'ERROR: An error occurred getting sales value.' ELSE IF @ret_code = 4 PRINT 'ERROR: No sales recorded for this employee.' GO

Retornado Datos pero usando Parámetros de Salida SQL Server 2008 R2 Si se especifica la palabra clave de salida para un parámetro en la definición del procedimiento, el procedimiento almacenado puede devolver el valor actual del parámetro al programa que llama al salir del procedimiento almacenado. Para guardar el valor del parámetro en una variable que se puede utilizar en el programa de llamada, el programa de llamada debe utilizar la palabra clave OUTPUT al ejecutar el procedimiento almacenado.

Ejemplos El siguiente ejemplo muestra un procedimiento almacenado con una entrada y un parámetro de salida. El primer parámetro en el procedimiento almacenado @SalesPerson recibe el valor de entrada especificado por el programa de llamada, y el segundo parámetro @SalesYTD se utiliza para devolver el valor al programa de llamada. La

CST | Negocios y Tecnología

P á g i n a 3 | 24

instrucción SELECT utiliza el @SalesPerson parámetro para obtener el correcto SalesYTD valor, y asigna el valor a la @SalesYTD parámetro de salida.

Tran Transsac act-SQL t-SQL USE AdventureWorks2008R2; GO IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetEmployeeSalesYTD; GO CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD @SalesPerson nvarchar(50), @SalesYTD money OUTPUT AS SET NOCOUNT ON; SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; RETURN GO

Las siguientes sentencias se ejecutan el procedimiento almacenado con un valor para el parámetro de entrada y guarda el valor de salida del procedimiento almacenado en el @SalesYTD variable local al programa de llamada.

Tran Transsac act-SQL t-SQL -- Declare the variable to receive the output value of the procedure. DECLARE @SalesYTDBySalesPerson money; -- Execute the procedure specifying a last name for the input parameter -- and saving the output value in the variable @SalesYTDBySalesPerson EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT; -- Display the value returned by the procedure. PRINT 'Year-to-date sales for this employee is ' + convert(varchar(10),@SalesYTDBySalesPerson); GO

Los valores de entrada también se pueden especificar para los parámetros de salida cuando se ejecuta el procedimiento almacenado. Esto permite que el procedimiento almacenado para recibir un valor en el programa de llamada, cambiarlo o realizar operaciones con ella, y luego volver el nuevo valor al programa de llamada. En el ejemplo anterior, el @SalesYTDBySalesPerson variable puede ser asignado un valor antes de ejecutar el procedimiento almacenado. El @SalesYTD variable contiene el valor del parámetro en el cuerpo del procedimiento almacenado, y el valor de la@SalesYTD variable se devuelve al programa que llama cuando las salidas de procedimiento almacenado. Esto se refiere a menudo como "la capacidad de pasar por referencia." Si especifica de salida para un parámetro cuando se ejecuta un procedimiento almacenado y el parámetro no está definido mediante la producción en el procedimiento almacenado, se obtiene un mensaje de error. Puede ejecutar un procedimiento almacenado con parámetros OUTPUT y no especificar la salida al ejecutar el procedimiento almacenado. se devuelve ningún error, pero no se puede utilizar el valor de salida en el programa de llamada.

CST | Negocios y Tecnología

P á g i n a 4 | 24

Usando Tipo de Dato Cursor en un paramétro de Salida SQL Server 2008 R2 Procedimientos almacenados de Transact-SQL pueden utilizar el cursor del tipo de datos sólo para los parámetros de salida. Si el cursor se especifica el tipo de datos para un parámetro, se requieren tanto el VARYING y parámetros de salida. Si se especifica la palabra clave VARYING para un parámetro, el tipo de datos debe ser del cursor y la palabra clave de salida debe ser especificado.

Nota

El cursor del tipo de datos no se puede enlazar a las variables de aplicación a través de las API de bases de datos tales como OLE DB, ODBC, ADO, y DB-Library. Debido a que los parámetros de salida deben estar atados ante una aplicación puede ejecutar un procedimiento almacenado, procedimientos almacenados con el cursor parámetros de salida no se pueden llamar desde las API de base de datos. Estos procedimientos pueden ser llamados desde lotes de Transact-SQL, procedimientos almacenados, o se dispara sólo cuando el cursor se asigna a la variable de salida de un local de TransactSQL cursor variable.

Cursor Parámetros de salida Las siguientes reglas se refieren a cursor parámetros de salida cuando se ejecuta el procedimiento:  Para un cursor de sólo avance, las filas devueltas en el conjunto de resultados del cursor son sólo aquellas filas en y más allá de la posición del cursor en la conclusión del procedimiento almacenado ejecutado, por ejemplo: o Un cursor no desplazable se abre en un procedimiento en un conjunto de resultados llamado RS de 100 filas. o El procedimiento obtiene los primeros 5 filas del conjunto de resultados de RS. o El procedimiento devuelve a su llamador. o El conjunto de resultados RS devueltos a la persona que llama consiste en filas de 6 a 100 de RS, y el cursor de la persona que llama se coloca antes de la primera fila de RS.  Para un cursor sólo hacia adelante, si el cursor está situado antes de la primera fila tras la finalización del procedimiento almacenado, todo el conjunto de resultados se devuelve al lote de llamada, procedimiento almacenado o desencadenador. Cuando regresó, la posición del cursor se establece antes de la primera fila.  Para un cursor sólo hacia adelante, si el cursor se encuentra más allá del final de la última fila tras la finalización del procedimiento almacenado, un conjunto de resultados vacío se devuelve al lote de llamada, procedimiento almacenado o desencadenador.

Note

Un conjunto de resultados vacío no es lo mismo que un valor nulo. 

Para un cursor desplazable, todas las filas del conjunto de resultados se devuelven al proceso por lotes llamado, procedimiento almacenado o desencadenador en la conclusión de la ejecución del procedimiento almacenado. Cuando devuelto, la posición del cursor se deja en la posición de la última zona de alcance ejecutado en el procedimiento.

CST | Negocios y Tecnología

P á g i n a 5 | 24



Para cualquier tipo de cursor, si el cursor está cerrado, entonces un valor nulo se pasa de nuevo a la carga de llamadas, procedimiento almacenado o desencadenador. Esto también será el caso si un cursor se asigna a un parámetro, pero que no se abre cursor.

Note

El estado cerrado casi exclusivamente en el tiempo de retorno. Por ejemplo, es válido para cerrar una parte del cursor del camino a través del procedimiento, para abrirlo de nuevo más tarde en el procedimiento, y devolver el resultado de que el cursor se establece en el lote de llamada, procedimiento almacenado o desencadenador.

Ejemplo En el siguiente ejemplo, un procedimiento almacenado se crea que especifica un parámetro de salida, @currency _ cursor utilizando el cursor deltipo de datos. El procedimiento almacenado se denomina a continuación en un lote En primer lugar, crear el procedimiento que se declara a continuación, se abre un cursor en la tabla de divisas.

Tran Transsac act-SQL t-SQL USE AdventureWorks2008R2; GO IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.uspCurrencyCursor; GO CREATE PROCEDURE dbo.uspCurrencyCursor @CurrencyCursor CURSOR VARYING OUTPUT AS SET NOCOUNT ON; SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @CurrencyCursor; GO

A continuación, ejecutar un lote que declara una variable de cursor local, ejecuta el procedimiento para asignar el cursor a la variable local, y después obtiene las filas desde el cursor.

Tran Transs ac act-SQL t-SQL USE AdventureWorks2008R2; GO DECLARE @MyCursor CURSOR; EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO

CST | Negocios y Tecnología

P á g i n a 6 | 24

RETURN (Transact-SQL) Sale incondicionalmente a partir de una consulta o un procedimiento. RETORNO es inmediata y completa y se puede utilizar en cualquier momento para salir de un procedimiento, por lotes, o bloque de instrucciones. Las declaraciones que siguen RETURN no se ejecutan.

Sintaxis RETURN [integer_expression]

Argumentos integer_expression Es el valor entero que se devuelve. Los procedimientos almacenados pueden devolver un valor entero a un procedimiento de llamada o una aplicación.

Tipos de valores devueltos Retornos opcionalmente int .

Nota

A menos documentada de otra manera, todos los procedimientos almacenados del sistema devuelven un valor de 0. Esto indica éxito y un valor distinto de cero indica el fracaso.

Observaciones Cuando se utiliza con un procedimiento almacenado, el retorno no puede devolver un valor nulo. Si un procedimiento intenta devolver un valor nulo (por ejemplo, usando @status RETURN cuando @status es NULL), se genera un mensaje de advertencia y se devuelve un valor de 0. El valor de estado de retorno puede ser incluido en las instrucciones Transact-SQL posteriores en el lote o procedimiento que ejecuta el procedimiento actual, sino que se debe introducir en la siguiente forma: EJECUTAR @return_status = .

Ejemplos A. Al regresar de un procedimiento El siguiente ejemplo muestra si no se especifica ningún nombre de usuario como un parámetro cuando findjobs se ejecuta, RETURN hace que el procedimiento para salir después de un mensaje ha sido enviado a la pantalla del usuario. Si se especifica un nombre de usuario, los nombres de todos los objetos creados por este usuario en la base de datos actual se recuperan de las tablas del sistema apropiados. CREATE PROCEDURE findjobs @nm sysname = NULL AS IF @nm IS NULL BEGIN PRINT 'You must give a user name' RETURN END ELSE BEGIN SELECT o.name, o.id, o.uid FROM sysobjects o INNER JOIN master..syslogins l ON o.uid = l.sid

CST | Negocios y Tecnología

P á g i n a 7 | 24

WHERE l.name = @nm END;

B. Volviendo códigos de estado El siguiente ejemplo se comprueba el estado para el ID de un contacto especificado. Si el estado es Washington ( WA ), un estado de 1 se devuelve.De lo contrario, 2 se vuelve por cualquier otra condición (un valor distinto de WA para StateProvince o BusinessEntityID que no coincidía con una fila). USE AdventureWorks2008R2; GO CREATE PROCEDURE checkstate @param varchar(11) AS IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE BusinessEntityID = @param) = 'WA' RETURN 1 ELSE RETURN 2; GO Los siguientes ejemplos muestran el estado de retorno de la ejecución de checkState. El primero muestra un contacto en Washington; el segundo, el contacto no en Washington; y el tercero, un contacto que no es válido. El @return_status variable local debe ser declarado antes de que pueda ser utilizado. DECLARE @return_status int; EXEC @return_status = checkstate '291'; SELECT 'Return Status' = @return_status; GO Aquí está el conjunto de resultados. Estado de Devolución ------------1 Ejecutar la consulta de nuevo, especificando un número de contacto diferente. DECLARE @return_status int; EXEC @return_status = checkstate '6'; SELECT 'Return Status' = @return_status; GO Aquí está el conjunto de resultados. Estado de Devolución ------------2 Ejecutar la consulta de nuevo, especificando otro número de contacto. DECLARE @return_status int EXEC @return_status = checkstate '12345678901'; SELECT 'Return Status' = @return_status; GO

Aquí está el conjunto de resultados. Estado de Devolución ------------2

CST | Negocios y Tecnología

P á g i n a 8 | 24

@@ROWCOUNT (Transact-SQL) SQL Server 2008 R2

Devuelve el número de filas afectadas por la última declaración. Si el número de filas es de más de 2 mil millones, utilizar ROWCOUNT_BIG .

Sintaxis @@ROWCOUNT

Tipo de valores devueltos int

Observaciones Transact-SQL estados pueden establecer el valor de @@ ROWCOUNT de las siguientes maneras:  Establecer @@ ROWCOUNT al número de filas afectadas o leído. Filas puede o no puede ser enviado al cliente.  Preservar @@ ROWCOUNT de la ejecución de la declaración anterior.  Restablecer @@ ROWCOUNT a 0 pero no devolver el valor al cliente. Las declaraciones que hacen una tarea sencilla siempre ajustar el valor ROWCOUNT @@ a 1. No hay filas se envían al cliente. Ejemplos de estos estados son: SET @local_variable , RETURN, READTEXT, y seleccionar y sin instrucciones de consulta como SELECT GETDATE () o SELECT ' de texto genérico ' . Las declaraciones que hacen una asignación en una consulta o uso RETURN en una consulta ajustar el valor de @@ ROWCOUNT al número de filas afectadas o leídos por la consulta, por ejemplo: SELECT @ local_variable = c1 FROM t1. lenguaje de manipulación de datos (DML) establece el valor de @@ ROWCOUNT al número de filas afectadas por la consulta y devuelve ese valor al cliente. Las instrucciones DML no podrán dirigir sus filas al cliente. DECLARE CURSOR y FETCH ajustar el valor de @@ ROWCOUNT a 1. EXECUTE conservan el anterior ROWCOUNT @@. Declaraciones como USO, SET , DEALLOCATE CURSOR, CLOSE CURSOR, iniciar la transacción o la transacción de confirmación restablecer el valor ROWCOUNT a 0.

Ejemplos En el siguiente ejemplo se ejecuta una ACTUALIZACIÓN declaración y utiliza @@ ROWCOUNT para detectar si se han cambiado todas las filas. USE AdventureWorks2008R2; GO UPDATE HumanResources.Employee

CST | Negocios y Tecnología

P á g i n a 9 | 24

SET JobTitle = N'Executive' WHERE NationalIDNumber = 123456789 IF @@ROWCOUNT = 0 PRINT 'Warning: No rows were updated'; GO

Usando TRY...CATCH en Transact-SQL SQL Server 2008 R2

Los errores en el código de Transact-SQL pueden ser procesados mediante el uso de una construcción TRY ... CATCH similar a las características de manejo de excepciones de la Microsoft Visual C ++ y Microsoft Visual C # idiomas. Una construcción TRY ... CATCH se compone de dos partes: un bloque TRY y un bloque CATCH. Cuando se detecta una condición de error en una instrucción de Transact-SQL que está dentro de un bloque TRY, el control pasa a un bloque CATCH donde el error se puede procesar. Después de que el bloque CATCH controla la excepción, el control se transfiere a la primera instrucción de TransactSQL que sigue a la instrucción END CATCH. Si la instrucción END CATCH es la última instrucción en un procedimiento almacenado o desencadenador, se devuelve el control al código que invoca el procedimiento almacenado o desencadenador. instrucciones Transact-SQL en el bloque TRY siguen a la instrucción que genera un error no se ejecutarán. Si no hay errores dentro del bloque try, el control pasa a la instrucción inmediatamente después de la instrucción END CATCH asociado. Si la instrucción END CATCH es la última instrucción en un pro...