the max function requires 1 argument(s)

I wrote this very simple SP in SQL 2008:

Create procedure dbo.GetNextID 
(
    @TableName nvarchar(50),
    @FieldName nvarchar(50)
)
AS
    BEGIN
        exec('select isnull(max('+@FieldName+'),0)+1 as NewGeneratedID from '+ @TableName);
    END

When I execute this procedure in Visual Studio SQL Express and pass a t开发者_运维百科able name and a field name, it works fine. But when I try to add this SP as a query in a QueryTableAdapter in my ADO DataSet, I receive this error before clicking on Finish button:

the max function requires 1 argument(s)

can anyone help me with this?

I guess that VS tries to determine a field list by executing the SP. But as it does not know what to pass to the SP, it uses empty parameters. Now, of course, your select statement fails.

You could try adding the following to your SP:

IF ISNULL(@TableName,'') = '' SET @TableName = '<Name of a test table>';
IF ISNULL(@FieldName,'') = '' SET @FieldName = '<Name of some field>';

Use the names of some field and table that do exist here (for example names that you'd use from your application, too).

Alternatively you could add the following above the exec:

IF (ISNULL(@TableName, '') = '') OR (ISNULL(@FieldName, '') = '')
BEGIN
    SELECT -1 AS NewGeneratedId
    RETURN 0
END

EDIT
On a side note, I'd like to warn you about concurrency issues that I see coming up from what your code does. If this code is supposed to return a unique ID for a new record in some table, I'd redesign this as follows:

Create a table NumberSeries where each row contains a unique name, a possible range for IDs and the current ID value.

Create a stored procedure that uses UPDATE ... OUTPUT to update the current ID for a number series and retrieve it in one step.

That way you can make sure that creating a new ID is a single operation that does not cause concurrency problems.