Cómo hacer una variable con una lista nvarchar

Carlos,

Me alegro que lo comentates, porque yo olvide hablar sobre la nueva
facilidad que tenemos en la version 2008 (table-valued parameters).

Tus comentarios son siempre bienvenidos, asi que no dejes de hacerlos.

AMB


"Carlos M. Calvelo" wrote:

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

Hola Alejandro,

On 4 okt, 01:41, Alejandro Mesa
wrote:

Carlos,

Me alegro que lo comentates, porque yo olvide hablar sobre la nueva
facilidad que tenemos en la version 2008 (table-valued parameters).

Tus comentarios son siempre bienvenidos, asi que no dejes de hacerlos.

Pues ahí va: :)

Me he puesto a hacer uno de mis pinitos :) y a ver que te parece.

Con una CTE he conseguido algo que parece bastante eficiente. Pero
tiene el problema de que no puedo meterlo en una función porque
no se puede especificar un option(maxrecursion ...) en funciones.

A ver, que pongo lo que tengo por ahora:



declare @nombres varchar(max), @i int, @dt datetime
set @nombres=''
set @i=0
while @i<32767
begin
set @nombres=@nombres+'N'+cast(@i as varchar)+','
set @i=@i+1
end


set @dt=getdate()
select Value from dbo.inline_split_me(@nombres) as T;
print datediff(ms,@dt,getdate())


set @dt=getdate()

declare @string varchar(max), @delim char(1)
set @string=@nombres
set @delim=',';

begin
declare @lenstr int;
set @lenstr =len(@string);

with A as
(
select
case
when p=0 then @string
when p=1 then ''
else left(@string,p-1)
end as delimvalue,
case when p=0 then @lenstr+1 else p end as m,
charindex(@delim,@string,
case
when p=0 then @lenstr
else p
end+1) as n
from (select charindex(@delim,@string) as p) b
union all
select
case
when n=m+1 or @lenstr=m then ''
when n=0 then right(@string,@lenstr-m)
else substring(@string,m+1,n-m-1)
end as delimvalue,
case when n=0 then @lenstr+1 else n end as m,
charindex(@delim,@string,n+1) as n
from A
where m<=@lenstr
)
select delimvalue from A option(maxrecursion 32767)
end

print datediff(ms,@dt,getdate())

-
Para cada registro en la CTE, las columnas m y n son las
posiciones de las las dos siguientes comas. Un poco
críptico, pero espero se entienda.
Pero no veo forma de meter esto en una función con el
dichoso option(maxrecursion...)

Fíjate además que los resutados de la función inline_split_me() y
de la CTE son distintos. @nombres termina con "N32766," o sea
que hay una cadena vacía al final. Ese registro se ve en el
resultado de la CTE, pero no en el de la función.

Un problema? del CTE es también que solo puede generar hasta
32767 registros, por lo de la 'maxrecursion'. Una ventaja es
que es independiente de la tabla Numbers.

Buen pasatiempos este! :-)
Bueno... a ver que te parece.

Saludos,
Carlos

Carlos,

Fantastico, no hay mejor forma de aprender que tratando de hacerlo por uno
mismo.

Con una CTE he conseguido algo que parece bastante eficiente. Pero
tiene el problema de que no puedo meterlo en una función porque
no se puede especificar un option(maxrecursion ...) en funciones.

Asi es. Seria responsabilidad de quien use la funcion de usar la "opcion" en
el query la use.

select *
from tu_funcion('Carlos,Maria') as T
option (maxrecursion 0);
go

Pero si el programador no lo hace entonces pudiera recivir el error cuando
la CTE recursiva se pase de el valor por defecto de maxrecursion (100).

Si lees el articulo de Erland, el cual es muy refrescante, te daras cuenta
que uno de los metodos es usar una CTE recursiva como la que has usado.


AMB


"Carlos M. Calvelo" wrote:

Hola Alejandro,

On 4 okt, 01:41, Alejandro Mesa
wrote:
> Carlos,
>
> Me alegro que lo comentates, porque yo olvide hablar sobre la nueva
> facilidad que tenemos en la version 2008 (table-valued parameters).
>
> Tus comentarios son siempre bienvenidos, asi que no dejes de hacerlos.
>

Pues ahí va: :)

Me he puesto a hacer uno de mis pinitos :) y a ver que te parece.

Con una CTE he conseguido algo que parece bastante eficiente. Pero
tiene el problema de que no puedo meterlo en una función porque
no se puede especificar un option(maxrecursion ...) en funciones.

