Can Wappler handle MSSQL store procedures and how about advanced queries?

[raymantle]

So, I’m trying to move away from Dreamweaver and have a question in relation to MSSQL Store Procedures and advanced queries.

Can Wappler handle MSSQL Store Procedures? If it can, how do I perform these actions?

How do I perform advanced queries, especially when I need to add CASE as in the example below?

SELECT CASE tblHi5.intApprovedRejectStatus
           WHEN 0
           THEN 'Declined'
           WHEN 1
           THEN 'Approved'
           WHEN 999
           THEN 'Purged'
           ELSE 'Awaiting decision'
       END AS intApprovedRejectStatus
FROM tblHi5
     INNER JOIN tblEmployees ON tblHi5.intEmployeeID = tblEmployees.intEmployeeID
     INNER JOIN tblEmployees AS tblEmployees_1 ON tblHi5.intIssuerID = tblEmployees_1.intEmployeeID
WHERE(tblEmployees_1.intCompanyID = varintCompanyID);

Any info would be great.
Thanks,
Ray.

[Freddy_Blockchain]

not 100% sure but I think I‘ve opened already a smiliar topic based on mysql and as Stored Procedures arent same on MSSQL/MySQL and even their functions are DB related its not implemented.

I never used stored procedures, but what about triggering your cmd and handle all on DB side? It would also keep logic completely constrain

[raymantle]

@Freddy_Blockchain, thanks for the reply.

Can you tell me how I would trigger my cmd and pass parameters from Wappler to MSSQL?

Not sure if I’m asking the right questions, but won’t learn if I don’t ask. lol.

Thanks.

[TomD]

There are limitations when it comes to using advanced queries in Wappler. Though I hope this will be addressed at some point, there are alternative approaches which may overcome these limitations.

In this case I would using a calculated/generated column in MySQL (which I think is the same as a computed column in MSSQL). Sometimes you may need to use views. In other situations (which might be another option in your example), you can get the value you need in the front end - eg using a nested ternary operator (for your example).

[Teodor]

Stored procedures are not supported. But as Tom explained, you can just return the results as “0”, “1” and “999” on the page and then format them as needed using the data formatter.

[raymantle]

Stored procedures

Is there any plan to support Stored Procedures or is there an alternative which I should be looking at?

Thanks,
Ray.

[nevil]

Stored procedures are not supported.

Not yet? Or will this never be implemented?

We also would like to have this functionality, just talked to my developer about this.

[George]

We don’t have plans to support stored procedures yet.

This is mainly because stored procedures can return all kinds of different data and there is no predefined output. So we can not show any data to pick from in Wappler’s data bindings pickers.

Also most of the functionality of stored procedures can be achieved with the regular query builder already. So there is a very little need to support them.

[Antony]

Hello everyone…

I am feeling rather distressed to have found this post…

I had assumed that Wappler would be able to support calls to stored procedures, and before starting my Wappler trial I have spent weeks creating them in MySQL. My database structure is very complex, often with a mass of database activity happening when one button is clicked… and I really need to create, test and manage it all using procedures within MySQL.

The key reasons for using stored procedures are:

• Speed of execution
• Speed and ease of debugging
• The procedure based approach where no piece of code needs to be written more than once.

I am only looking to use stored procedures for data creation, so with inputs, but not necessarily to return values.

I am assuming that I will be able to call stored procedures, but I will need to write my own php to do that?

Does anyone have experience of doing this?

@George, it is sounding like even input-only stored procedures are no on the roadmap right now… is that correct?

Best wishes,
Antony.

[Antony]

Hang on though… can I use a custom query like this:?

[mebeingken]

Stored procedures can be used in the custom query. I use them myself.

[Antony]

Oh fab… looks like we posted at the same time Ken!

Worry over… it is quite a struggle working out what Wappler can do and what it can’t!

[mebeingken]

Using Wappler you can do most anything…just comes down to what it natively supports in the UI and what needs to be done elsewhere – primary reason I love it.

On the SP, the call command goes in the query area rather than the name…let me know if you don’t get it sorted!

[Antony]

Thanks Ken… yes, I can see I put the call in the wrong place now… just executed a stored procedure live and it worked… Yippee!

[Hyperbytes]

Bookmarked, may do a video at some point on this one! Never thought of doing it that way

[Antony]

That’s great Brian!

I am used to a whole concept in Bubble called “Privacy Settings” where you can set up some complex rules about which user can manipulate which fields from which data type. It feels very safe to have this all set up, and I am using the procedures to, amongst other things, replicate my security needs between different user types in this way.

I also want to keep my data management totally separate from my GUI, so that if, in the future, I decide to build another app another way (e.g. mobile), then all the access to my data is cleanly managed in MySQL, and I just have to build a new user interface that accesses that data through the same “gateway” I will have created.

[Jaymer]

Hi, just reaching out to you. - showed you are still current, which is fabulous after 2+ years!
At the beginning of my journey, I’m asking the same Qs as you did 2+ years ago.
One of my main queries is a SP so I needed to see if its possible.

So since you are experienced with back end SQL, 2 years later are you happy with the integration that Wappler provides? I use MSSQL.

[Antony]

Hey @Jaymer, thanks for your message!

Yes, I am super happy with Wappler. It does all I could ask for and am about to launch my huge business admin app in May after 2 years of Wappler development (and 2 years on Bubble before that! ).

It is quite an adjustment and steep learning curve, but definitely worth every minute.

I would NEVER want to be locked into Bubble again.

Wishing you well with your project!

Best regards,
Antony.

[AlexandreMoreira]

Hi @Antony! I have exactly the same project that we were implementing in Bubble for 2 years and now migrating to Wappler! Thanks for the encouragement! How is the project going? Within expectations or results above expectations! We opted for a NodeJS project with MSSQL, what do you think?

[Antony]

Hey @AlexandreMoreira, welcome!

I have an amazing product that my clients love with Wappler… I’m soooo happy!