Trouble with MAX in Stored Procedure

[JeffCarlin]

Here's the current code, where "@Parm1" is the passed table name:

BEGIN
SET @stg1 = 
"UPDATE " + @Parm1 + " " +
"SET dteLast =  " +
"(SELECT MAX(tblHealth.dteDateEntry) " +
"FROM tblHealth " +
"WHERE tblHealth.strHealthPatSSN=" + @Parm1 + ".strSSN " +
"AND tblHealth.strHealthPatTreatNum=" + @Parm1 + ".strTreat) " 
EXECUTE (@stg1)
END

Running this produces NULLs in the dteLast column. If I remove the MAX, the code works, but of course, only returns the first value found. Can anyone figure this out?

Thanks.

 

[mukund]

Hi JeffCarlin,

Try the following query, instead

SET @stg1 =
"UPDATE " + @Parm1 + " " +
"SET dteLast = " +
"(SELECT Top 1 tblHealth.dteDateEntry " +
"FROM tblHealth " +
"WHERE tblHealth.strHealthPatSSN=" + @Parm1 + ".strSSN " +
"AND tblHealth.strHealthPatTreatNum=" + @Parm1 + ".strTreat Order By tblHealth.dteDateEntry Desc) "
EXECUTE (@stg1)


Hope this helps,
Mukund.

 

[JeffCarlin]

Works like a charm. Many thanks Mukund.