Como hago esta consulta

Igual que el otro, le pasas un documento xml al procedimiento almacenado que
contiene los Ids con el formato. El documento xml lo pasas como una cadena de
carateres y tiene un formato como este:

<Ids>
<Id>1</Id>
<Id>2</Id>
<Ids>


El procedimiento almacenado es un poco más feo que en 2005 pero hace lo mismo:

CREATE PROCEDURE ClientesConProductos
@IdsProductos TEXT
AS
DECLARE @DocHandle INT
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @IdsProductos

SELECT IdCliente, Nombre
FROM Clientes C
WHERE NOT EXISTS(
SELECT Id FROM OPENXML(@DocHandle, '/Ids/Id', 2) WITH ( Id INT '.')
WHERE Id NOT IN (
SELECT IdProducto
FROM ProductosClientes
WHERE IdCliente = C.IdCliente
)
)

EXEC sp_xml_removedocument @DocHandle


Saludos:

Jesús López
MVP VB
Solid Quality Learning
www.solidqualitylearning.com



"LuisMiguel" escribió:

Lo siento, es SQL 2000

Feliz Entrada de Año
LuisMiguel
"Jesús López" escribió en el mensaje
news:
>
> En SQL Server 2005 yo crearía un procedimiento almacenado
> ClientesConProductos al que se le pasaría un XML con los Ids de los
> productos. La forma del XML sería la siguiente;
>
> <Ids>
> <Id>1</Id>
> <Id>2</Id>
> </Ids>
>
> O sea como elemento raíz Ids, y por cada Id de producto un elemento Id.
>
> El procedimiento almacenado sería el siguiente:
>
> CREATE PROCEDURE ClientesConProductos
> @IdsProductos XML
> AS
> WITH IdsProductos( Id )
> AS
> (
> SELECT Id.value('.', 'int') As Id
> FROM @IdsProductos.nodes('/Ids/Id') As Ids(Id)
> )
> SELECT IdCliente, Nombre
> FROM Clientes C
> WHERE NOT EXISTS(
> SELECT Id from IdsProductos
> WHERE Id NOT IN (
> SELECT IdProducto
> FROM ProductosClientes
> WHERE IdCliente = C.IdCliente
> )
> )
> GO
>
> Desde el Management Studio podríamos ejecutarlo como :
>
> EXEC ClientesConProductos '<Ids><Id>1</Id><Id>3</Id></Ids>'
>
> Desde VB se ejecutaría como cualquier otro procedimiento almacenado que
> devuelve registros, podría ser, por qué no el SelectCommand de un
> DataAdapter.
>
> Saludos:
>
> Jesús López
> MVP
>
>

Gracias
Pues a ti te parecera feo, pero a mi me ha parecido de perlas.
A proposito, mis Id eran varchar, y tuve que cambiar la definicion, despues
de intentar entender el OPENXML, lo que no me queda claro es lo que hace el
"." posterior al tipo de datos.
Como este filtro tendre que utilizarlo en varios informes, seria aconsejable
hacer una funcion que me devuelva los codigos de los Clientes y realizar un
INNER JOIN con el campo de Clientes, funcionara rapido?
de nuevo 1000 gracias

Gracias
LuisMiguel
"Jesús López" escribió en el mensaje
news:

Igual que el otro, le pasas un documento xml al procedimiento almacenado
que
contiene los Ids con el formato. El documento xml lo pasas como una cadena
de
carateres y tiene un formato como este:

<Ids>
<Id>1</Id>
<Id>2</Id>
<Ids>


El procedimiento almacenado es un poco más feo que en 2005 pero hace lo
mismo:

CREATE PROCEDURE ClientesConProductos
@IdsProductos TEXT
AS
DECLARE @DocHandle INT
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @IdsProductos

