Multiple tables with Stored Procedures

Posted on 2004-04-29
Last Modified: 2013-12-24

I'm calling a stored procedure. It returns two tables. Using <cfquery> how do I assign the two tables to two variables/structures/queries?



<cfquery datasource="#dsource#" name="findAccomsByAliasList">
      DECLARE @RC int
      DECLARE @licence varchar(128)
      DECLARE @aliasSetType varchar(8000)
      DECLARE @imgSize varchar(128)
      DECLARE @imgPrefix varchar(128)
      DECLARE @imgFileType varchar(128)
      DECLARE @imgOperatorLogo varchar(128)
      SELECT @licence = '#licence#'
      SELECT @aliasSetType = '#aliasSetType#'
      EXEC @RC = [TrailStream].[dbo].[findAccomsByAliasList] @licence, @aliasSetType, DEFAULT, DEFAULT, DEFAULT, DEFAULT
Question by:Stoke
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 17

Expert Comment

ID: 10954313
Use the tag cfstoredproc and cfprocresult for each result.

<cfstoredproc ............>
    <cfprocparam .............>
    <cfprocresult name="qOne" resultset="1">
    <cfprocresult name="qTwo" resultset="2">

Author Comment

ID: 10957446
But I get the following error:

Invalid CFML construct found on line 30 at column 55.
ColdFusion was looking at the following text:


# An expression that began on line 28, column 64.
Your expression might be missing an ending "#" (it might look like #expr ).
# the tag attribute "value", on line 28, column 43.
# a cfprocparam tag beginning on line 26, column 26.
# a cfprocparam tag beginning on line 26, column 26
LVL 17

Expert Comment

ID: 10957728
Can I see your code? I hope you did not exactly copied what I typed, what I typed was just to give you an idea ;-)
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.


Author Comment

ID: 10957851
Sure... Thank you:

<cfstoredproc datasource="#dsource#" procedure="findAliasList" >
      <cfprocparam type = "IN"
             CFSQLType = CF_SQL_VARCHAR
                    value = "#licence#"  dbVarName = @licence >
      <cfprocparam type = "IN"
             CFSQLType = CF_SQL_VARCHAR
                    value = "aliasSetType#"  dbVarName = @aliasSetType >

      <cfprocresult name = "qONE" resultset="1">
      <cfprocresult name = "qTWO" resultSet = "2">
LVL 17

Accepted Solution

Tacobell777 earned 50 total points
ID: 10958061
 dbVarName does not work, you need to pass the parameters in the order the are in the SP.

the problem is here
<cfprocparam type = "IN"
           CFSQLType = CF_SQL_VARCHAR
                 value = "aliasSetType#"  dbVarName = @aliasSetType >

it needs to be

<cfprocparam type = "IN"
           CFSQLType = CF_SQL_VARCHAR
                 value = "#aliasSetType#"  dbVarName = @aliasSetType >

you were missing a # infront of aliassettype

Author Comment

ID: 10958097
That's great. Thanks... That's going to prove really useful.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service ( is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question