Tukukkk

I would like to pass the stored proc result to another stored proc 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 store proc, I am passing the result of 1st proc to the dbvarname sqlStatement of 2nd stored proc. But the passed value #getYN# should be query instead of result because I am using it for FROM clause. The 2nd stored proc 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>

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