CODEWITHSUNDEEP
sqlsql-server-2005
David.Chu.ca
David.Chu.ca

Reputation: 38654

Generic SQL to get max value given table and column names as varchar

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?

Upvotes: 1

Views: 1770

Answers (5)

dkretz
dkretz

Reputation: 37655

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

Upvotes: 5

Lobstrosity
Lobstrosity

Reputation: 3936

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

Upvotes: 0

BC.
BC.

Reputation: 24918

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

Upvotes: 6

eglasius
eglasius

Reputation: 36037

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.

Upvotes: 0

Rob Lachlan
Rob Lachlan

Reputation: 14459

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

SELECT MAX(ID) FROM mytable

Upvotes: 0

Related Questions