Hello, thanks to your help, my dbproc gets 'compiled' at least, but when I call it with 'call foo(1,'X') I get
---- Error ------------------------------- Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed General error;-4024 . call foo(1,'X') where 'all' from call is marked red. So I changed the dbproc in that way, that I get back the whole sqlstring (I had to delete 'Declare ' || $Cursor || and all other cursor-related things) to exclude this as a cause for this error. So I sorted out 2 errors within the creation of the SQLString. After that with my changed program I get back the correct sqlstring which shows me the correct results, if I copy&paste the string into a SQL-dialog. So I don't know why I get the error above, when I try to call my dbproc 'foo' and I can call all other dbprocs without any problems. Best Regards Peter -----Urspr�ngliche Nachricht----- Von: Anhaus, Thomas [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 11. Dezember 2003 11:38 An: '[EMAIL PROTECTED]' Cc: H�bschen, Peter Betreff: RE:Return cursor in dbproc with concatenated SQL-statement?? Peter H�bschen wrote : >Hello, >encouraged through >http://listserv.sap.com/pipermail/sapdb.general/2003-April/014359.html >I tried to create a dbproc with a concatenated SQL-statement to get back a >result-table. But somehow I didn't managed to get it work. I tried something >like this: >Create dbproc foo(In myvar1 smallint, in myvar2 varchar(3)) >Returns Cursor as >Var > SQLString Varchar(100); >$Cursor = 'foo_Cursor': >Begin > SQLString = 'Select * from mytable where ' > If myvar1 > 0 Then SQLString = SQLString || ' compvar1 = ' || myvar1; > If myvar2 <> '' Then SQLString = SQLString || 'compvar2 = ''' || myvar2; > >Declare :$Cursor Cursor for >Execute SQLString; >End; >In this case I get a 'Missing keyword Select' - Error at the >execute-command. It's obvious why, but I want to know if it's possible to >get a result-table back through a dbproc or something else, where the >SQL-Statement is dynamically build. >My DB-version is 7.4.3.30 on a Linux-Box >Thanks in advance >Peter This syntax is slightly different but the following should work : Create dbproc foo(In myvar1 smallint, in myvar2 varchar(3)) Returns Cursor as Var SQLString Varchar(120); $Cursor = 'foo_Cursor'; SQLString = 'DECLARE ' || $Cursor || ' Select * from mytable where ' if myvar1 > 0 Then SQLString = SQLString || ' compvar1 = ' || myvar1; If myvar2 <> '' Then SQLString = SQLString || 'compvar2 = ''' || myvar2; Execute SQLString; Best Regards, Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