SELECT IdCliente, Nombre
FROM Clientes C
WHERE NOT EXISTS(
SELECT Id FROM OPENXML(@DocHandle, '/Ids/Id', 2) WITH ( Id INT '.')
WHERE Id NOT IN (
SELECT IdProducto
FROM ProductosClientes
WHERE IdCliente = C.IdCliente
)
)

EXEC sp_xml_removedocument @DocHandle


Saludos:

Jesús López
MVP VB
Solid Quality Learning
www.solidqualitylearning.com



"LuisMiguel" escribió:

Lo siento, es SQL 2000

Feliz Entrada de Año
LuisMiguel
"Jesús López" escribió en el mensaje
news:
>
> En SQL Server 2005 yo crearía un procedimiento almacenado
> ClientesConProductos al que se le pasaría un XML con los Ids de los
> productos. La forma del XML sería la siguiente;
>
> <Ids>
> <Id>1</Id>
> <Id>2</Id>
> </Ids>
>
> O sea como elemento raíz Ids, y por cada Id de producto un elemento Id.
>
> El procedimiento almacenado sería el siguiente:
>
> CREATE PROCEDURE ClientesConProductos
> @IdsProductos XML
> AS
> WITH IdsProductos( Id )
> AS
> (
> SELECT Id.value('.', 'int') As Id
> FROM @IdsProductos.nodes('/Ids/Id') As Ids(Id)
> )
> SELECT IdCliente, Nombre
> FROM Clientes C
> WHERE NOT EXISTS(
> SELECT Id from IdsProductos
> WHERE Id NOT IN (
> SELECT IdProducto
> FROM ProductosClientes
> WHERE IdCliente = C.IdCliente
> )
> )
> GO
>
> Desde el Management Studio podríamos ejecutarlo como :
>
> EXEC ClientesConProductos '<Ids><Id>1</Id><Id>3</Id></Ids>'
>
> Desde VB se ejecutaría como cualquier otro procedimiento almacenado que
> devuelve registros, podría ser, por qué no el SelectCommand de un
> DataAdapter.
>
> Saludos:
>
> Jesús López
> MVP
>
>

En este fragmento:

OPENXML(@DocHandle, '/Ids/Id', 2) WITH ( Id INT '.')

'/Ids/Id' es la expresión XPath (la ruta para entendernos) para encontrar
los registros dentro del documento XML, lo que marca, evidentemente, son los
'Id'. Dentro de cada registro están los campos, en nuestro caso sólo hay
uno, el Id, que es el nodo texto del propio nodo del registo. (Id INT '.')
indica que los registros tienen sólo un campo que le vamos a llamar Id, que
es de tipo INT, y el '.' es la expresión XPath relativa al nodo registro
para encontrar el campo, el punto significa nodo actual, asi que lo que le
estamos diciendo es que el campo Id se encuentra en el propio nodo del
registro o sea en '/Ids/Id'.

En cuanto a la eficiencia, tengo que decirte que la consulta ya es "por
definición" ineficiente. No hay forma de que SQL Server no tenga que
recorrerse todos los registros de la tabla clientes, y entonces, por cada
cliente y por cada uno de los Id de producto que le hemos pasado por
parámetro, tiene que buscar en la tabla ProductosClientes si el cliente
tiene ese producto.

Si ya de por sí, la consulta es ineficiente, sólo faltaba que además la
metiéramos en una función tabular de múltiples sentencias. Sí, no la podemos
meter en una función tabular en línea porque estamos en SQL 2000 y tenemos
que usar sp_xml_prepare_document, OPENXML y sp_xml_removedocument, ya
tenemos ahí, las múltiples sencencias. Un función tabular de múltiples
sentencias requiere declarar una variable de tipo tabla (la que devuelve) e
insertar todos los registros en esta variable. Las variables de tipo tabla,
aunque parezca que no, se implementan como tablas temporales, o sea que
estamos creando una tabla y estamos insertando registros en una tabla, no
sólo leer como con el procedimiento almacenado que te he puesto. En
definitiva, meterlo en una función tabular es añadir ineficiencia. Si además
luego lo tenemos que unir con JOINS con otras tablas, ya ni te cuento.

