CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
user1570210
user1570210

Reputation: 1219

Dynamic SQL to pass table name and return Max ID value

Here is what I am trying to do I have list of tables(50 of them) and in all the tables I have one common column called "ID", what I want to do is pass table name that will dynamically return my MAX(ID) value. i need the MAX(ID) value since I will be using this value for other stuff.

So I was thinking of creating a function where I will pass table name and it will return me MAX(ID) value and then I can use that function in my other select statements.But the problem here is SQL functions does not support dynamic SQL

Any suggestion on what other options do I have, remember I need the max(id) value in a SQL.

Upvotes: 2

Views: 3467

Answers (5)

Meredith Poor
Meredith Poor

Reputation: 351

create function [dbo].[LastIdOf](@TableName varchar(50)) returns int
as
begin
   declare @LastId int;
   select @LastId = convert(int, i.last_value) from sys.tables t inner join sys.identity_columns i on t.object_id = i.object_id where t.name = @TableName;
   return @LastId;
end

Upvotes: 4

Rizwan Fancy
Rizwan Fancy

Reputation: 1

select 
'select max('+z.COLUMN_NAME +')' +' from '+z.TABLE_NAME  from (
SELECT u.TABLE_NAME,u.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
left join INFORMATION_SCHEMA.TABLES t on u.TABLE_NAME = t.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND u.TABLE_NAME = t.TABLE_NAME) as z

Upvotes: 0

vamosrafa
vamosrafa

Reputation: 695

Try something like this:

select *
from TABLE A
where id = (
  select max(id)
  from TABLE
  where Table.key = A.Table.key
);

Upvotes: 0

JeffO
JeffO

Reputation: 8043

create proc procTableMaxID (@Tablename varchar(100)
as
DECLARE @SQL AS nvarchar(500)

SET @SQL = 'SELECT Max(ID) from ' + @Tablename


EXECUTE sp_executesql @SQL

Procs don't offer the flexibility to be used as a table or in a select statement, but there are ways around that.

Upvotes: 0

user2480011
user2480011

Reputation: 46

Why don't you create a View and do a MAX(ID) on that?

Upvotes: 0

Related Questions