[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Multiple tables with Stored Procedures

Hi,

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

Thanks,

Stoke

<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
</cfquery>
0
Stoke
Asked:
Stoke
  • 3
  • 3
1 Solution
 
Tacobell777Commented:
Use the tag cfstoredproc and cfprocresult for each result.

<cfstoredproc ............>
    .......................
    <cfprocparam .............>
    <cfprocresult name="qOne" resultset="1">
    <cfprocresult name="qTwo" resultset="2">
</cfstoredproc>
0
 
StokeAuthor Commented:
Thanks.
But I get the following error:

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

qONE

# 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
0
 
Tacobell777Commented:
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 ;-)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
StokeAuthor Commented:
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">
</cfstoredproc>
0
 
Tacobell777Commented:
 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
0
 
StokeAuthor Commented:
That's great. Thanks... That's going to prove really useful.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now