?
Solved

Problem with UNIQUEIDENTIFIERS within an IN clause

Posted on 2002-07-01
8
Medium Priority
?
335 Views
Last Modified: 2008-03-03
I have created a SPROC that generates a comma-delimited list of GUIDs that I would like to pass into an IN clause in another SPROC. Unfortunately, I receive the following error message:
    "Syntax error converting from a character string to uniqueidentifier"

Following is the code from my SPROC that generates the list of GUIDs:

---------- BEGIN --------------

/* =====================================
    Used to retrieve the hierarchically lower agencies
    in relation to the passed agency id. Called from
    the spCustomerSatisfaction SPROC.
    ===================================== */

CREATE PROCEDURE [usp_GetRelationalAgencies]

      @PASSED_AGENCY UNIQUEIDENTIFIER,
      @AGENCIES VARCHAR(8000) OUTPUT
AS

SET NOCOUNT ON
      SET @AGENCIES = ''

-- Declare the variables to store the values returned by FETCH.
DECLARE @AGENCY UNIQUEIDENTIFIER
DECLARE @AGENCY2 UNIQUEIDENTIFIER  --Used for our second cursor
DECLARE agency_cursor CURSOR FOR
SELECT AGENCYID FROM METRICS_AGENCY WHERE PARENTID = @PASSED_AGENCY
OPEN agency_cursor
-- Perform the first fetch and store the values in the @AGENCY variable.
FETCH NEXT FROM agency_cursor
INTO @AGENCY
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Let's append the current AGENCY ID to our AGENCIES collection first...
    --SELECT @AGENCIES = RTRIM(@AGENCIES) + ',''' + RTRIM(CAST(@AGENCY AS VARCHAR(40))) + ''''
    -- Now, see if the current AGENCY is a parent...
      DECLARE agency_cursor2 CURSOR FOR
      SELECT AGENCYID FROM METRICS_AGENCY WHERE PARENTID = @AGENCY
      OPEN agency_cursor2
      -- Perform the second (nested) fetch and append the values in the @AGENCY variable.
      FETCH NEXT FROM agency_cursor2
      INTO @AGENCY2
      -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
      WHILE @@FETCH_STATUS = 0
      BEGIN
            --SELECT @AGENCIES = RTRIM(@AGENCIES) + ',''' + RTRIM(CAST(@AGENCY2 AS VARCHAR(40))) + ''''
            -- This is executed as long as the previous fetch succeeds.
                FETCH NEXT FROM agency_cursor2
                INTO @AGENCY2
      END
        CLOSE agency_cursor2
      DEALLOCATE agency_cursor2
    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM agency_cursor
    INTO @AGENCY
END
 
CLOSE agency_cursor
DEALLOCATE agency_cursor

SELECT @AGENCIES = '''' + CAST(@PASSED_AGENCY AS VARCHAR(40)) + '''' + @AGENCIES

---------- END --------------------

Below is a test script I set up to run in Query Analyzer (notice the @AGENCY parameter in the IN clause):

---------- BEGIN ------------------

DECLARE @AGENCY             varchar(8000)
DECLARE @PASSED_AGENCY            uniqueidentifier
DECLARE @StartDate             datetime
DECLARE @EndDate            datetime
DECLARE @TotalEaseOfAcessNum      int
SET @StartDate            = '6/1/2002'
SET @EndDate            = '6/30/2002'
SET @PASSED_AGENCY      = 'F77841D7-27A9-11D6-B72E-00E02950ED59'
EXECUTE usp_GetRelationalAgencies @PASSED_AGENCY, @AGENCIES = @AGENCY OUTPUT
PRINT @AGENCY

-- Here is my stop point where I receive the above-mentioned error ("Syntax error converting from a character string to uniqueidentifier"). This error pertaines to the @AGENCY parameter value (GUIDs separated by commas)
SELECT @TotalEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

PRINT @TotalEaseOfAcessNum
PRINT @AGENCY

-- Here I rebuild the problem line above, but manually insert the values returned by the usp_GetRelationalAgencies SPROC. I do not get an error in this instance. Weird!

SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN '6/1/2002' AND '6/30/2002' AND AgencyID IN('F77841D7-27A9-11D6-B72E-00E02950ED59','F77841D8-27A9-11D6-B72E-00E02950ED59','F77845A9-27A9-11D6-B72E-00E02950ED59','F77845AA-27A9-11D6-B72E-00E02950ED59','F77841DD-27A9-11D6-B72E-00E02950ED59','F77841DE-27A9-11D6-B72E-00E02950ED59','0950F23C-BC97-461D-B0B7-79CD16B3598A','A4E4C9D3-E8C4-4466-A3D7-A434070932A8','F77841D9-27A9-11D6-B72E-00E02950ED59','F77841DA-27A9-11D6-B72E-00E02950ED59','F77841DB-27A9-11D6-B72E-00E02950ED59','F77841DC-27A9-11D6-B72E-00E02950ED59')

------------ END -----------------

Does anyone know why placing comma-separated GUIDs within an IN clause works when I manually do it (see above) versus when I pass the exact same string into a parameter (@AGENCY)?

Thanks - Joel
0
Comment
Question by:joelhulen
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 7123204
The IN clause does not accept a variable for parsing, unfortunately (wouldn't that be easy?)  To pass a list of items into an IN clause, you will need to build the SQL statement dynamically, e.g.:

Declare @SQL varchar(8000)
Set @SQL = 'SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''' AND AgencyID IN(' + @AGENCY + ')'

