Q: generic SQL to get max value

Question

it is very easy to use the following SQL to get value for a specific primary key: ID from a specific table: myTale:

DECLARE @v_maxID bigint;
SELECT @v_maxID = MAX(ID) FROM myTable;

What I need is a generic SQL codes to get the max value for a key from a table, where both key and table are specified as varchar(max) types as parameters:

DECLARE @v_maxID bigint;
-- SELECT @v_maxID = MAX(@p_ID) FROM @p_Table;

I comment out the SELECT since it is not working. I tried to build a SQL string and I can EXEC it, but I cannot get the max value back to my local variable(@v_maxID). Any suggestions?

Answer 1

Correct me if I'm wrong, but don't you just want:

SELECT MAX(ID) FROM mytable

Answer 2

Just build the query at the app level, thus the query running would be just like the one above. Doing in on sql will certainly open you for sql injection, since you have to use exec(). Also in either case, be careful with user input.

Answer 3

DECLARE @max bigint, @sql nvarchar(max)
SET @sql = N'SELECT @max = MAX(' + @p_ID + ') FROM ' + @p_Table

EXEC sp_executesql 
    @query = @sql, 
    @params = N'@max bigint OUTPUT', 
    @max = @max OUTPUT 

PRINT @max

Answer 4

As BC states, you have to use sp_executesql with an OUTPUT parameter.

How to specify output parameters when you use the sp_executesql stored procedure in SQL Server

Answer 5

Users are choosers, but I consider this an ugly idea (for being overgeneralized). And unoptimizable. Just write the SQL.

Answer 6

Great! I got it working.

One more question related question to this issue, if I do this in a similar way:

SET @sql = N'INTERT INTO #temp_Table' + @others;
EXEC sp_executesql ...
-- EXEC @sql -- or this way

Can I access to the data in the temporary table #temp_Table after sp_executesql?