Cómo hacer una variable con una lista nvarchar

Usa sql dinámico y forma la consulta en una variable y posteriormente
ejecutas esa variable, te muestro un ejemplo de como lo puedes realizar:

Declare @tabla varchar(50)Declare
@condicion varchar(50)Declare
@Sql varchar(350)set @tabla = 'Nombre_Tabla'
set @condicion = 'nombre in (''Carlos'', ''Maria'')'
select @Sql = 'select * From ' + @tabla + ' Where ' + @condicion
select @Sql
exec (@Sql)

Saludos,
Julio.

"DT" escribió en el mensaje de
noticias:

Hola a todos

Tengo el siguiente sql.
Select *
from tabla
Where nombre IN ('Carlos', 'Maria')

Ahora intento hacer esto:
Declare @nombres nvarchar(100)
Set @nombres = 'Carlos, Maria'


Select *
from tabla
Where nombre IN @nombre

Ningún resuldato cuál es el problema???
Gracias por sus consejos!
(sql Server 2005)

DT,

No existe la macro-sustitucion en T-SQL. La sentencia que tratas de armar,
esta preguntando lo sgte:

select *
from T
where nombre in ('Carlos, Maria');

Te das cuenta que la cadena 'Carlos, Maria' es el unico elemento contenido
en la lista?

Si expandimos el operador "ïn", entonces tendriamos:

select *
from T
where nombre = 'Carlos, Maria';

Lo mismo si usas:

select *
from T
where nombre = '''Carlos'', ''Maria''';

La cadena '''Carlos'', ''Maria''' sigue siendo un unico elemnto y no una
lista.

Existen varias formas de hacerlo. Yo no recomiendo el uso de sql dinamico
solo para concatenar la lista, pues eso puede causar que el cache de
procedimientos crezca demasiado con planes que no seran re-utlizados.

1 - Usar el operador "like".

set @nombres = 'Carlos,Maria';

select *
from T
where ',' + nombre + ',' like '%,' + @nombres + ',%'

A pesar de que funciona, cuando se manipula una columna en un predicado, SQL
Server no puede usar el histograma (en caso de existir un indice que se pueda
usar) para estimar la selectividad de ese predicado. Esto puede provocar que
SQL Server decida usar un scan de el indice en vez de usar seek.

2 - Usar XML para desmantelar la lista.

declare @nombres varchar(100);
declare @x xml;

set @nombres = 'Carlos,Maria';
set @x = '<e>' + REPLACE(@nombres, ',', '</e><e>') + '</e>';

select *
from T
where nombre in (
select N.c.value('.', 'varchar(50)')
from @x.nodes('/e') as N(c)
) as A;
GO

Esta solucion tambien tiene inconvenientes. Si la cadena contiene algun
caracter no valido para xml, entonces puede causar error.

declare @nombres varchar(1000);
declare @x xml;

set @nombres = 'SQL&Server,2008';
set @x = '<e>' + REPLACE(@nombres, ',', '</e><e>') + '</e>';

select N.c.value('.', 'nvarchar(40)')
from @x.nodes('/e') as N(c);
GO

Resultado:

Msg 9411, Level 16, State 1, Line 6
XML parsing: line 1, character 14, semicolon expected

3 - Puedes crear una funcion tipo tabla que devuelva cada elemento de la
lista como una fila. Puedes crear la funcion usando SQLCLR o T-SQL.

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
go
CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO
INSERT Numbers(Number) SELECT n FROM fn_nums(1000);
go
CREATE FUNCTION inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2,
@param + N',', Number) -
Number)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(@param))
AND substring(N',' + @param, Number, 1) =
N',' COLLATE Slovenian_BIN2)
go
declare @nombres varchar(100);

set @nombres = 'Carlos,Maria';

select Value from dbo.inline_split_me(@nombres) as T;

select *
from T
where nombre in (select Value from dbo.inline_split_me(@nombres) as T);
go
drop function dbo.fn_nums,dbo.inline_split_me;
drop table Numbers;
go

No tienes porque eliminar la tabla auxiliar de numeros, pues hay muchos
problemas que se pueden resolver usando esta tabla.

Aca te paso un articulo muy interesante que toca todos estos metodos a fondo.

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html


AMB


"DT" wrote:

Hola a todos

Tengo el siguiente sql.
Select *
from tabla
Where nombre IN ('Carlos', 'Maria')

Ahora intento hacer esto:
Declare @nombres nvarchar(100)
Set @nombres = 'Carlos, Maria'


Select *
from tabla
Where nombre IN @nombre

Ningn resuldato cul es el problema???
Gracias por sus consejos!
(sql Server 2005)

Hola Alejandro,

On 3 okt, 03:18, Alejandro Mesa
wrote:

<...>

1 - Usar el operador "like".

set @nombres = 'Carlos,Maria';

select *
from T
where ',' + nombre + ',' like '%,' + @nombres + ',%'

