0

I'm writting a big procedure in T-SQL with dynamic elements. To make it short for you I only post the important codelines.

declare....

BEGIN

while @start <= @rowcount
begin
  print @start
  print @rowcount

  set @sqlval = 'select @zwischen1 = @currentcolumn_val1
                 from z_skm
                 where id = @startid';

  set @ParmDefinitionval = N'@currentcolumn_val1 nvarchar(500),
                             @startid int,
                             @zwischen1 nvarchar(50) OUTPUT';

  exec sp_executesql
    @sqlval,
    @ParmDefinitionval,
    @currentcolumn_val1 = @currentcolumn_val,
    @startid = @start,
    @zwischen1 = @currentval OUTPUT;

  print @currentval


  set @sqlkey ='select @zwischen2 = @currentcolumn_key1
                from z_skm
                where id = @startid';

  set @ParmDefinitionkey = N'@Currentcolumn_key1 nvarchar(500),
                             @startid int,
                             @zwischen2 nvarchar(50) OUTPUT';

  exec sp_executesql
    @sqlkey,
    @ParmDefinitionkey,
    @Currentcolumn_key1 = @currentcolumn_key,
    @startid = @start,
    @zwischen2 = @currentkey OUTPUT;

  print @currentkey

  set @start = @start + 1
  print'loop-end'

end

END
GO

My problem is, that it always prints the same values. And the values are the ones which are passed at @ParmDefinitionKey and @parmdefinitionval into @currentcolumn_key1 and @Currentcolumn_val1.

I hope you guys can help out.

Edit:

Now it works. Here is the change in the code : 

set @sqlval = 'select @zwischen1 = '+@currentcolumn_val+' from z_skm where id = @startid';
SET @ParmDefinitionval = N'@startid int,@zwischen1 nvarchar(50) OUTPUT' ;
exec sp_executesql @sqlval,@ParmDefinitionval,@startid=@start,@zwischen1=@currentval OUTPUT;



set @sqlkey ='select @zwischen2 = '+@currentcolumn_key+' from z_skm where id = @startid';
set @ParmDefinitionkey=N'@startid int, @zwischen2 nvarchar(50) OUTPUT';
exec sp_executesql @sqlkey,@ParmDefinitionkey,@startid=@start,@zwischen2=@currentkey OUTPUT;

I dont know why, but it looks like that it is not possible to pass the dynamic_column via argument into the execute, it needed to placed into the string before.. This made absolutely no sense for me.

steve
  • 123
  • 1
  • 14
  • 1
    If `@currentcolumn_val1` and `@currentcolumn_key1` are supposed to represent dynamic column names, then [you cannot do that](http://stackoverflow.com/q/10092869/11683). – GSerg Nov 19 '14 at 10:59
  • '@currentcolumn_val1' and '@currentcolumn_key1' represent the dynamic column values which stay the same for all loops. The '@startid' var changes. '@currentkey' and '@currentval' should print the single line value from the select statement – steve Nov 19 '14 at 11:20
  • I don't understand your "dynamic column values." If `@currentcolumn_key1` contains name of the column, then you cannot do that and should use a workaround from the link. If `@currentcolumn_key1` does not contain the name of the column, then your whole query does not make sense and can be replaced with `set @var1 = @var2`. – GSerg Nov 19 '14 at 12:36
  • Greg see my edit. Is this what you wanted to teach me? – steve Nov 19 '14 at 14:01
  • 1
    steve, if you want to 'ping' another user in a comment, you can do it using the '@' symbol like this: @GSerg, see my edit. Is this what you were trying to explain to me? – AHiggins Nov 19 '14 at 14:29

1 Answers1

3

Try to change your queries like this,

set @sqlval = 'select '+@zwischen1+' = '+@currentcolumn_val1+'
                 from z_skm
                 where id = '+@startid;

I think @zwischen1 is variable that hold the Column name

Dony George
  • 175
  • 7