July 25, 2016 at 9:22 am
1.) I am trying to go through every table in the CLAIM schema that has a column SOURCEID
2.) Then I want to find the max value in the column SOURCEID
3.) Just PRINT the table name and the MAX(SOURCEID) value
For now.. that's it what i need. But my code needs some syntax help inside the loop. That EXEC statement
does not get the MAXX value to a variable. Please help
if object_id('tempdb..#t') is not null DROP TABLE #t;
Select Distinct 'CLAIM.' + T.name as name , IDENTITY( int, 1 ,1 ) as ID INTO #t
FROM sys.tables T
inner join sys.columns C on ( T.object_id = C.object_id )
Where T.schema_id = 6
and C.name = 'SourceID';
Declare @s-2 varchar(max);
Declare @i int, @max-2 int
Declare @tab varchar(1000);
Declare @res VARCHAR(100);
Select @max-2= COUNT(*) FROM #t;
Set @i=1;
While( @i <= @max-2 )
Begin
Select @tab=name from #t where ID = @i;
Select @s-2 ='Declare @res VARCHAR(100); Select @res=MAX(SourceID) FROM ' + @tab
/* Need Help */
Exec(@s);
PRINT @res
Select @i=@i+1;
End
July 25, 2016 at 9:38 am
You have 2 variables named @res. One inside the dynamic code, and one outside. The one inside the dynamic code is lost and the one outside is printed but never gets a value assigned. You need to use parameters on your dynamic code if you want to do something other than print the value. Or you could include the print statements in your dynamic code.
Declare @s-2 nvarchar(max);
Declare @i int, @max-2 int
Declare @tab varchar(1000);
Declare @res VARCHAR(100);
Select @max-2= COUNT(*) FROM #t;
Set @i=1;
While( @i <= @max-2 )
Begin
Select @tab=name from #t where ID = @i;
Select @s-2 ='Select @res=MAX(SourceID) FROM ' + @tab
/* Need Help */
Exec sp_executesql @s-2, N'@res VARCHAR(100) OUTPUT', @res OUTPUT;
PRINT @res
Select @i=@i+1;
End
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply