Variable Problem with Simple Function

[BDRichardson]

Please would someone advise as to whether it is possible to do what I am attempting to with the following function? If so, would you kindly explain what I am doing wrong?

I simply want to declare table and column names to a function, and in return, get the next free number for the column, i.e. similar to an AutoNumber column, but controlled through a program using a function.

The function is:

CREATE FUNCTION fnNextIdFromTable( @TableName VARCHAR( 128), @IdColumnName VARCHAR( 128))

RETURNS INT

AS

BEGIN 

DECLARE @NextId INT

SELECT
	@NextId = MAX( @IdColumnName) + 1
FROM
	@TableName

RETURN @NextId

END

 

[JamesLean]

No, you can't do that. To use a variable in place of a table name you have to use dynamic SQL:

DECLARE @t sysname
SET @t = 'mytable'
EXEC('SELECT * FROM ' + @t)

But you can't use dynamic SQL inside a function

You could put this functionality inside a stored proc, but why don't you just use an IDENTITY column and save yourself the bother?

--James

 

[BDRichardson]

That blows my ideas out of the water then! I had a feeling it was going to conclude to this.

I cannot change the design of the table, so I will have to control the value through my program. Unfortunately, this is not my preferred method since.

Many thanks for your guidance. Much appreciated.

 

[donutman]

If you are doing what I think you are doing (returning Max(ID) to app prior to insert), then in a multi-user environment that technique would have been problematic. You may want to describe your situation more fully in order to get other alternatives.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]