Está claro que usar una función tabular va a facilitar el desarrollo de
aquellas consultas e informes que requieran este tipo de filtro, el código
será más sencillo y mantenible, lo cual es bueno, pero esto tiene un coste
en eficiencia que hay que valorar.

Así que tu verás lo que quieres. Si quieres eficiencia tendrás que usar
procedimientos almacenados para todas las consultas e informes que requieran
este tipo de filtro y repetir todo ese follón de sp_xml_preparedocument,
OPENXML y sp_xml_removedocument en estos procedimientos almacenados.

Por cierto, es mucho más eficiente usar Ids de tipo INT que de tipo VARCHAR,
así que te recomendaría que los usaras de tipo int siempre que puedas.

Saludos:

Jesús López
MVP

Gracias de nuevo
Vere si me interesa repetir el codigo o crear esa tabla con el coste del
rendimiento
Gracias
LuisMiguel
"Jesús López" escribió en el mensaje
news:

En este fragmento:

OPENXML(@DocHandle, '/Ids/Id', 2) WITH ( Id INT '.')

'/Ids/Id' es la expresión XPath (la ruta para entendernos) para encontrar
los registros dentro del documento XML, lo que marca, evidentemente, son
los 'Id'. Dentro de cada registro están los campos, en nuestro caso sólo
hay uno, el Id, que es el nodo texto del propio nodo del registo. (Id INT
'.') indica que los registros tienen sólo un campo que le vamos a llamar
Id, que es de tipo INT, y el '.' es la expresión XPath relativa al nodo
registro para encontrar el campo, el punto significa nodo actual, asi que
lo que le estamos diciendo es que el campo Id se encuentra en el propio
nodo del registro o sea en '/Ids/Id'.

En cuanto a la eficiencia, tengo que decirte que la consulta ya es "por
definición" ineficiente. No hay forma de que SQL Server no tenga que
recorrerse todos los registros de la tabla clientes, y entonces, por cada
cliente y por cada uno de los Id de producto que le hemos pasado por
parámetro, tiene que buscar en la tabla ProductosClientes si el cliente
tiene ese producto.

Si ya de por sí, la consulta es ineficiente, sólo faltaba que además la
metiéramos en una función tabular de múltiples sentencias. Sí, no la
podemos meter en una función tabular en línea porque estamos en SQL 2000 y
tenemos que usar sp_xml_prepare_document, OPENXML y sp_xml_removedocument,
ya tenemos ahí, las múltiples sencencias. Un función tabular de múltiples
sentencias requiere declarar una variable de tipo tabla (la que devuelve)
e insertar todos los registros en esta variable. Las variables de tipo
tabla, aunque parezca que no, se implementan como tablas temporales, o sea
que estamos creando una tabla y estamos insertando registros en una tabla,
no sólo leer como con el procedimiento almacenado que te he puesto. En
definitiva, meterlo en una función tabular es añadir ineficiencia. Si
además luego lo tenemos que unir con JOINS con otras tablas, ya ni te
cuento.

Está claro que usar una función tabular va a facilitar el desarrollo de
aquellas consultas e informes que requieran este tipo de filtro, el código
será más sencillo y mantenible, lo cual es bueno, pero esto tiene un coste
en eficiencia que hay que valorar.

Así que tu verás lo que quieres. Si quieres eficiencia tendrás que usar
procedimientos almacenados para todas las consultas e informes que
requieran este tipo de filtro y repetir todo ese follón de
sp_xml_preparedocument, OPENXML y sp_xml_removedocument en estos
procedimientos almacenados.

Por cierto, es mucho más eficiente usar Ids de tipo INT que de tipo
VARCHAR, así que te recomendaría que los usaras de tipo int siempre que
puedas.

Saludos:

Jesús López
MVP