MS SQL Server - T-SQL Statement

Author

T-SQL Statement

kuhrty

2006-10-24, 6:28 pm

I have written statement like this in the past but I am forgetting
something. I keep getting an error at the first equal sign. I am
trying to assign a value to variable.

Any help is appreciated.

declare @Calc_Start_Column varchar(2), @cnt int

set @cnt = 1

exec('SELECT ' + @Calc_Start_Column + ' = Calc_Start_Column_Ye
ar_'+
@cnt +'
FROM Inv_Calc
WHERE Inv_Month = 8 and Firm_Month = 1 and build_type = 1')

print @Calc_Start_Column

Ken

2006-10-24, 6:28 pm

kuhrty wrote:
> I have written statement like this in the past but I am forgetting
> something. I keep getting an error at the first equal sign. I am
> trying to assign a value to variable.
>
> Any help is appreciated.
>
>
> declare @Calc_Start_Column varchar(2), @cnt int
>
> set @cnt = 1
>
> exec('SELECT ' + @Calc_Start_Column + ' = Calc_Start_Column_Ye
ar_'+
> @cnt +'
> FROM Inv_Calc
> WHERE Inv_Month = 8 and Firm_Month = 1 and build_type = 1')
>
> print @Calc_Start_Column

@cnt must be alphanumeric in the string

convert it like + convert(varchar(10),
@cnt) +

Hugo Kornelis

2006-10-24, 6:28 pm

On 17 Sep 2006 14:14:58 -0700, kuhrty wrote:

>I have written statement like this in the past but I am forgetting
>something. I keep getting an error at the first equal sign. I am
>trying to assign a value to variable.
>
>Any help is appreciated.
>
>
>declare @Calc_Start_Column varchar(2), @cnt int
>
>set @cnt = 1
>
>exec('SELECT ' + @Calc_Start_Column + ' = Calc_Start_Column_Ye
ar_'+
>@cnt +'
>FROM Inv_Calc
>WHERE Inv_Month = 8 and Firm_Month = 1 and build_type = 1')
>
>print @Calc_Start_Column

Hi kuhrty,

The best advise would be to modify your table design. Instead of having
column names Calc_Start_Column_Ye
ar_1, Calc_Start_Column_Ye
ar_2, etc,
you should move these columns to a new table, with 1, 2, etc as values
in a new column (that is part of the key), and Calc_Start_Column_Ye
ar as
the data column. Your current design is not normalised, and forces you
to use dynamic SQL, with all downsides and problems that brings.

For a quick kludge, you'll have to use sp_executesql instead of EXEC().
The latter executes in it's own scope, so variable assignments made
within it are lost as soon as you return to the outer scope. The SQL in
an sp_executesql call will also execute in it's own scope, but yoou can
at least use pre-defined paramters to pass values in and out of that
inner scope.

More info: http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP

Sponsored Links