EXEC(@SQL)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7123966
Comment
From: angelIII  Date: 07/01/2002 08:02AM PST  
the syntax <AgencyID IN(@AGENCY)> doesn't work by design, as @AGENCY is used as a single value...

Try this (note that performance will probably be less):
',' + @AGENCY + ',' LIKE '%,' + AgencyID + ',%'

CHeers
 
0
 

Author Comment

by:joelhulen
ID: 7125149
bhess and angellll,

I have tried both of your suggestions without success. The major factor is the shear size of the sql statement I am trying to run. Also, angellll, I still get the same error ("Syntax error converting from a character string to uniqueidentifier")

Here is a copy of the SPROC I am trying to run:

---------- BEGIN ------------

CREATE PROC spCustomerSatisfaction

      @StartDate            datetime,
      @EndDate            datetime,
      @PASSED_AGENCY      uniqueidentifier,
      --@AGENCY      uniqueidentifier,

--      Timeless Table
      @TotalEaseOfAcessNum int            OUTPUT,
      @TotalInitResTimeNum int            OUTPUT,
      @TotalUltResTimeNum      int             OUTPUT,

      @UnsEaseOfAcessNum      int             OUTPUT,
      @UnsIRTNum      int                  OUTPUT,
      @UnsURTNum int                  OUTPUT,

      @BAEaseOfAcessNum      int            OUTPUT,
      @BAIRTNum      int                  OUTPUT,
      @BAURTNum int                  OUTPUT,

      @AEaseOfAcessNum      int            OUTPUT,
      @AIRTNum      int                  OUTPUT,
      @AURTNum int                                OUTPUT,

      @AAEaseOfAcessNum      int            OUTPUT,
      @AAIRTNum      int                  OUTPUT,
      @AAURTNum int                  OUTPUT,

      @EEaseOfAcessNum      int            OUTPUT,
      @EIRTNum      int                  OUTPUT,
      @EURTNum int                        OUTPUT,

--      Personnel Table
      @TotalTechKnowNum int            OUTPUT,
      @TotalCourtPleasNum      int             OUTPUT,

      @UnsTKNum      int                  OUTPUT,
      @UnsCPNum int                  OUTPUT,

      @BATKNum      int                  OUTPUT,
      @BACPNum int                        OUTPUT,

      @ATKNum      int                  OUTPUT,
      @ACPNum int                                OUTPUT,

      @AATKNum      int                  OUTPUT,
      @AACPNum int                        OUTPUT,

      @ETKNum      int                  OUTPUT,
      @ECPNum int                        OUTPUT,

--      General Table
      @TotalEffectNum int                  OUTPUT,
      @TotalOverSatNum      int             OUTPUT,

      @UnsEffectNum      int            OUTPUT,
      @UnsOverSatNum int                  OUTPUT,

      @BAEffectNum      int                  OUTPUT,
      @BAOverSatNum int                  OUTPUT,

      @AEffectNum      int                  OUTPUT,
      @AOverSatNum int                  OUTPUT,

      @AAEffectNum      int                  OUTPUT,
      @AAOverSatNum int                  OUTPUT,

      @EEffectNum      int                  OUTPUT,
      @EOverSatNum int                  OUTPUT

AS

BEGIN TRAN TransStart

-- RETRIEVE ASSOC AGENCIES PER PASSED VALUE(@PASSED_AGENCY)
DECLARE @AGENCY       varchar(2000)
EXECUTE usp_GetRelationalAgencies @PASSED_AGENCY, @AGENCIES = @AGENCY OUTPUT

-- Timeless Table
SELECT @TotalEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalInitResTimeNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalUltResTimeNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

SELECT @UnsEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

-- Personnel Table
SELECT @TotalTechKnowNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalCourtPleasNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

SELECT @UnsTKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsCPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @ATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @ACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)
SELECT @ETKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)
SELECT @ECPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)

-- General Table
SELECT @TotalEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

SELECT @UnsEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

COMMIT TRAN TransStart

---------- END ------------

As you can see, the query string is waaaay to large to run an EXECUTE @SQL or EXECUTE sp_executesql @SQL on.

What other options can I try???

Thanks
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:joelhulen
ID: 7125264
bhess and angellll,

I have tried both of your suggestions without success. The major factor is the shear size of the sql statement I am trying to run. Also, angellll, I still get the same error ("Syntax error converting from a character string to uniqueidentifier")

Here is a copy of the SPROC I am trying to run:

---------- BEGIN ------------

CREATE PROC spCustomerSatisfaction

      @StartDate            datetime,
      @EndDate            datetime,
      @PASSED_AGENCY      uniqueidentifier,
      --@AGENCY      uniqueidentifier,

--      Timeless Table
      @TotalEaseOfAcessNum int            OUTPUT,
      @TotalInitResTimeNum int            OUTPUT,
      @TotalUltResTimeNum      int             OUTPUT,

      @UnsEaseOfAcessNum      int             OUTPUT,
      @UnsIRTNum      int                  OUTPUT,
      @UnsURTNum int                  OUTPUT,

      @BAEaseOfAcessNum      int            OUTPUT,
      @BAIRTNum      int                  OUTPUT,
      @BAURTNum int                  OUTPUT,

      @AEaseOfAcessNum      int            OUTPUT,
      @AIRTNum      int                  OUTPUT,
      @AURTNum int                                OUTPUT,

      @AAEaseOfAcessNum      int            OUTPUT,
      @AAIRTNum      int                  OUTPUT,
      @AAURTNum int                  OUTPUT,

      @EEaseOfAcessNum      int            OUTPUT,
      @EIRTNum      int                  OUTPUT,
      @EURTNum int                        OUTPUT,

--      Personnel Table
      @TotalTechKnowNum int            OUTPUT,
      @TotalCourtPleasNum      int             OUTPUT,

      @UnsTKNum      int                  OUTPUT,
      @UnsCPNum int                  OUTPUT,

      @BATKNum      int                  OUTPUT,
      @BACPNum int                        OUTPUT,

      @ATKNum      int                  OUTPUT,
      @ACPNum int                                OUTPUT,

      @AATKNum      int                  OUTPUT,
      @AACPNum int                        OUTPUT,

      @ETKNum      int                  OUTPUT,
      @ECPNum int                        OUTPUT,

