set variable in stored procedure

can I set a value to a variable when the table name and the column are variable? I tried this way and got an error...

select @cur_max_value = MAX(@cur_field) FROM @cur_table

You could try making the right hand side a function and then using

select @cur_max_value = SELECT * FROM dbo.myMAXfunction(@cur_field, @cur_table)

I've used a sql_variant datatype below because I don't know what you might be maxxing, and you don't have to use a table function (i think) but it's the first thing I thought of.

CREATE FUNCTION dbo.myMAXfunction
(
@cur_field nvarchar(200),
@cur_table nvarchar(200)
)
RETURNS @myTable TABLE
(
[maxcol] sql_variant
)
AS
BEGIN
INSERT @myTable
SELECT MAX(@cur_field) FROM @cur_table
RETURN
END

I tried to run the following function (in query analyzer):


But I'm getting this error: "Must declare the variable '@cur_table'."

remove the AS after each variable name and see what happens. I'd also recommend putting dbo. in front of all sps and udfs

CREATE FUNCTION dbo.GET_MAX_VALUE
(
@cur_field VARCHAR(50),
@cur_table VARCHAR(50)
)

Try this

DECLARE @sQuery nvarchar(200)

SET sQuery = 'SELECT MAX(' + @cur_field +') FROM ' + @cur_table

EXEC (@sQuery)

Try this:

Are you using the database with an application or a website? If so this becomes a lot easier because you can return the value back to the application and store it in a variable there. Otherwise I'm not sure it can be done using pure sql server.