Run an ADHOC Query in an SQL Stored Procedure

James Huff
James Huff
Ninth Anniversary Loyalty Badge
in Blackbaud CRM Developer Discussions

I feel like I must be missing something simple that I just cannot see. I need to run an ADHOC query that was built in the CRM interface in SQL code, but I need access to all of the output fields, not just the IDSet for the query.

I know there is some way to do this, but I can't seem to figure it out, can someone point me in the right direction?

Comments

  • William Burke
    William Burke
    Third Anniversary Kudos 1 Name Dropper Participant

    @James Huff Hi James - I brought your question to a couple of our subject-matter experts, and they indicated we need a little more information in order to provide a useful answer - we have asked your CSM to reach out to get some more details. You should hear from her tomorow. Thanks!

  • Will Mercer
    Will Mercer
    Fifth Anniversary Kudos 1 Name Dropper Facilitator 1

    @James Huff the only way I can think of to do this would be to create the query and then copy the SQL into an SDK stored procedure spec. As you mentioned when you create a selection only the ID column is committed to a table in the database, and as ad-hoc queries don't create a database object like a view etc there isn't anything for you to reference in a stored procedure. Hope that helps.

  • Mitchell Gibbs
    Mitchell Gibbs
    Fifth Anniversary Kudos 1 Name Dropper Participant

    @James Huff One way you might be able to get what you're looking for is to save the query as a User-Defined Data List from the ad-hoc query editor. The query and data list fork into separate objects at that point, but it does create a SQL stored procedure that returns all of the fields.

    e.g., EXEC [USP_DATALIST_ADHOCQUERY_00000000_0000_0000_0000_000000000000]

Categories