Dynamic SQL String

I need to write a SQL String to retrieve a field value from a table. The problem is that I need to supply the table name as a parameter. If I was just updating a table, I could build a dynamic SQL String and use Exec()

This is what I would write if the name of the table was known:

Select @.RecordNo = MAx([VehicleID]) from Alto

This is what my dynamic SQl string looks like:

Select @.SqlStatement = 'Select Max([VehicleID]) from ' + @.TableName

So how do I run this statement and get the value it would return? Is there an equivalent to exec() that returns a value?look at
sp_execute|||Thanks for the reply.

Do you mean sp_executesql?

I can't see that I can get a value returned by using this|||Let me rephrase that...

I can't see how I can return a value into a local variable by using sp_executesql|||This doesn't generate any errors, but doesn't assign @.recordNo with a value. Am I anywhere close with this?

Select @.SqlStatement='Select Max([VehicleID]) From ' + @.TableName
exec @.RecordNo = sp_executesql @.Sqlstatement|||try this :

declare @.tbl as nvarchar(20),@.stmt as nvarchar(100)
declare @.maxid int
set @.tbl='users'
select @.stmt='select max(userid) from ' + @.tbl
exec @.maxid=sp_Executesql @.stmt
print @.maxid

hth|||oops yes sorry I dropped the sql bit!|||You have used different variable names, but apart from that I am doing exectly the same as you. Should this work?|||yes i just tried it on my tables...so you should change the table names and column names...it works for me.

hth|||Yes I agree that a value gets printed. But it doesn't come from the last line in your example. It comes from the execution of the exec statement.

I also get a value printed but the variable assignment doesn't take place. No matter what I try, I get get the assignment to happen.|||are you trying to get the id into asp.net ? you can always get the id like this :

dim MySQL as string = "select max(userid) from " & tbl
...
intid=cms.executescalar()
...

and send the tablename dynamically
and send in the table name...though its not completelyt safe...i cant think of anything else...

hth|||No i'm not trying ti get the Id into asp.net, well not directly. When the user creates a new record, I need to give it a RecordNo so it can be referenced later. So all i'm trying to do here is find the last record in the table and add 1 to it. The trouble is that the same sp will be used to work with several tables. So I need to construct this dynamic Sql string.|||you can do a select max(userid) from the table but that will not always be accurate...you can get an id of 35 ( for xample) but if some other user just made an insert while you are querying for the maxid...you can get the new id after the insert...using SCOPE_IDENTITY() function..

hth|||We seem to be losing the point here. I have no problem querying the table if the table name is fixed.

I need to be able to specify the table name in a parameter supplied to the sp. This is where I am stuck.|||I've been stuck on this problem for a week now. I need to get it sorted out. I've put the offending code into a sp of it's own. Here it is:

CREATE PROCEDURE GetNextRecordNo

@.TableName nvarchar(15)
AS

declare @.SqlStatement nvarchar(100)
declare @.RecordNo int

Select @.sqlstatement = 'Select max(VehicleID) from ' + @.TableName

execute @.RecordNo = sp_executesql @.sqlstatement

If @.RecordNo = Null select @.RecordNo = 0

select @.RecordNo=@.RecordNo+1

Return (@.RecordNo)
GO

From my main sp I am calling the above sp like this:

execute @.recordNo=GetNextRecordNo 'Alto'

If the table in question has a maximum Vehicle ID of 10 then the value returned is also 10. The only explanation for this is that the Execute command half way down the sp is what is actually returning the value.

What am I doing wrong?