--      General Table
      @TotalEffectNum int                  OUTPUT,
      @TotalOverSatNum      int             OUTPUT,

      @UnsEffectNum      int            OUTPUT,
      @UnsOverSatNum int                  OUTPUT,

      @BAEffectNum      int                  OUTPUT,
      @BAOverSatNum int                  OUTPUT,

      @AEffectNum      int                  OUTPUT,
      @AOverSatNum int                  OUTPUT,

      @AAEffectNum      int                  OUTPUT,
      @AAOverSatNum int                  OUTPUT,

      @EEffectNum      int                  OUTPUT,
      @EOverSatNum int                  OUTPUT

AS

BEGIN TRAN TransStart

-- RETRIEVE ASSOC AGENCIES PER PASSED VALUE(@PASSED_AGENCY)
DECLARE @AGENCY       varchar(2000)
EXECUTE usp_GetRelationalAgencies @PASSED_AGENCY, @AGENCIES = @AGENCY OUTPUT

-- Timeless Table
SELECT @TotalEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalInitResTimeNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalUltResTimeNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

SELECT @UnsEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction WHERE CS_Access = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction WHERE CS_RespTime =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction WHERE CS_ResTime = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

-- Personnel Table
SELECT @TotalTechKnowNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalCourtPleasNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

SELECT @UnsTKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsCPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @ATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @ACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)
SELECT @ETKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction WHERE CS_TechKnowledge =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)
SELECT @ECPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction WHERE CS_Courteous = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)

-- General Table
SELECT @TotalEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @TotalOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

SELECT @UnsEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @UnsOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @BAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @AAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction WHERE CS_Effectiveness =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))
SELECT @EOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction WHERE CS_OverallSat = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID IN(@AGENCY))

COMMIT TRAN TransStart

---------- END ------------

As you can see, the query string is waaaay to large to run an EXECUTE @SQL or EXECUTE sp_executesql @SQL on.

What other options can I try???

Thanks
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 7125333
Comment:  The most likely solution at this point that I can see is to place the GUID values into a temporary table, and link to that table.  Sample code:

Declare @TempName varchar(25)
Set @TempName = '##TMP' + Cast(@@SPID As Varchar)
Declare @SQL varchar(100)
Set @SQL = 'CREATE TABLE ' + @TempName + ' (GUIDValues AS uniqueidentifier Primary Key)'
EXEC(@SQL)

--You now have a global temporary table unique to your process

-- add the @TempName value to your target SP

----------- BEGIN --------------------
CREATE PROCEDURE [usp_GetRelationalAgencies]

     @PASSED_AGENCY UNIQUEIDENTIFIER,
     @TempTbl Varchar(25),
AS

SET NOCOUNT ON

-- Declare the variables to store the values returned by FETCH.
DECLARE @AGENCY UNIQUEIDENTIFIER
DECLARE @AGENCY2 UNIQUEIDENTIFIER  --Used for our second cursor
DECLARE @SQL Varchar(200)  -- used for table inserts
DECLARE agency_cursor CURSOR FOR
SELECT AGENCYID FROM METRICS_AGENCY WHERE PARENTID = @PASSED_AGENCY
OPEN agency_cursor
-- Perform the first fetch and store the values in the @AGENCY variable.
FETCH NEXT FROM agency_cursor
INTO @AGENCY
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Let's add the current AGENCY ID to temp table first...
     Set @SQL = 'INSERT INTO ' + @TempTbl + ' Values(' + @Agency + ')'
     Exec(@SQL)

   -- Now, insert all records that this is a parent of
   -- This gets rid of one cursor

     Set @SQL = 'Insert Into ' + @TempTbl + ' SELECT AGENCYID FROM METRICS_AGENCY WHERE PARENTID = ' + @Agency
     EXEC (@SQL)
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM agency_cursor
   INTO @AGENCY
END
 
CLOSE agency_cursor
DEALLOCATE agency_cursor

---------- END -------------


