sp_executesql
-
i need to declare variables dynamic , then this my code for make that .but i can't used why???????????????????? Declare @TableName Char(100) Set @TableName = 'Ar_PromotionDetails' --{Columns}PromotionId Declare Cur_Sys_DataAdapterTable Cursor For Select Cln.name As [Columns] , Type.Name Types , Cln.Length , Cln.xprec , Cln.xscale From sysobjects Obj inner join syscolumns Cln On Obj.Id = Cln.id Inner join systypes Type On Type.xtype = Cln.xtype Where obj.id = object_Id(@TableName) Declare @ColumnName char(120) , @TypeName char(120) Declare @Length SmallInt Declare @xprec Tinyint , @xscale Tinyint Open Cur_Sys_DataAdapterTable Fetch Next From Cur_Sys_DataAdapterTable Into @ColumnName , @TypeName , @Length ,@xprec , @xscale Declare @DeclareVariable NChar(300) Set @DeclareVariable =N'' While @@fetch_status = 0 begin if ( LOWER(Rtrim(@TypeName)) in ( 'datetime' , 'bit' , 'int' , 'bigint', 'smallint' , 'tinyint' , 'bigint' , 'smalldatetime' , 'float') ) Set @DeclareVariable = Rtrim(@ColumnName) + space(5) + Rtrim(@TypeName) else if ( LOWER (Rtrim(@TypeName)) in ('numeric' , 'decimal' ) ) Set @DeclareVariable = Rtrim(@ColumnName) + space(5)+Rtrim(@TypeName) + N'('+ Rtrim(@xprec)+ N',' + Rtrim(@xscale) +N')' else Set @DeclareVariable = Rtrim(@ColumnName) + space(5)+Rtrim(@TypeName)+N'(' + Rtrim(@Length) + N')' Set @DeclareVariable = N'Declare @@' + @DeclareVariable + Char(13) Exec sp_Executesql @DeclareVariable print 'load ok...' Fetch Next From Cur_Sys_DataAdapterTable Into @ColumnName , @TypeName , @Length , @xprec , @xscale End Close Cur_Sys_DataAdapterTable deallocate Cur_Sys_DataAdapterTable Select @@PromotionId --when i use this command make error (Server: Msg 137, Level 15, State 2, Line 62 Must declare the variable '@@PromotionId'.) how i can resolve this problem?
123