Solved

Problem with UNIQUEIDENTIFIERS within an IN clause

Posted on 2002-07-01
8
324 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:bhess1
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 142

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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 32

Expert Comment

by:bhess1
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:
bhess1 earned 200 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now