A ver, que pongo lo que tengo por ahora:



declare @nombres varchar(max), @i int, @dt datetime
set @nombres=''
set @i=0
while @i<32767
begin
set @nombres=@nombres+'N'+cast(@i as varchar)+','
set @i=@i+1
end


set @dt=getdate()
select Value from dbo.inline_split_me(@nombres) as T;
print datediff(ms,@dt,getdate())


set @dt=getdate()

declare @string varchar(max), @delim char(1)
set @string=@nombres
set @delim=',';

begin
declare @lenstr int;
set @lenstr =len(@string);

with A as
(
select
case
when p=0 then @string
when p=1 then ''
else left(@string,p-1)
end as delimvalue,
case when p=0 then @lenstr+1 else p end as m,
charindex(@delim,@string,
case
when p=0 then @lenstr
else p
end+1) as n
from (select charindex(@delim,@string) as p) b
union all
select
case
when n=m+1 or @lenstr=m then ''
when n=0 then right(@string,@lenstr-m)
else substring(@string,m+1,n-m-1)
end as delimvalue,
case when n=0 then @lenstr+1 else n end as m,
charindex(@delim,@string,n+1) as n
from A
where m<=@lenstr
)
select delimvalue from A option(maxrecursion 32767)
end

print datediff(ms,@dt,getdate())

-
Para cada registro en la CTE, las columnas m y n son las
posiciones de las las dos siguientes comas. Un poco
críptico, pero espero se entienda.
Pero no veo forma de meter esto en una función con el
dichoso option(maxrecursion...)

Fíjate además que los resutados de la función inline_split_me() y
de la CTE son distintos. @nombres termina con "N32766," o sea
que hay una cadena vacía al final. Ese registro se ve en el
resultado de la CTE, pero no en el de la función.

Un problema? del CTE es también que solo puede generar hasta
32767 registros, por lo de la 'maxrecursion'. Una ventaja es
que es independiente de la tabla Numbers.

Buen pasatiempos este! :-)
Bueno... a ver que te parece.

Saludos,
Carlos

Hola Alejandro,

On 4 okt, 02:25, Alejandro Mesa
wrote:

Carlos,

Fantastico, no hay mejor forma de aprender que tratando de hacerlo por uno
mismo.

> Con una CTE he conseguido algo que parece bastante eficiente. Pero
> tiene el problema de que no puedo meterlo en una función porque
> no se puede especificar un option(maxrecursion ...) en funciones.

Asi es. Seria responsabilidad de quien use la funcion de usar la "opcion" en
el query la use.

select *
from tu_funcion('Carlos,Maria') as T
option (maxrecursion 0);
go

Pero si el programador no lo hace entonces pudiera recivir el error cuando
la CTE recursiva se pase de el valor por defecto de maxrecursion (100).

Pues no me gusta tener dejar la opcion fuera de la función.., y
si nos ponemos a eso no me gusta ni que exista tal opción.
Pero... es lo que hay.

Saludos,
Carlos

Hola Alejandro,

A raíz de lo que hablamos ayer en este hilo quisiera compartir lo
siguiente.

La forma general de CTEŽs recursivas es algo así:

WITH <NombreTabla>
(
<consulta base>
UNION ALL
<consulta2> OPERATOR <consulta sobre NombreTabla>
)
<consulta-resultado> (a base de <NombreTabla>)

La recursividad es obvia en la definición de la CTE. Es decir que se
hace referencia a <NombreTabla> dentro de su propia definición. Como
todos sabemos y como ya hemos visto en el hilo, la ejecución de una
CTE está restringida por un máximo de llamadas recursivas. Por
defecto 100 y como máximo 32767 (Žpor casualidadŽ el valor máximo que
puede obtener un entero representado en twoŽs complement en 16 bits).

Ahora hay que diferenciar entre la definición o especificación de una
función en general o una CTE en particular (el códico que escribe el
programador) y el proceso que esa definición genera en la memoria de
una computadora para calcular el resultado deseado. En concreto, la
definición puede ser recursiva (algo deseable para poder expresar de
forma sencilla la solución a ciertos problemas) y el proceso que se
lleva a cabo para realizar el cálculo puede ser iterativo. Esto
último es deseable porque un proceso iterativo puede usar una
cantidad de memoria constante independientemente del input y un
proceso recursivo no.

Bien. Pues los diseñadores y programadores de estas CTE's parecen
no ser conscientes de esto, porque el proceso que generan estas
CTE's es recursivo y no tiene por que serlo.