-- Now, let's modify your SP to use the Temp Table
-- to simplify, let's use a local temp table, and copy the data

EXECUTE usp_GetRelationalAgencies @PASSED_AGENCY, @TempTbl

Create Table #tmpGUIDS (GUIDValues AS UniqueIdentifier PRIMARY KEY)

INSERT INTO #tmpGUIDS SELECT * FROM @tmpTable

Drop Table @TmpTable

--  Change your code to reference the #tmpGUIDS table

-- Example:

SELECT @TotalEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues
WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
0
 

Author Comment

by:joelhulen
ID: 7126929
bhess1,

Would you put this code:

------ BEGIN -------
Comment:  The most likely solution at this point that I can see is to place the GUID values into a temporary table, and link to that table.  Sample code:

Declare @TempName varchar(25)
Set @TempName = '##TMP' + Cast(@@SPID As Varchar)
Declare @SQL varchar(100)
Set @SQL = 'CREATE TABLE ' + @TempName + ' (GUIDValues AS uniqueidentifier Primary Key)'
EXEC(@SQL)

--You now have a global temporary table unique to your process

-- add the @TempName value to your target SP

------ END -------

Into the spCustomerSatisfaction SPROC or ??
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 800 total points
ID: 7127610
---------- BEGIN ------------

CREATE PROC spCustomerSatisfaction

@StartDate datetime,
@EndDate datetime,
@PASSED_AGENCY uniqueidentifier,
--@AGENCY uniqueidentifier,

-- Timeless Table
@TotalEaseOfAcessNum int OUTPUT,
@TotalInitResTimeNum int OUTPUT,
@TotalUltResTimeNum int OUTPUT,

@UnsEaseOfAcessNum int OUTPUT,
@UnsIRTNum int OUTPUT,
@UnsURTNum int OUTPUT,

@BAEaseOfAcessNum int OUTPUT,
@BAIRTNum int OUTPUT,
@BAURTNum int OUTPUT,

@AEaseOfAcessNum int OUTPUT,
@AIRTNum int OUTPUT,
@AURTNum int OUTPUT,

@AAEaseOfAcessNum int OUTPUT,
@AAIRTNum int OUTPUT,
@AAURTNum int OUTPUT,

@EEaseOfAcessNum int OUTPUT,
@EIRTNum int OUTPUT,
@EURTNum int OUTPUT,

-- Personnel Table
@TotalTechKnowNum int OUTPUT,
@TotalCourtPleasNum int OUTPUT,

@UnsTKNum int OUTPUT,
@UnsCPNum int OUTPUT,

@BATKNum int OUTPUT,
@BACPNum int OUTPUT,

@ATKNum int OUTPUT,
@ACPNum int OUTPUT,

@AATKNum int OUTPUT,
@AACPNum int OUTPUT,

@ETKNum int OUTPUT,
@ECPNum int OUTPUT,

-- General Table
@TotalEffectNum int OUTPUT,
@TotalOverSatNum int OUTPUT,

@UnsEffectNum int OUTPUT,
@UnsOverSatNum int OUTPUT,

@BAEffectNum int OUTPUT,
@BAOverSatNum int OUTPUT,

@AEffectNum int OUTPUT,
@AOverSatNum int OUTPUT,

@AAEffectNum int OUTPUT,
@AAOverSatNum int OUTPUT,

@EEffectNum int OUTPUT,
@EOverSatNum int OUTPUT

AS

BEGIN TRAN TransStart

Declare @TempName varchar(25)
Set @TempName = '##TMP' + Cast(@@SPID As Varchar)
Declare @SQL varchar(100)
Set @SQL = 'CREATE TABLE ' + @TempName + ' (GUIDValues AS uniqueidentifier Primary Key)'
EXEC(@SQL)

-- RETRIEVE ASSOC AGENCIES PER PASSED VALUE(@PASSED_AGENCY)

EXECUTE usp_GetRelationalAgencies @PASSED_AGENCY, @TempTbl

