procedimientos - que es un procedimiento almacenado en sql server

Acceso a conjuntos de resultados desde Procedimientos almacenados Servidor SQL Transact-SQL

Question

Estoy usando SQL Server 2005, y me gustaría saber cómo acceder a diferentes conjuntos de resultados desde dentro de transact-sql. El siguiente procedimiento almacenado devuelve dos conjuntos de resultados, ¿cómo puedo acceder a ellos desde, por ejemplo, otro procedimiento almacenado?

CREATE PROCEDURE getOrder (@orderId as numeric) AS
BEGIN   
    select order_address, order_number from order_table where order_id = @orderId
    select item, number_of_items, cost from order_line where order_id = @orderId
END

Necesito poder iterar a través de ambos conjuntos de resultados individualmente.

EDITAR: solo para aclarar la pregunta, quiero probar los procedimientos almacenados. Tengo un conjunto de procedimientos almacenados que se utilizan desde un cliente VB.NET, que devuelve múltiples conjuntos de resultados. No se van a cambiar a una función con valores de tabla; de hecho, no puedo cambiar los procedimientos. Cambiar el procedimiento no es una opción.

Los conjuntos de resultados devueltos por los procedimientos no son los mismos tipos de datos o número de columnas.

Answer 1

Hay dos formas de hacer esto fácilmente. O pegue los resultados en una tabla temporal y luego haga referencia a la tabla temporal desde su sproc. La otra alternativa es colocar los resultados en una variable XML que se utiliza como una variable OUTPUT.

Sin embargo, existen ventajas y desventajas para ambas opciones. Con una tabla temporal, deberá agregar código al script que crea el procedimiento de llamada para crear la tabla temporal antes de modificar el procedimiento. Además, debe limpiar la tabla temporal al final del procedimiento.

Con XML, puede consumir mucha memoria y ser lento.

Answer 2

Hay un obstáculo que puedes hacer también. Agregue un parámetro opcional N int a su sproc. Valor predeterminado de N a -1. Si el valor de N es -1, haga cada una de sus selecciones. De lo contrario, seleccione N y solo N seleccione.

Por ejemplo,

if (N = -1 or N = 0)
    select ...

if (N = -1 or N = 1)
    select ...

Las personas que llaman de su sproc y no especifiquen N recibirán un conjunto de resultados con más de una tabla. Si necesita extraer una o más de estas tablas de otro sproc, simplemente llame a su sproc especificando un valor para N. Tendrá que llamar al sproc una vez para cada tabla que desee extraer. Ineficiente si necesita más de una tabla del conjunto de resultados, pero funciona en puro TSQL.

Answer 3

La respuesta corta es: no puedes hacerlo.

Desde T-SQL no hay forma de acceder a resultados múltiples de una llamada de procedimiento almacenado anidado, sin cambiar el procedimiento almacenado como otros han sugerido.

Para estar completo, si el procedimiento devolviera un único resultado, podría insertarlo en una tabla temporal o variable de tabla con la siguiente sintaxis:

INSERT INTO #Table (...columns...)
EXEC MySproc ...parameters...

Puede usar la misma sintaxis para un procedimiento que devuelve resultados múltiples, pero solo procesará el primer resultado, el resto se descartará.

Answer 4

Lamentablemente, es imposible hacer esto. El problema es, por supuesto, que no hay una sintaxis SQL para permitirlo. Ocurre "debajo del capó", por supuesto, pero no puede obtener estos otros resultados en TSQL, solo desde la aplicación a través de ODBC o lo que sea.

Hay un camino alrededor, como con la mayoría de las cosas. El truco es usar la automatización de ole en TSQL para crear un objeto ADODB que abre cada uno de los resultados y escribe los resultados en las tablas que nominas (o haz lo que quieras con los conjuntos de resultados). también puedes hacerlo en DMO si disfrutas el dolor.

Answer 5

Pude hacerlo fácilmente creando un procedimiento almacenado CLR SQL2005 que contenía un conjunto de datos interno.

Verá, un nuevo SqlDataAdapter completará un sproc de conjunto de resultados múltiples en un conjunto de datos de múltiples tablas de forma predeterminada. Los datos en estas tablas pueden, a su vez, insertarse en tablas #Temp en el sproc llamante que desea escribir. dataset.ReadXmlSchema le mostrará el esquema de cada conjunto de resultados.

Paso 1: comience a escribir el sproc que leerá los datos del sproc multi-resultado-set

a. Cree una tabla separada para cada conjunto de resultados de acuerdo con el esquema.

CREATE PROCEDURE [dbo].[usp_SF_Read] AS
SET NOCOUNT ON;
CREATE TABLE #Table01 (Document_ID VARCHAR(100)
  , Document_status_definition_uid INT
  , Document_status_Code VARCHAR(100) 
  , Attachment_count INT
  , PRIMARY KEY (Document_ID));

segundo. En este punto, es posible que deba declarar un cursor para llamar repetitivamente al sproc CLR que creará aquí:

Paso 2: hacer el CLR Sproc

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub usp_SF_ReadSFIntoTables()

    End Sub
End Class

a. Conéctese usando New SqlConnection("context connection=true") .

segundo. Configure un objeto de comando (cmd) para que contenga el sproc de resultados múltiples.

do. Obtenga todos los datos usando lo siguiente:

    Dim dataset As DataSet = New DataSet
    With New SqlDataAdapter(cmd)
        .Fill(dataset) '' get all the data.
    End With
''you can use dataset.ReadXmlSchema at this point...

re. Itere sobre cada tabla e inserte cada fila en la tabla temporal apropiada (que creó en el paso uno anterior).

Nota final: en mi experiencia, es posible que desee aplicar algunas relaciones entre sus tablas para saber de qué lote proviene cada registro.

¡Eso es todo!

~ Shaun, cerca de Seattle

Answer 6

Puede seleccionarlos en tablas temporales o escribir funciones con valores de tabla para devolver conjuntos de resultados. ¿Están preguntando cómo iterar a través de los conjuntos de resultados?

Answer 7

Tenga en cuenta que hay una limitación adicional no documentada en la instrucción INSERT INTO ... EXEC: no se puede anidar. Es decir, el proceso almacenado que llama el EXEC (o cualquiera que llame a su vez) no puede hacer un INSERT INTO ... EXEC. Parece que hay un único scratchpad por proceso que acumula el resultado, y si están anidados obtendrás un error cuando la persona que llama abre esto, y luego el destinatario intentará volver a abrirlo.

Matthieu, necesitarías mantener tablas temporales separadas para cada "tipo" de resultado. Además, si está ejecutando el mismo varias veces, es posible que deba agregar una columna adicional a ese resultado para indicar de qué convocatoria resultó.