V9 - Executing stored procedures whilst explicitly naming the paramaters....

Paul_York

Hi,

 

Just wondering if anyone has found any workaround to this...

 

We used to use the %SelectSQL function to execute data retrieval stored procedures however we'd always name the paramaters that we were passing in as we don't like to rely on implicit ordering and sometimes certain parameters can be optional.  This means that we could have a sp call in the manner of "EXEC sp_get_user_list @city='London', @postcode='NW1 5ET'.  This sp may have an optional @usere_;id paramer for finding the user by key which in this case we wouldn't be providing.

 

In V9 you set parameters to MBO's using the @ symbol which means that these explicitly listed parameters are now replaced automatically and the resulting SQL is wrong.

 

I'm going to try copying out the command to notepad and amending the SQLArg's that are created as I'm hoping these are just type aware replace functions.  It's tedious and it may work but I'd love to hear how others are or would do this?

 

Thanks,

 

Paul.

 

 

Paul_York

OK I've tried a couple of ways and both have failed....  

 

The first attempt was to change the string used for the parameter to something other than one use the @character...

 

SelectSql(new MetastormDefault(),
"EXEC OM_SaveCustomer @Title=|Title|,@Firstname=|Firstname|,@Surname=|Surname|,@Email=|Email|",
SQLArg("|Title|",Local.Title),
SQLArg("|Firstname|",Local.Firstname),
SQLArg("|Surname|",Local.Surname),
SQLArg("|Email|",Local.EmailAddress),
)

 

I then wondered if the @ but alas this gave no joy either...

 

 

SelectSql(new MetastormDefault(),
"EXEC OM_SaveCustomer @@Title=@Title,@@Firstname=@Firstname,@@Surname=@Surname,@@Email=@Email",
SQLArg("@Title",Local.Title),
SQLArg("@Firstname",Local.Firstname),
SQLArg("@Surname",Local.Surname),
SQLArg("@Email",Local.EmailAddress))

 

 

Does anyone have any other ideas or should I start reading the manual and seeing if this is covered there?

Thanks,

Paul.