What's wrong in this code?

[jeba]

Hi there!
I am getting the following error when I run the following SP.

Server: Msg 137, Level 15, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@maxid'.

CREATE  Procedure spFindMax @tablename varchar(50), @pykey varchar(50)  As 
Declare @SqlStr varchar(1000) , @maxid int
set @SqlStr ='Select @maxid=ISNULL(max('+ @pykey  + '),0) From ' + @tablename
Exec (@SqlStr)
print @maxid

Please help me. Thanx in advance.

[Venkrishna]

Dear Jeba,

Well, I have tested ur proc. I have removed the @maxid from the
code and executed it. It works fine.

CREATE Procedure spFindMax @tablename varchar(50), @pykey varchar(50) As
Declare @SqlStr varchar(1000)
set @SqlStr ='Select ISNULL(max('+ @pykey + '),0) From ' + @tablename
Exec (@SqlStr)

[jeba]

Dear Venkrishna!
well, thanks for your reply. I wanted to use @maxid as the OUTPUT parameter, so that I can eliminate creation of a recordset.

[Venkrishna]

Ok Jeba,

I understand.

[jeba]

Hi VB_DBA!
Thanks a lot for the reply. Sorry for disturbing you again...
Instead of printing, can you please return the @maxid as the OUTPUT parameter? I've tried to assign the value of @maxid to another variable, but it is giving an error:

Couldn't assign a varchar value to int datatype.

Please help.

[vb_dba]

Do you have to have it returned as an output parameter? You could do the following and it would return the MaxID as a recordset:

CREATE Procedure spFindMax @tablename varchar(50),
@pykey varchar(50) As

Declare @SqlStr varchar(1000)
Set @SqlStr = ''

set @SqlStr = @SqlStr + 'Select ISNULL(max(' + @pykey + '),0) '
set @SqlStr = @SqlStr + 'From ' + @tablename

Exec (@SqlStr)

Other than doing it this way, I don't believe you are going to be able to use an Output parameter with this type of stored proc.

Chris

[jeba]

Hi Chris!
It would be better(..the best) if I could return it as Output parameter, because I have to call this Stored procedure in many places. So I feel opening a recordset for each instance is a bit costly. Why can't I assign the @maxid variable to the output parameter?

Pls help.