XLCubed XL3ExecuteSQLProc error

First of al this is a RTFM if you know where to look ;-).


Consider the following (simplified) procedure)


CREATE PROCEDURE uspGetNewId
@UserName varchar(250)
AS
BEGIN--
-- Some more magic
--
SELECT NEWID() INTO TBL_LOG
END


If you add =XL3RunSqlProc(B1,1,"uspGetNewId",XL3UserName()) to your sheet you get the following error:

Procedure or function 'uspGetNewId' expects parameter '@UserName', which was not supplied.
The problem is dat XLCubed access the database with hardcoded parameters: @Parameter1,@Parameter2,@Parameter3....


There are 3 approches for fixing this:
1
Rewrite or wrap your procedure like this, using the default parameters :


ALTER PROCEDURE uspGetNewId

@Parameter1 varchar(250)




AS

BEGIN


--

-- Some more magic

--

SELECT NEWID() INTO TBL_LOG




END





2
Use XL3RunSQL instead and compose your own SQL string:
=XL3RunSql(B2,1,"EXEC dbo.uspGetNewId @UserName='"&XL3UserName() &"'")


3
Add a dedicated ODBC connection (see manual)


Till Next Time