Interacting with stored procedures (MSSQL)

I have a need to call existing stored procedures in two scenarios:

1) The stored procedure returns the value I need via its return code
2) The stored procedure has OUTPUT parameters to return the values

In this environment, I do not have permission to modify these stored procedures nor create additional SQL objects. Is there a way to use the conn:execute() to retrieve data in these scenarios? Are there any other suggestions on how to accomplish this?

Thanks,
Ron

Hey Ron,

Welcome to the Iguana community – does this URL answer your questions?:

http://wiki.interfaceware.com/132.html

No; I had already seen that. It shows how to make a stored procedure call and get the result set(s) that are returned, but not how to capture the return code or output parameters.

Hmmm. I guess this may be a gap in the APIs we offer.

I’m not a stored procedure guru – is it possible to work around the gap by wrapping a stored procedure with another one which can collect this information and return it as a result set?

It would be if I had permission to create objects in this database; I only have datareader/datawriter and can’t create any new stored procedures, tables or functions. I could create an external program (probably in C++ or Python) to call the SP and return the values, but that just doesn’t feel right.

Couple of other possibilities would be if we were to assist you in putting together a Lua extension DLL.

That can be written in C++ which opens up a whole load of options in terms of what libraries one can use. The linking aspect is something we could you help save time with in terms of getting that the right settings.

Another possible solution path is to see if we can tweak one of the other third Lua database extension DLLs to work within the translator.

Are you building bindings into your own application? Who owns the database that you are interacting with? What is the timeline?

These may be questions you prefer to take offline – we can always share the results of the final solution in this forum.

For return values, you could do the following:

SqlStr = ‘declare @retval int; ‘
..’exec @retval = sp_abc ‘..Param1..’; ‘
..’select @retval;’
RetVal = conn:execute{sql = SqlStr, live = true}
RetValNum = tonumber(RetVal[1][1]:S())

Similarly, output parameters could be returned in the resultset of the SQL.

It never occurred to me to put multiple sql statements in the string without using a wrapper stored procedure. Seems obvious now. Thanks!

I created this for it:

function spWrapper(DB,SQL,live)
   --[[
   This assumes that SQL does not contain the
   EXEC keyword and is not in ODBC CALL syntax
   --]]
   --Create wrapper SQL
   local mSQL=[[declare @rc INT; EXEC @rc=]]
              ..SQL..[[;SELECT @rc as rc]]
   --Execute wrapper SQL
   local rc, rs = DB:execute{sql=mSQL,live=live}
   --Capture the return code as a number
   if live then
      rc=tonumber(rc[1][1]:nodeValue())
   else
      rc=0
   end
   --Remove the extra result set
   rs[#rs]=nil
   --[[
   Return the return code plus all the
   normal returns of db:execute()
   --]]
   return rc, rs[#rs], rs
end

Example call:

local DB=db.connect{...}
   local retval, lastresultset, resultsets = spWrapper(DB,[[sp_TestProc 'arg1', 'arg2']])
   DB:close()

I haven’t implemented anything for the OUTPUT parameters yet, but it would be similar.