Is it possible to pass stored procedure result to another cfstoredproc in ColdFusion?

Question

I would like to pass the stored procedure result to another stored procedure in ColdFusion. If anyone would be able to help on this.

<cfif not isDefined("getYN")>
     <cfstoredproc procedure="stored_proc" datasource="#dsn#">
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@lang" type="in" value="#this.lang#"/>
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@sqlStatement" type="in" value="#getYN#" null="#NOT len(trim(getYN))#" />            
        <cfprocresult name="getYN" resultset = "1">
     </cfstoredproc>
</cfif>

<cfstoredproc procedure="sp_test" datasource="#dsn#">
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@lang" type="in" value="#this.lang#"/>
        <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@sqlStatement" type="in" value="#getYN#" null="#NOT len(trim(getYN))#" />            
        <cfprocresult name="get" resultset = "2">
 </cfstoredproc>

The above is the code example. In the second stored procedure, I am passing the result of 1st stored procedure to the dbvarname sqlStatement of 2nd stored procedure. But the passed value #getYN# should be query instead of result because I am using it for FROM clause.

The 2nd stored procedure in SQL Server is like below:

ALTER PROCEDURE [dbo].[sp_test]
    @lang CHAR(5),
    @code VARCHAR(20),
    @sqlStatement NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sSQL nVARCHAR(max)

    SET @sSQL = '   SELECT col1
             FROM '+ @sqlStatement +
           ' WHERE col2 = @lang
              AND col3 = @code '

    EXECUTE SP_EXECUTESQL @sSQL, N'@lang CHAR(5),
                           @code VARCHAR(20)', @lang, @code ;

    SET NOCOUNT OFF;
END

In addition, the above two code is created from the below code to replace it with cfstoredproc instead of cfquery:

<cfif NOT isDefined("request.getYN")>

        <cfquery name="request.getYN" datasource="#request.dsn.pqr#">

SELECT
    LANGUAGE_CODE                ,
    YN_CODE                ,
    YN_DESCRIPTION          
FROM
    LANGUAGE_ALTS_YN          
WHERE
    language_code IN (
        'EN','#this.lang#'
    )        
        </cfquery>
    </cfif>

    <cfquery name="get" dbtype="query">

SELECT
    yn_description        
FROM
    request.getYN        
WHERE
    language_code =
<cfqueryparam cfsqltype="cf_sql_varchar" value="#this.lang#" />         
    AND yn_code = <cfqueryparam cfsqltype="cf_sql_varchar" 
     value="#arguments.code#" />      
     </cfquery>

If your stored procedure does not change any data, can you make a SQL function instead? — James A Mohler, Nov 21 '18 at 02:52
@JamesAMohler Yes, but I have to have store proc instead of function. — S M, Nov 21 '18 at 16:06
Your sample code can be in a normal ``. Having said that, I would recommend doing a `` after the first query, picking off the variables and feeding them to the next one. I am not sure what the stored procedure is supposed to do that simpler methods won't do. — James A Mohler, Nov 21 '18 at 16:54
@JamesAMohler yes, I can do that using but I am trying to remove cfquery and replace by stored proc. — S M, Nov 21 '18 at 16:59
This is this not `codereview.stackexchange.com`, but I hope you are aware that you are writing twice as much code and it will come out less secure. I suspect it will run slower too. — James A Mohler, Nov 21 '18 at 17:05
Agreed about it being less secure. sp_execsql **won't** protect that particular query. — SOS, Nov 21 '18 at 19:44
Adding a dynamic `FROM` to a query can be very dangerous. At the very least, I would add in some kind of blacklisting to prevent access to system tables, or preferably whitelist to only allow the tables you intend a user to access. Or even better, take a deep look at user permissions for both `EXECUTE` and `SELECT`. Also, are you able to combine the two stored procedures into another single sproc? What flavor and version of SQL? — Shawn, Nov 21 '18 at 19:52
@Shawn Yes, I have combined two of the stored proc into one. I am using SQL Server 2014 now. Thanks — S M, Nov 21 '18 at 20:09
Good deal and good luck. I've always found that when you're trying to do something to manipulate the data you're trying to work with, it's usually better to handle it from the database end rather than from the application side. — Shawn, Nov 21 '18 at 21:28

James A Mohler · Accepted Answer · 2018-11-26T16:40:05.013

The second query really isn't a query. It can't be made into a stored procedure because it does not run on the database server. In other words dbtype="query" is not on the DB server

Besides, you can just filter this data down.

Was

<cfquery name="get" dbtype="query">

 SELECT yn_description        
 FROM
    request.getYN        
 WHERE
   language_code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#this.lang#" />
   AND yn_code = <cfqueryparam cfsqltype="cf_sql_varchar" 
   value="#arguments.code#" />      
 </cfquery>

Should be

get = request.getYN.filter(function () {
   return (lang_code == this.lang && yn_code == code);
   });

Note: that code on my second line is not scoped. That is not a mistake.

For query filters see: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryfilter.html

Code based on comment

get = request.getYN.filter(function () {
   return (lang_code == this.lang && yn_code == code);
   }).yn_description;

BTW: Unless the field are large text, varchar(max), or xml, It typically does matter if you are picking one or all

Thank you so much. It helped me to get rid of inline sql statements. — S M, Nov 26 '18 at 15:55
But how to get the column value yn_description only instead of getting all the column values. Thanks again. — S M, Nov 26 '18 at 16:12

Is it possible to pass stored procedure result to another cfstoredproc in ColdFusion?

1 Answers1