A pesar de que funciona, ...

Pues no va a funcionar.
Por ejemplo, para el registro con nombre='Carlos':

',Carlos,' like '%,Carlos,Maria,%' es false

cuando se manipula una columna en un predicado, SQL
Server no puede usar el histograma (en caso de existir un indice que se pueda
usar) para estimar la selectividad de ese predicado. Esto puede provocar que
SQL Server decida usar un scan de el indice en vez de usar seek.

2 - Usar XML para desmantelar la lista.

declare @nombres varchar(100);
declare @x xml;

set @nombres = 'Carlos,Maria';
set @x = '<e>' + REPLACE(@nombres, ',', '</e><e>') + '</e>';

select *
from T
where nombre in (
select N.c.value('.', 'varchar(50)')
from @x.nodes('/e') as N(c)
) as A;
GO

Esta solucion tambien tiene inconvenientes. Si la cadena contiene algun
caracter no valido para xml, entonces puede causar error.

declare @nombres varchar(1000);
declare @x xml;

set @nombres = 'SQL&Server,2008';
set @x = '<e>' + REPLACE(@nombres, ',', '</e><e>') + '</e>';

select N.c.value('.', 'nvarchar(40)')
from @x.nodes('/e') as N(c);
GO

Resultado:

Msg 9411, Level 16, State 1, Line 6
XML parsing: line 1, character 14, semicolon expected

3 - Puedes crear una funcion tipo tabla que devuelva cada elemento de la
lista como una fila. Puedes crear la funcion usando SQLCLR o T-SQL.

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
go
CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO
INSERT Numbers(Number) SELECT n FROM fn_nums(1000);
go
CREATE FUNCTION inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2,
@param + N',', Number) -
Number)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(@param))
AND substring(N',' + @param, Number, 1) > N',' COLLATE Slovenian_BIN2)
go
declare @nombres varchar(100);

set @nombres = 'Carlos,Maria';

select Value from dbo.inline_split_me(@nombres) as T;

select *
from T
where nombre in (select Value from dbo.inline_split_me(@nombres) as T);
go
drop function dbo.fn_nums,dbo.inline_split_me;
drop table Numbers;
go

No tienes porque eliminar la tabla auxiliar de numeros, pues hay muchos
problemas que se pueden resolver usando esta tabla.

Aca te paso un articulo muy interesante que toca todos estos metodos a fondo.

Arrays and Lists in SQL Serverhttp://www.sommarskog.se/arrays-in-sql.html

Que forma de complicarse la vida Alejandro. En lo que es fuerte SQL
es en trabajar con tablas. No con listas, arrays o documentos XML.
Parece entonces que estructurar @nombres en una tabla sería lo
mas sensato.

declare @nombres table(nombre varchar(50))
insert @nombres (nombre) values ('Carlos')
insert @nombres (nombre) values ('Maria')

Dada esta estructura (una tabla) se simplifica la cosa:

select * from T
where nombre in (select nombre from @nombres)

o bien:

select * from T
where exists(select * from @nombres where nombre=T.nombre)

o tal vez:

select T.* from T join @nombres N on T.nombre=N.nombre

o ...?

Saludos,
Carlos

Carlos M. Calvelo,

> select *
> from T
> where ',' + nombre + ',' like '%,' + @nombres + ',%'
>
> A pesar de que funciona, ...

Pues no va a funcionar.

Gracías por la corrección. Eso me pasa por contestar de carretilla.

use Northwind;
go

declare @customers nvarchar(100);
set @customers = N'BLONP,BOLID';

select *
from dbo.customers
where N',' + @customers + ',' like N'%,' + customerid + N',%';
go

Que forma de complicarse la vida Alejandro. En lo que es fuerte SQL
es en trabajar con tablas. No con listas, arrays o documentos XML.
Parece entonces que estructurar @nombres en una tabla sería lo
mas sensato.

Claro que si, que usando una tabla es lo logico, pero hasta la version 2005,
no contavamos con esa facilidad. Como bien sabes, no teniamos un parametro
tipo tabla para ser usado en un procedimiento almacenado, el cual ya tenemos
en la version 2008.

Si trabajas con versiones anteriores a la 2008, entonces como defines esa
variable tipo tabla desde una aplicacion cliente?

Claro esta que puedes enviar tantas sentencias "insert" como elementos en la
lista, pero eso es mas lento que enviar la lista y desmantelarla en el
servidor.


AMB


"Carlos M. Calvelo" wrote:

Hola Alejandro,

On 3 okt, 03:18, Alejandro Mesa
wrote:

<...>

>
> 1 - Usar el operador "like".
>
> set @nombres = 'Carlos,Maria';
>
> select *
> from T
> where ',' + nombre + ',' like '%,' + @nombres + ',%'
>
> A pesar de que funciona, ...

Pues no va a funcionar.
Por ejemplo, para el registro con nombre='Carlos':

',Carlos,' like '%,Carlos,Maria,%' es false


