&SQL in Cache Server Page

Does your where clause need a colon in front of pVariable ?

where COLUMNC = :pVariable

If that doesn't fix it, I suggest you first check whether the problem is caused by the &sql(...) or by the way your web page is calling your server-side method.

Remove all your code between the opening &sql( and the closing ) - incidentally, I assume you have a closing ) even though it didn't show in your post.

Make your method end with QUIT pVariable instead of returning tempVariable1.

Do you still get a Javascript error?

Hello Rajath,

In addition to what John said, it is always advisable to check the value of SQLCODE after running an embedded SQL statement. This will tell you if the SQL statement executed successfully, or encountered an error. If it encountered an error, you can get more details from the %msg variable. See the documentation here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

You said that you are getting a JavaScript error. What is the error message? Can you send the JavaScript code you are using to call the retrieveDetail() function?

There is also comma after the ColumnA column, which causes invalid SQL. You should also check value of SQLCODE variable, because ouput host variables are valid only for SQLCODE=0 as described in Caché documentation:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

"It is good programming practice to check the SQLCODE value immediately after exiting Embedded SQL. Output host variable values should only be used when SQLCODE=0."

So the code should look as follows:

<script language="cache" method="retrieveDetail" arguments="pVariable" returntype="%String">
 &sql( select ColumnA into :tempVariable1 from MyTable where COLUMNC = :pVariable)
 If SQLCODE=0 { QUIT tempVariable1}
 Else {quit ""}</script>

Will it help?