apply the result of a query that includes variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

apply the result of a query that includes variable

Author

Topic

louradwan
Starting Member

5 Posts

Posted - 2008-06-19 : 12:25:09

The following query is failing when trying to apply the
MAX(field_x_order)
to the variable @max
Note the x is represented by the string variable @stri

declare @i int
declare @stri varchar(10)
declare @max int
set @i = 18
set @max = 0
while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
select @max = MAX(field_ + @stri + _ORDER) FROM table_name WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near the keyword 'IS'.
exec ('UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL')
end

I have also tried:
select MAX(field_ + @stri + _ORDER) INTO @max = FROM display_1a WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near '@max'.

and:
select @max = ('SELECT MAX(field_' + @stri + '_ORDER) FROM display_1a WHERE field_' + @stri + ' IS NOT NULL') -- error: Conversion failed when converting the varchar value 'SELECT MAX(field_19_ORDER) FROM display_1a WHERE field_19 IS NOT NULL' to data type int.

Thanks,
Lou

Lou

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:32:29

You cant use variables directly like this. you need to use dynamic sql. something like:-

declare @i int
declare @stri varchar(10),@Sql varchar(8000)

set @i = 18

while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL; 
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

EXEC(@Sql)

end

louradwan
Starting Member

5 Posts

Posted - 2008-06-19 : 12:44:00

thanks for the reply. I tried running the query as it is, and got the following error:

Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@max".

quote:

Originally posted by visakh16

You cant use variables directly like this. you need to use dynamic sql. something like:-

declare @i int
declare @stri varchar(10),@Sql varchar(8000)

set @i = 18

while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL; 
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

EXEC(@Sql)

end

Lou

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:47:10

quote:
Originally posted by louradwan

thanks for the reply. I tried running the query as it is, and got the following error:

Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@max".

quote:
Originally posted by visakh16

You cant use variables directly like this. you need to use dynamic sql. something like:-

declare @i int
declare @stri varchar(10),@Sql varchar(8000)

set @i = 18

while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

EXEC(@Sql)

end

Lou

Are you sure you gave the declare inside @Sql variable as i did?

louradwan
Starting Member

5 Posts

Posted - 2008-06-19 : 12:52:57

Yes, I copied the query out the window and pasted it directly in my query window. I did notice that there was no single quote at the end of that line (in red):

set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

quote:
Originally posted by visakh16

quote:
Originally posted by louradwan

thanks for the reply. I tried running the query as it is, and got the following error:

Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@max".

quote:
Originally posted by visakh16

You cant use variables directly like this. you need to use dynamic sql. something like:-

declare @i int
declare @stri varchar(10),@Sql varchar(8000)

set @i = 18

while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

EXEC(@Sql)

end

Lou

Are you sure you gave the declare inside @Sql variable as i did?

Lou

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:59:32

its a single line with multiple sql statements seperated by ;. Thats for dynamic sql execution. just copy it as it as and try executing.

set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

louradwan
Starting Member

5 Posts

Posted - 2008-06-19 : 13:05:59

When I executed the exact query you gave me, I got this error:

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@max".

Thanks,
Lou

quote:
Originally posted by visakh16

its a single line with multiple sql statements seperated by ;. Thats for dynamic sql execution. just copy it as it as and try executing.

set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL'')'

Lou

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 13:20:10

change like this and try:-

set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL; 
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order '' + @max + ''WHERE field_' + @stri + ' IS NULL'')'

you could use Print(@sql) instead of Exec at first to see if its buiding correct sql string before applying Exec.

louradwan
Starting Member

5 Posts

Posted - 2008-06-19 : 13:44:57

Thank you for the help. Adding a + to the query where I was adding the variable @max to the column value fixed the errors I was having...
Lou

quote:
Originally posted by visakh16

change like this and try:-

set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order '' + @max + ''WHERE field_' + @stri + ' IS NULL'')'

you could use Print(@sql) instead of Exec at first to see if its buiding correct sql string before applying Exec.

Lou

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 13:51:23

quote:
Originally posted by louradwan

Thank you for the help. Adding a + to the query where I was adding the variable @max to the column value fixed the errors I was having...
Lou

quote:
Originally posted by visakh16

change like this and try:-

set @Sql='declare @max int;
set @max = 0;
select @max = MAX(field_' + @stri + '_ORDER) FROM table_name WHERE field_' + @stri + ' IS NOT NULL;
exec (''UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order '' + @max + ''WHERE field_' + @stri + ' IS NULL'')'

you could use Print(@sql) instead of Exec at first to see if its buiding correct sql string before applying Exec.

Lou

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources