We help IT Professionals succeed at work.

Dynamic sql in stored procedure

spasol
spasol asked
on
Medium Priority
174 Views
Last Modified: 2010-05-02
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!
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
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
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Maybe defining the parameter as default can help?

CREATE PROCEDURE MaxID
    @ParmID integer =0 output
as ...

To be honest, i didn't yet debug stored procedures except using the Query Analyser...

CHeers

Author

Commented:
It didn't but thanks for all

Explore More ContentExplore courses, solutions, and other research materials related to this topic.