Create Table #tmpGUIDS (GUIDValues AS UniqueIdentifier PRIMARY KEY)
INSERT INTO #tmpGUIDS SELECT * FROM @TempTbl
Drop Table @TempTbl

-- Timeless Table
SELECT @TotalEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalInitResTimeNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalUltResTimeNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)

SELECT @UnsEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)

-- Personnel Table
SELECT @TotalTechKnowNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalCourtPleasNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)

SELECT @UnsTKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsCPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @ATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @ACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)
SELECT @ETKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)
SELECT @ECPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate AND AgencyID = @AGENCY)

-- General Table
SELECT @TotalEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)

SELECT @UnsEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)

COMMIT TRAN TransStart

---------- END ------------
0
 

Author Comment

by:joelhulen
ID: 7127802
bhess1,

Thank you so much for everything, including bearing with me through this. You came up with a very good solution and I thank you again. I was able to use this code with some modification. Below is the final code that worked in both of my Stored Procedures:


---------- BEGIN SPCUSTOMERSATISFACTION ------------

/****** Object:  Stored Procedure dbo.spCustomerSatisfaction2    Script Date: 7/2/2002 10:30:11 AM ******/
/*
 This SPROC is for computing the values that go on the
Customer Satisfaction Report
*/

CREATE PROC spCustomerSatisfaction

@StartDate datetime,
@EndDate datetime,
@PASSED_AGENCY uniqueidentifier,
--@AGENCY uniqueidentifier,

-- Timeless Table
@TotalEaseOfAcessNum int OUTPUT,
@TotalInitResTimeNum int OUTPUT,
@TotalUltResTimeNum int OUTPUT,

@UnsEaseOfAcessNum int OUTPUT,
@UnsIRTNum int OUTPUT,
@UnsURTNum int OUTPUT,

@BAEaseOfAcessNum int OUTPUT,
@BAIRTNum int OUTPUT,
@BAURTNum int OUTPUT,

@AEaseOfAcessNum int OUTPUT,
@AIRTNum int OUTPUT,
@AURTNum int OUTPUT,

@AAEaseOfAcessNum int OUTPUT,
@AAIRTNum int OUTPUT,
@AAURTNum int OUTPUT,

@EEaseOfAcessNum int OUTPUT,
@EIRTNum int OUTPUT,
@EURTNum int OUTPUT,

-- Personnel Table
@TotalTechKnowNum int OUTPUT,
@TotalCourtPleasNum int OUTPUT,

@UnsTKNum int OUTPUT,
@UnsCPNum int OUTPUT,

@BATKNum int OUTPUT,
@BACPNum int OUTPUT,

@ATKNum int OUTPUT,
@ACPNum int OUTPUT,

@AATKNum int OUTPUT,
@AACPNum int OUTPUT,

@ETKNum int OUTPUT,
@ECPNum int OUTPUT,

-- General Table
@TotalEffectNum int OUTPUT,
@TotalOverSatNum int OUTPUT,

@UnsEffectNum int OUTPUT,
@UnsOverSatNum int OUTPUT,

@BAEffectNum int OUTPUT,
@BAOverSatNum int OUTPUT,

@AEffectNum int OUTPUT,
@AOverSatNum int OUTPUT,

@AAEffectNum int OUTPUT,
@AAOverSatNum int OUTPUT,

@EEffectNum int OUTPUT,
@EOverSatNum int OUTPUT

AS

BEGIN TRAN TransStart

DECLARE @TempName varchar(25)
SET @TempName = '##TMP' + Cast(@@SPID As varchar)
DECLARE @SQL varchar(100)
SET @SQL = 'CREATE TABLE ' + @TempName + ' (GUIDValues uniqueidentifier PRIMARY KEY)'
EXEC(@SQL)
SET @SQL = ''

-- RETRIEVE ASSOC AGENCIES PER PASSED VALUE(@PASSED_AGENCY)

EXECUTE usp_GetRelationalAgencies @PASSED_AGENCY, @TempName

