asked on

Dynamic sql in stored procedure

I work in SQL Server 2000 and VB 6

I use sp to find a max value of specific column "ID"
in one specific table "Street"

CREATE PROCEDURE MaxID
@ParmID integer output
as
begin
select @ParmID = max(ID) from Street
end
GO

Because I have a lot of table with a same column and I want to look for
max value of "ID" column in those tables I want to use dynamic sql as
follows
select @ParmId = max(ID) from @TableName
where @TableName is input parm for sp MaxID

Question is: HOW?
Please help!

Guy Hengel [angelIII / a3]

CREATE PROCEDURE MaxID
@Table nvarchar(100),
@ParmID integer output
as
exec sp_executesql N'select @p = MAX(ID) FROM ' + @Table , N'@p integer OUTPUT, @ParmID OUTPUT

Cheers

spasol

ASKER

Thanks but you made a little mistake excluding one apostroph ' after N'@p integer OUTPUT but I figure out
how to skip this problem

I have further question.

How to debug sp in SQL Server
I use Object Browser where I Debug sp. I dont now how to debug sp
when I have a output parameter in sp.
Debug procedure window treat output parameter in sp as input/output and
ask me for Value. If I don't put some Value (what is obvious) because
with this parameter I expect value from sp I get Message from Server

"...[SQL Server]Procedure 'MaxID' expects parameter '@ParmID',
which was not supplied "

Of course Angellll I would give you more points for this
Thanks

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spasol

ASKER

It didn't but thanks for all