Printable View
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'.
Please help me. Thanx in advance.Code: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
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)
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.
Ok Jeba,
I understand.
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.
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
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.