Para la consulta definida recursivamente arriba el método iterativo
para su ejecución, en líneas generales, es el siguiente:
(No estoy tratando de ser preciso aquí, solo de comunicar la idea.)

<resultado> := <consulta-base>;

<resultado-intermedio> := <resultado> OP <consulta-recursiva>;

WHILE NOT (<resultado-intermedio> = {})
BEGIN

<resultado> : <resultado> UNION <resultado-intermedio>;

<resultado-intermedio> : <resultado-intermedio> OP <consulta-recursiva>;

END

Aclaraciones:
Los valores que se asignan con :=, son tablas (resultados de
consultas).
OP es la operación que define la recursión (por ejemplo un join)
{} representa una tabla sin registros.

Obviamente esta estrategia es iterativa y de paso una explicación
sencilla de que son las CTE's recursivas en general. Pero visto lo
visto con la restricción de MAXRECURSION, no es algo así lo que
hace SQL Server.

Para un ejemplo de como la recursión en el proceso representa un
problema véase este mismo hilo. Y aquí abajo pongo una solución que
yo mismo tengo que programar como iterativa, pero que en realidad
es la estrategia que deberían seguir automáticamente las CTEŽs sin
restricciones como el MAXRECURSION.

En comentarios he tratado de señalar los puntos clave que concuerdan
con la estrategia general descrita arriba. Esta función hace lo
mismo que la versión con CTE pero sin tal restricción. Naturalmente
no es tan eficiente, pero no se trata de eso aquí.
De lo que se trata es de que el trabajo con SQL Server es una lucha
constante con restricciones sin ninguna base en la lógica. Eso nos
obliga a tener que solucinar rompecabezas que no tienen ninguna razón
de ser y eso es a coste del problema que realmente tenemos entre
manos. Muchos BLOGs y publicaciones en internet se basan precisamente
en esos rompecabezas, pero por lo general sin crítica alguna hacia
el producto.

Alejandro, quizás quieras probar la función que he puesto abajo. No
he tratado de ninguna manera de optimizar. Solo he peleado con ella
para hacerla funcionar y solo es un ejemplo de una estrategia mas
general que deberían seguir las CTE's para calcular sus resultados
sin recursión. Comentarios son bienvenidos.

Los que quieran profundizar mas en los conceptos mencionados arriba
sobre recursividad e iteración (diferencia entre especificación de
una función y el poceso que esta genera en memoria) pueden darse un
paseo por el mundo de lenguajes funcionales. Por ejemplo algo de la
famila LISP (Scheme) y ver lo que significan conceptos como 'tail
recursion' y demás. Pero es también un 'truco' de optimización
utilizado por compiladores de lenguajes mas 'convencionales'.

Me parece que voy a tener que empezar un BLOG :) para publicar este
tipo de cosas. Pero mucho mejor elaborado y no así a correr y a
medias como en esta aportación. Además ya no me parece un tema
para este foro.

Saludos,
Carlos

create function [dbo].[delimstr2table3](@string varchar(max), @delim
char(1))
returns @res table(delimvalue varchar(100))
as
begin
declare @t1 table(delimvalue varchar(100), m int, n int)
declare @t2 table(delimvalue varchar(100), m int, n int)

insert into @t1 (delimvalue, m, n)
select
case
when p=0 then @string
when p=1 then ''
else left(@string,p-1)
end as delimvalue,
case when p=0 then len(@string)+1 else p end as m,
charindex(@delim,@string,
case
when p=0 then len(@string)
else p
end+1) as n
from (select charindex(@delim,@string) as p) b

insert into @res (delimvalue) select delimvalue from @t1

insert into @t2 (delimvalue, m, n)
select
case
when n=m+1 or len(@string)=m then ''
when n=0 then right(@string,len(@string)-m)
else substring(@string,m+1,n-m-1)
end as delimvalue,
case when n=0 then len(@string)+1 else n end as m,
charindex(@delim,@string,n+1) as n
from @t1
where m<=len(@string)

while exists(select * from @t2)
begin
delete @t1

insert into @t1 (delimvalue, m, n)
select delimvalue, m, n from @t2

insert into @res (delimvalue) select delimvalue from @t1

delete @t2

insert into @t2 (delimvalue, m, n)
select
case
when n=m+1 or len(@string)=m then ''
when n=0 then right(@string,len(@string)-m)
else substring(@string,m+1,n-m-1)
end as delimvalue,
case when n=0 then len(@string)+1 else n end as m,
charindex(@delim,@string,n+1) as n
from @t1
where m<=len(@string)
end
return
end