Assign a string to a variable

Hi
I'm using a varchar variable (@.Cadena) to store a select statement.
--
declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
set @.quincena = 24
set @.anio = 2004
set @.numeroempleado = 589
SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
WHEN 1 THEN ImpQuin01
WHEN 2 THEN ImpQuin02
WHEN 3 THEN ImpQuin03
WHEN 4 THEN ImpQuin04
WHEN 5 THEN ImpQuin05
WHEN 6 THEN ImpQuin06
WHEN 7 THEN ImpQuin07
WHEN 8 THEN ImpQuin08
WHEN 9 THEN ImpQuin09
WHEN 10 THEN ImpQuin10
WHEN 11 THEN ImpQuin11
WHEN 12 THEN ImpQuin12
WHEN 13 THEN ImpQuin13
WHEN 14 THEN ImpQuin14
WHEN 15 THEN ImpQuin15
WHEN 16 THEN ImpQuin16
WHEN 17 THEN ImpQuin17
WHEN 18 THEN ImpQuin18
WHEN 19 THEN ImpQuin19
WHEN 20 THEN ImpQuin20
WHEN 21 THEN ImpQuin21
WHEN 22 THEN ImpQuin22
WHEN 23 THEN ImpQuin23
WHEN 24 THEN ImpQuin24
END As SumaSDOS
FROM HisMovimientosQnal
WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
CAST(@.NumeroEmpleado AS varchar)
+ ' AND TipoIncidencia = ''SDOS'')'
EXEC (@.Cadena) --Execute the select statement
--
Till this point, the result is shown correctly.
I would like to store that result into another variable.
How can I do it?Gonzalo Torres wrote:
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = '
+
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
Exec @.variable =(@.Cadena)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Look up the topic sp_ExecuteSQL in SQL Server Books Online. It has some
examples which shows how to assign the results to an output variable.
Also, just by glancing through the code, it seems like you might have a
better/easier way of retrieving the results without using Dynamic SQL. But
then without further knowledge of your table structures & business rules, it
is hard to tell.
Anith|||I don't know your table structure, but this thing looks suspiciously
de-normalized...
Anyways, you might look into sp_executesql instead of EXEC. Safer and
shouldn't require all the CASTs, plus you can get a little performance boost
out of it since it compiles the query plan for parameterized queries. Check
it out in BOL.
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:O4nRqYCJFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
>