CREATE TABLE #tmpGUIDS (GUIDValues uniqueidentifier PRIMARY KEY)
SET @SQL = 'INSERT INTO #tmpGUIDS SELECT * FROM ' + @TempName
EXEC(@SQL)
SET @SQL = ''
SET @SQL = 'DROP TABLE ' + @TempName
EXEC(@SQL)

-- Timeless Table
SELECT @TotalEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalInitResTimeNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalUltResTimeNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)

SELECT @UnsEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EEaseOfAcessNum = (SELECT Count(CS_Access) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Access = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EIRTNum = (SELECT Count(CS_RespTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_RespTime =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EURTNum = (SELECT Count(CS_ResTime) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_ResTime = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)

-- Personnel Table
SELECT @TotalTechKnowNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalCourtPleasNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)

SELECT @UnsTKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsCPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @ATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @ACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AATKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AACPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @ETKNum = (SELECT Count(CS_TechKnowledge) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_TechKnowledge =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @ECPNum = (SELECT Count(CS_Courteous) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Courteous = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)

-- General Table
SELECT @TotalEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @TotalOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_EntryDate BETWEEN @StartDate AND @EndDate)

SELECT @UnsEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @UnsOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 1 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @BAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 2 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 3 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @AAOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 4 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EEffectNum = (SELECT Count(CS_Effectiveness) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_Effectiveness =5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)
SELECT @EOverSatNum = (SELECT Count(CS_OverallSat) FROM CustomerSatisfaction INNER JOIN #tmpGUIDS ON AgencyID = GUIDValues WHERE CS_OverallSat = 5 AND CS_EntryDate BETWEEN @StartDate AND @EndDate)

COMMIT TRAN TransStart


----------- BEGIN USP_GETRELATIONALAGENCIES ----------------

/* =====================================
    Used to retrieve the hierarchically lower agencies
    in relation to the passed agency id. Called from
    the spCustomerSatisfaction SPROC.
    ===================================== */

CREATE PROCEDURE [usp_GetRelationalAgencies]

    @PASSED_AGENCY UNIQUEIDENTIFIER,
    @TempTbl Varchar(25)
AS

SET NOCOUNT ON

-- Declare the variables to store the values returned by FETCH.
DECLARE @AGENCY UNIQUEIDENTIFIER
DECLARE @AGENCY2 UNIQUEIDENTIFIER  --Used for our second cursor
DECLARE @SQL Varchar(200)  -- used for table inserts
  -- Let's add the passed AGENCY ID to temp table first...
    SET @SQL = 'INSERT INTO ' + @TempTbl + ' (GUIDVALUES) VALUES (''' + CAST(@PASSED_AGENCY AS VARCHAR(40)) + ''')'
    EXEC(@SQL)
    SET @SQL = ''
DECLARE agency_cursor CURSOR FOR
SELECT AGENCYID FROM METRICS_AGENCY WHERE PARENTID = @PASSED_AGENCY
OPEN agency_cursor
-- Perform the first fetch and store the values in the @AGENCY variable.
FETCH NEXT FROM agency_cursor
INTO @AGENCY
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Let's add the current AGENCY ID to temp table first...
    SET @SQL = 'INSERT INTO ' + @TempTbl + ' (GUIDVALUES) VALUES (''' + CAST(@AGENCY AS VARCHAR(40)) + ''')'
    EXEC(@SQL)
    SET @SQL = ''

  -- Now, insert all records that this is a parent of
  -- This gets rid of one cursor

    SET @SQL = 'INSERT INTO ' + @TempTbl + ' SELECT AGENCYID FROM METRICS_AGENCY WHERE PARENTID = ''' + CAST(@AGENCY AS VARCHAR(40)) + ''''
    EXEC (@SQL)
  -- This is executed as long as the previous fetch succeeds.
  FETCH NEXT FROM agency_cursor
  INTO @AGENCY
END

CLOSE agency_cursor
DEALLOCATE agency_cursor

----------- END -------------
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

840 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