> cuando se manipula una columna en un predicado, SQL
> Server no puede usar el histograma (en caso de existir un indice que se pueda
> usar) para estimar la selectividad de ese predicado. Esto puede provocar que
> SQL Server decida usar un scan de el indice en vez de usar seek.
>
> 2 - Usar XML para desmantelar la lista.
>
> declare @nombres varchar(100);
> declare @x xml;
>
> set @nombres = 'Carlos,Maria';
> set @x = '<e>' + REPLACE(@nombres, ',', '</e><e>') + '</e>';
>
> select *
> from T
> where nombre in (
> select N.c.value('.', 'varchar(50)')
> from @x.nodes('/e') as N(c)
> ) as A;
> GO
>
> Esta solucion tambien tiene inconvenientes. Si la cadena contiene algun
> caracter no valido para xml, entonces puede causar error.
>
> declare @nombres varchar(1000);
> declare @x xml;
>
> set @nombres = 'SQL&Server,2008';
> set @x = '<e>' + REPLACE(@nombres, ',', '</e><e>') + '</e>';
>
> select N.c.value('.', 'nvarchar(40)')
> from @x.nodes('/e') as N(c);
> GO
>
> Resultado:
>
> Msg 9411, Level 16, State 1, Line 6
> XML parsing: line 1, character 14, semicolon expected
>
> 3 - Puedes crear una funcion tipo tabla que devuelva cada elemento de la
> lista como una fila. Puedes crear la funcion usando SQLCLR o T-SQL.
>
> CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
> go
> CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
> RETURN
> WITH
> L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
> L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
> L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
> L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
> L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
> L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
> Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
> SELECT n FROM Nums WHERE n <= @n;
> GO
> INSERT Numbers(Number) SELECT n FROM fn_nums(1000);
> go
> CREATE FUNCTION inline_split_me(@param nvarchar(MAX))
> RETURNS TABLE AS
> RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
> substring(@param, Number,
> charindex(N',' COLLATE Slovenian_BIN2,
> @param + N',', Number) -
> Number)
> ))) AS Value
> FROM Numbers
> WHERE Number <= convert(int, len(@param))
> AND substring(N',' + @param, Number, 1) > > N',' COLLATE Slovenian_BIN2)
> go
> declare @nombres varchar(100);
>
> set @nombres = 'Carlos,Maria';
>
> select Value from dbo.inline_split_me(@nombres) as T;
>
> select *
> from T
> where nombre in (select Value from dbo.inline_split_me(@nombres) as T);
> go
> drop function dbo.fn_nums,dbo.inline_split_me;
> drop table Numbers;
> go
>
> No tienes porque eliminar la tabla auxiliar de numeros, pues hay muchos
> problemas que se pueden resolver usando esta tabla.
>
> Aca te paso un articulo muy interesante que toca todos estos metodos a fondo.
>
> Arrays and Lists in SQL Serverhttp://www.sommarskog.se/arrays-in-sql.html
>

Que forma de complicarse la vida Alejandro. En lo que es fuerte SQL
es en trabajar con tablas. No con listas, arrays o documentos XML.
Parece entonces que estructurar @nombres en una tabla sería lo
mas sensato.

declare @nombres table(nombre varchar(50))
insert @nombres (nombre) values ('Carlos')
insert @nombres (nombre) values ('Maria')

Dada esta estructura (una tabla) se simplifica la cosa:

select * from T
where nombre in (select nombre from @nombres)

o bien:

select * from T
where exists(select * from @nombres where nombre=T.nombre)

o tal vez:

select T.* from T join @nombres N on T.nombre=N.nombre

o ...?

Saludos,
Carlos

Hola Alejandro,

On 3 okt, 19:56, Alejandro Mesa
wrote:

> Que forma de complicarse la vida Alejandro. En lo que es fuerte SQL
> es en trabajar con tablas. No con listas, arrays o documentos XML.
> Parece entonces que estructurar @nombres en una tabla sería lo
> mas sensato.

Claro que si, que usando una tabla es lo logico, pero hasta la version 2005,
no contavamos con esa facilidad. Como bien sabes, no teniamos un parametro
tipo tabla para ser usado en un procedimiento almacenado, el cual ya tenemos
en la version 2008.

Si trabajas con versiones anteriores a la 2008, entonces como defines esa
variable tipo tabla desde una aplicacion cliente?

OK!
Claro que para solucionarlo de forma genérica se complica la cosa.

El OP empezó con que hace esto:

Declare @nombres nvarchar(100)
Set @nombres = 'Carlos, Maria'

y me has pillado pensando:

"Pues que haga esto:

declare @nombres table(nombre varchar(50))
insert @nombres (nombre) values ('Carlos')
insert @nombres (nombre) values ('Maria')

... y ya está."

Puede usted considerar mis comentarios anteriores al respecto
como 'no enviados'. :-)

Saludos,
Carlos