locating SELECT statements in SQL Stored Procedure and corresponding query in ColdFusion

Question

In ColdFusion, I have the following cfstoredproc being called with 3 inputs and 6 outputs.

<cfstoredproc procedure="si_updateProject" datasource="#mydsn#" returncode="yes">
    <cfprocparam type="IN" cfsqltype="CF_SQL_VARCHAR" value="#platform#">
    <cfprocparam type="IN" cfsqltype="CF_SQL_VARCHAR" value="#projectData#">
    <cfprocparam type="IN" cfsqltype="CF_SQL_VARCHAR" value="#sysData#">
    <cfprocresult name="projectInfo" resultSet=1>
    <cfprocresult name="newPSA" resultSet=2>
    <cfprocresult name="newStatus" resultSet=3>
    <cfprocresult name="goliveSystems" resultSet=4>
    <cfprocresult name="goliveHistory" resultSet=5>
    <cfprocresult name="newSystems" resultSet=6>
</cfstoredproc>

Within in the stored procedure, si_updateProject, how do I identify the 6 resultSet queries as listed for the resultSets? The stored proc has several statements (select, updates, deletes, etc).

Is there a bigger problem you are trying to solve? Because I am not aware of any method other than eyeballing the sql for SELECT statements that generate a result and comparing the `columnList` values. ie The first statement will correspond to resultSet=1, the second to resultSet=2, etc... — Leigh, Feb 13 '12 at 19:27
@Leigh - I need to modify one of the select statements to include some new criteria and just need to update the correct sql statement. The storedproc works with a lot of virtual tables so I cannot view output once the storedproc completes. I was doing what you suggested but I cannot confirm the data since the storedproc works with virtual tables - unless you know of another trick you can teach me. — HPWD, Feb 13 '12 at 21:00
There is really is not an easy way. When I need to test a modification to a stored proc, I usually comment out the `CREATE PROC ... BEGIN` and `END` statements and run the contents manually in the query analyzer. Give me a minute to post an example. — Leigh, Feb 13 '12 at 21:36
Very low tech, but useful for debugging/testing procedures involving table variables http://pastebin.com/LhzhZNru . (There are probably fancier step through debugging tools. But this is what I use for quick and dirty testing). — Leigh, Feb 13 '12 at 22:02
@Leigh thanks for the example. If you will post it as an answer, I'll give you credit. — HPWD, Feb 13 '12 at 22:05

score 1 · Accepted Answer · answered Feb 13 '12 at 22:18

(Synopsis from comments above ..)

I am not aware of any method other than eyeballing the sql for SELECT statements that generate a result and comparing the columnList values.

When I need to test modifications to a stored procedure involving virtual tables, I usually just comment out the CREATE PROCEDURE ... BEGIN and END statements and run the sql manually in the query analyzer. Very low tech, but useful for quick testing.

--- Comment out the procedure wrapper and run the sql in
--- the query analyzer manually with test parameters
DECLARE @platform varchar(50)
DECLARE @projectData varchar(50)
DECLARE @sysData varchar(50)

SET @platform = 'foo'
SET @projectData = 'bar'
SET @sysData = 'qax'

/*     
CREATE PROCEDURE si_updateProject
        @platform varchar(50)
        , @projectData varchar(50)
        , @sysData varchar(50)
AS
BEGIN
*/

        -- simulate some virtual tables
        DECLARE @table1 TABLE ( columnOne varchar(50), createdDate datetime)
        DECLARE @table2 TABLE ( columnTwo varchar(50), createdDate datetime)
        DECLARE @table3 TABLE ( columnThree varchar(50), createdDate datetime)

        -- now you can do whatever debugging you want with the virtual tables ...
        SELECT  'Testing the 1st resultset' AS DebugText, *
        FROM    @table1


        -- simulate some resultsets
        SELECT  columnOne FROM @Table1
        SELECT  columnTwo FROM @Table2
        SELECT  columnThree FROM @Table3

/*
END */
GO

locating SELECT statements in SQL Stored Procedure and corresponding query in ColdFusion

1 Answers1