Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 25th, 2004, 03:06 PM
Authorized User
 
Join Date: Jun 2003
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Big Query

I have inherited a DB from another project and am tasked with fine tuning some of the SP’s. One that is really biting me is this web page that allows you to search on like 22 different combinations of fields.

Naturally it is extremely inefficient and I need to change it. I was thinking of trying to get like the top 5 most types of searches and build separate SP’s to handle just those types of searches to try and break things out – does anyone else have any suggestions?

Here is the SP:

Code:
CREATE PROCEDURE dbo.p_ViewInspections

                 @inspection_num    NUMERIC (10, 0) = NULL,

                 @cntrlg_insp_num   NUMERIC (10, 0) = NULL,

                 @related_activity  NUMERIC (10, 0) = NULL,

                 @UBI               CHAR (9)        = NULL,

                 @bus_loc           CHAR (8)        = NULL,

                 @acct_num          CHAR (8)        = NULL,

                 @naics             CHAR (6)        = NULL, -- For filtering by NAICS or SIC, depending on length.

                 @date_type         CHAR (2)        = NULL,

                 @from_date         DATETIME        = NULL,

                 @to_date           DATETIME        = NULL,

                 @assignment_type   CHAR (1)        = NULL,

                 @standard_violated VARCHAR (50)    = NULL,

                 @inspection_type   CHAR (1)        = NULL,

                 @site_region           CHAR (1)           = NULL,

                 @csho_region           CHAR (1)        = NULL,

                 @special_tracking  CHAR (50)       = NULL,

                 @on_appeal         CHAR (1)        = NULL,

                 @csho_id           CHAR (5)        = NULL,

                 @safety_health     CHAR (1)        = NULL,

                 @dba_name          VARCHAR (50)    = NULL,

                 @legal_name        VARCHAR (50)    = NULL,

                 @verbose           CHAR (1)        = NULL  -- Y means print out (select) the executed SQL.



AS

--****************************************************

-- $Modtime: 3/18/04 11:59a $

-- Description: This procedure builds a SQL query on

-- the fly and extracts data for ViewInspections.asp

-- web page.

--

-- Input:

--    Could be almost any combination of input fields.

--    

--****************************************************

 

DECLARE @sqlinsert             CHAR (255),

        @sqlselect01           CHAR (255),

        @sqlselect02           CHAR (255),

        @sqlselect03           CHAR (255),

        @sqlselect04           CHAR (255),

        @sqlselect05           CHAR (255),

        @sqlselect06           CHAR (255),

        @sqlselect07           CHAR (255),

        @sqlselect08           CHAR (255),

        @sqlselect09           CHAR (255),

        @sqlselect10           CHAR (255),

        @sqlselect11           CHAR (255),

        @sqlselect12           CHAR (255),

        @sqlselect13           CHAR (255),

        @sqlselect14           CHAR (255),

        @sqlselect15           CHAR (255),

        @sqlselect16           CHAR (255),

        @sqlselect17           CHAR (255),

        @sqlselect18           CHAR (255),

        @sqlselect19           CHAR (255),

        @sqlselect20           CHAR (255),

        @sqlselect21           CHAR (255),

        @sqlselect22           CHAR (255),

        @sqlselect23           CHAR (255),

        @sqlselect24           CHAR (255),

        @sqlselect25           CHAR (255),

        @sqlselect26           CHAR (255),

        @sqlselect27           CHAR (255),

        @sqlselect27a          CHAR (255),

        @sqlselect27b          CHAR (255),

        @sqlselect27c          CHAR (255),

        @sqlselect27d          CHAR (255),

        @sqlselect28           CHAR (255),

        @sqlselect29           CHAR (255),

        @sqlselect30           CHAR (255),

        @sqlselect31           CHAR (255),

        @sqlselect32           CHAR (255),

        @sqlselect33           CHAR (255),

        @sqlselect34           CHAR (255),

        @sqlselect35           CHAR (255),

        @sqlselect36           CHAR (255),

        @sqlfrom               CHAR (255),

        @sqlwhere01            CHAR (255),

        @sqlwhere02            CHAR (255),

        @winspection_num       CHAR (255),

        @wcntrlg_insp_num          CHAR  (255),

        @wrelated_activity     CHAR (255),

        @wUBI                  CHAR (255),

        @wbus_loc              CHAR (255),

        @wacct_num             CHAR (255),

        @wnaics                CHAR (255), -- A filtering where clause that matches either NAICS or SIC,

                                           -- depending on the length of @naics.

        @wdate                 CHAR (255), -- Uses @date_type, @from_date, and @to_date.

        @wassignment_type      CHAR (255),

        @wstandard_violated    CHAR (255),

        @winspection_type      CHAR (255),

        @wsite_region          CHAR (255),

        @wcsho_region          CHAR (255),

        @wspecial_tracking     CHAR (255),

        @won_appeal            CHAR (255),

        @wcsho_id              CHAR (255),

        @wsafety_health        CHAR (255),

        @wdba_name             CHAR (255),

        @wlegal_name           CHAR (255),

 

        -- Used by cursors.

        @insptn_enfact_id      NUMERIC (10, 0),

        @related_activity_num  NUMERIC (10, 0)

 

CREATE TABLE #TempInspectionList (encase_bus_id            CHAR (9)        NULL,

                                  encase_acct_id           CHAR (8)        NULL,

                                  encase_bus_dba_name      VARCHAR (50)    NULL,

                                  encase_bus_legal_name    VARCHAR (50)    NULL,

                                  encase_id                NUMERIC (10, 0) NULL,

                                  enfact_osha_num          VARCHAR (10)    NULL,

                                  insptn_enfact_id         NUMERIC (10, 0) NULL,

                                  ViolationTypeP           VARCHAR (50)    NULL,

                                  ViolationTypeW           VARCHAR (50)    NULL,

                                  ViolationTypeRS          VARCHAR (50)    NULL,

                                  ViolationTypeFS          VARCHAR (50)    NULL,

                                  ViolationTypeS           VARCHAR (50)    NULL,

                                  ViolationTypeR           VARCHAR (50)    NULL,

                                  ViolationTypeFG          VARCHAR (50)    NULL,

                                  ViolationTypeG           VARCHAR (50)    NULL,

                                  enreac_related_enfact_id VARCHAR (250)   NULL,

                                  insptn_type_code         CHAR (50)       NULL,

                                  enfact_asgnd_csho_num    CHAR (5)        NULL,

                                  encase_site_regn_num     CHAR (1)        NULL,

                                  csho_pri_regn_num        CHAR (1)        NULL,

                                  insptn_appeal_flg        CHAR (1)        NULL,

                                  insptn_cittn_flg         CHAR (1)        NULL,

                                  insptn_cittn_date        DATETIME        NULL)

SELECT @sqlinsert   = 'INSERT INTO #TempInspectionList '

SELECT @sqlselect01 = 'SELECT top 20 encase_bus_id,'

SELECT @sqlselect02 = '       encase_acct_id,'

SELECT @sqlselect03 = '       encase_bus_dba_name,'

SELECT @sqlselect04 = '       encase_bus_legal_name,'

SELECT @sqlselect05 = '       C.encase_id,'

SELECT @sqlselect06 = '       COALESCE (CONVERT (VARCHAR, A.enfact_osha_num),'

SELECT @sqlselect07 = '                 ''None'') AS enfact_osha_num,'

SELECT @sqlselect08 = '       insptn_enfact_id,'

SELECT @sqlselect09 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''P''  AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeP,'

SELECT @sqlselect10 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''W''  AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeW,'

SELECT @sqlselect11 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''RS'' AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeRS,'

SELECT @sqlselect12 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''FS'' AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeFS,'

SELECT @sqlselect13 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''S''  AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeS,'

SELECT @sqlselect14 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''R''  AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeR,'

SELECT @sqlselect15 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''FG'' AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeFG,'

SELECT @sqlselect16 = '       (SELECT violn_type_state_code + ''-'' + convert (varchar, count (*)) FROM Violn WHERE violn_type_state_code = ''G''  AND insptn_id = A.enfact_id AND violn_del_date IS NULL GROUP BY violn_type_state_code) AS ViolationTypeG,'

SELECT @sqlselect17 = '       '''' AS enreac_related_enfact_id,'

SELECT @sqlselect18 = '       (SELECT cntl_desc'

SELECT @sqlselect19 = '          FROM cntl'

SELECT @sqlselect20 = '         WHERE cntl_code = I.insptn_type_code'

SELECT @sqlselect21 = '           AND cntl_list_name = ''InspectionType'''

SELECT @sqlselect22 = '           AND cntl_del_date IS NULL) AS insptn_type_code,'

SELECT @sqlselect23 = '       A.enfact_asgnd_csho_num,'

SELECT @sqlselect24 = '       (SELECT SUBSTRING (cntl_code, 1, 1)'

SELECT @sqlselect25 = '          FROM cntl'

SELECT @sqlselect26 = '         WHERE cntl_alt_osha_code = C.encase_site_regn_num'

SELECT @sqlselect27 = '           AND cntl_del_date IS NULL) AS encase_site_regn_num,'

SELECT @sqlselect27a = '       (SELECT SUBSTRING (cntl_code, 1, 1)'

SELECT @sqlselect27b = '          FROM cntl'

SELECT @sqlselect27c = '         WHERE cntl_alt_osha_code = csho.csho_pri_regn_num'

SELECT @sqlselect27d = '           AND cntl_del_date IS NULL) AS csho_pri_regn_num,'

SELECT @sqlselect28 = '       CASE'

SELECT @sqlselect29 = '          WHEN insptn_appeal_flg = 1 THEN ''Y'''

SELECT @sqlselect30 = '          ELSE                            ''N'''

SELECT @sqlselect31 = '       END AS insptn_appeal_flg,'

SELECT @sqlselect32 = '       CASE'

SELECT @sqlselect33 = '          WHEN insptn_cittn_flg  = 1 THEN ''Y'''

SELECT @sqlselect34 = '          ELSE                   ''N'''

SELECT @sqlselect35 = '       END AS insptn_cittn_flg,'

SELECT @sqlselect36 = '       insptn_cittn_date'

 

-- Determine FROM clause.

IF @related_activity IS NOT NULL

   SELECT @sqlfrom  = '  FROM Encase C, Enfact A, Insptn I, CSHO, Enreac R, Enfact RA '

ELSE IF @standard_violated IS NOT NULL

   BEGIN

      IF @special_tracking IS NOT NULL

         SELECT @sqlfrom  = '  FROM Encase C, Enfact A, Insptn I, CSHO, Violn V, Optval O '

      ELSE

         SELECT @sqlfrom  = '  FROM Encase C, Enfact A, Insptn I, CSHO, Violn V '

   END

ELSE IF @special_tracking IS NOT NULL

   SELECT @sqlfrom  = '  FROM Encase C, Enfact A, Insptn I, CSHO, Optval O '

ELSE

   SELECT @sqlfrom  = '  FROM Encase C, Enfact A, Insptn I, CSHO '

 

                        -- By starting out this way, all the other where clauses can start with AND.

SELECT @sqlwhere01  = ' WHERE C.encase_id = A.encase_id AND A.enfact_id = I.insptn_enfact_id AND A.enfact_type_code = ''I'' AND CSHO.csho_num = C.encase_pri_csho_num'

SELECT @sqlwhere02  = '   AND encase_del_date IS NULL AND A.enfact_del_date IS NULL AND insptn_del_date IS NULL AND CSHO.csho_del_date IS NULL '

 

-- Determine additional joining WHERE clauses.

IF @inspection_num IS NOT NULL

   SELECT @winspection_num = '   AND A.enfact_osha_num = ' + CONVERT (VARCHAR, @inspection_num) + ' '

ELSE

   SELECT @winspection_num = ''

 

IF @cntrlg_insp_num IS NOT NULL

   SELECT @wcntrlg_insp_num = '   AND I.insptn_fi_cntl_osha_num = ' + CONVERT (VARCHAR, @cntrlg_insp_num) + ' '

ELSE

   SELECT @wcntrlg_insp_num = ''

 

IF @related_activity IS NOT NULL

   SELECT @wrelated_activity  = '   AND R.enreac_enfact_id = A.enfact_id AND R.enreac_related_enfact_id = RA.enfact_id AND RA.enfact_osha_num = ' +

                                CONVERT (VARCHAR, @related_activity) + ' AND R.enreac_del_date IS NULL and RA.enfact_del_date IS NULL '

ELSE

   SELECT @wrelated_activity  = ''

 

-- Determine filtering WHERE clauses.

IF @UBI IS NOT NULL

   SELECT @wUBI = '   AND encase_bus_id = ''' + @UBI + ''' '

ELSE

   SELECT @wUBI = ''

 

IF @bus_loc IS NOT NULL

   SELECT @wbus_loc = '   AND encase_busloc_id = ''' + @bus_loc + ''' '

ELSE

   SELECT @wbus_loc = ''

 

IF @acct_num IS NOT NULL

   SELECT @wacct_num = '   AND encase_acct_id = ''' + @acct_num + ''' '

ELSE

   SELECT @wacct_num = ''

 

-- If @naics is 4 characters long or less, use it to match the SIC code,

-- If it is 5 or 6 characters long, use it to match the NAICS code.

IF @naics IS NOT NULL

   BEGIN

      IF DATALENGTH (LTRIM (RTRIM (@naics))) <= 4

         SELECT @wnaics = '   AND encase_sic_code = ''' + @naics + ''' '

      ELSE

         SELECT @wnaics = '   AND encase_naics_code = ''' + @naics + ''' '

   END

ELSE

   SELECT @wnaics = ''

 

-- Determine WHERE clause for date.

SELECT @wdate =

       CASE

          WHEN @date_type = 'OC' THEN '  AND insptn_opconf_date between ''' + CONVERT (VARCHAR, @from_date) + ''' AND ''' + CONVERT (VARCHAR, @to_date) + ''' '

          WHEN @date_type = 'CC' THEN '  AND insptn_clconf_date between ''' + CONVERT (VARCHAR, @from_date) + ''' AND ''' + CONVERT (VARCHAR, @to_date) + ''' '

          WHEN @date_type = 'A'  THEN '  AND insptn_appeal_date between ''' + CONVERT (VARCHAR, @from_date) + ''' AND ''' + CONVERT (VARCHAR, @to_date) + ''' '

          WHEN @date_type = 'CI' THEN '  AND insptn_cittn_date  between ''' + CONVERT (VARCHAR, @from_date) + ''' AND ''' + CONVERT (VARCHAR, @to_date) + ''' '

          ELSE                        ''

       END

 

IF @assignment_type IS NOT NULL

   SELECT @wassignment_type = '   AND encase_assgn_type_code = ''' + @assignment_type + ''' '

ELSE

   SELECT @wassignment_type = ''

 

IF @standard_violated IS NOT NULL

      SELECT @wstandard_violated = '   AND V.insptn_id = I.insptn_enfact_id AND violn_wac_win_code = ''' + @standard_violated + ''' AND violn_del_date IS NULL '

ELSE

      SELECT @wstandard_violated = ''

 

IF @inspection_type = 'T'

       begin

                 SELECT @winspection_type = '   AND I.insptn_type_code in ( ''T'', ''W'', ''O'', ''U'', ''Z'' ) '

       end 

IF @inspection_type = 'R'

       begin

           SELECT @winspection_type = '   AND I.insptn_type_code  in ( ''R'', ''K'' ) '

       end 

 

IF @inspection_type <> 'T' OR @inspection_type <> 'R'

   IF @inspection_type is not NULL

       begin

         SELECT @winspection_type = '   AND I.insptn_type_code = ''' + @inspection_type + ''' '

        end

 

IF @inspection_type IS NULL

       begin    

          SELECT @winspection_type = ''

       end

IF @site_region IS NOT NULL

   SELECT @wsite_region = '   AND C.encase_site_regn_num = (SELECT cntl_alt_osha_code FROM cntl WHERE cntl_list_name = ''cplRegion'' AND cntl_code = ''' + @site_region + ''' AND cntl_del_date IS NULL) '

ELSE

   SELECT @wsite_region = ''

 

IF @csho_region IS NOT NULL

   SELECT @wcsho_region = '   AND csho.csho_pri_regn_num = (SELECT cntl_alt_osha_code FROM cntl WHERE cntl_list_name = ''cplRegion'' AND cntl_code = ''' + @csho_region + ''' AND cntl_del_date IS NULL) '

ELSE

   SELECT @wcsho_region = ''

 

IF @special_tracking IS NOT NULL

   SELECT @wspecial_tracking = '   AND O.optval_type_code = ''' + SUBSTRING (@special_tracking, 1, 1)                                          + ''' ' +

                               '   AND O.optval_num = '''       + SUBSTRING (@special_tracking, 2, 2)                                          + ''' ' +

                               '   AND O.optval_desc = '''      + SUBSTRING (@special_tracking, 5, DATALENGTH (RTRIM (@special_tracking)) - 4) + ''' ' +

                               '   AND O.optval_key_code = A.enfact_type_code ' +

                               '   AND O.optval_key_num  = A.enfact_id ' +

                               '   AND O.optval_del_date IS NULL '

ELSE

   SELECT @wspecial_tracking = ''

 

IF @on_appeal IS NOT NULL

   BEGIN

      IF @on_appeal = '1'

         SELECT @won_appeal = '   AND I.insptn_appeal_flg = 1 '

      ELSE IF @on_appeal = '0'

         SELECT @won_appeal = '   AND I.insptn_appeal_flg = 0 '

      ELSE

         SELECT @won_appeal = ''

   END

ELSE

   SELECT @won_appeal = ''

 

IF @csho_id IS NOT NULL

   SELECT @wcsho_id = '   AND A.enfact_asgnd_csho_num = ''' + @csho_id + ''' '

ELSE

   SELECT @wcsho_id = ''

 

IF @safety_health IS NOT NULL

   BEGIN

      IF @safety_health = 'S'

         SELECT @wsafety_health = '   AND C.encase_pgm_code = ''S'' '

      ELSE IF @safety_health = 'H'

         SELECT @wsafety_health = '   AND C.encase_pgm_code = ''H'' '

      ELSE

         SELECT @wsafety_health = ''

   END

ELSE

   SELECT @wsafety_health = ''

 

IF @dba_name IS NOT NULL

   SELECT @wdba_name = '   AND C.encase_bus_dba_name LIKE ''' + @dba_name + '%'' '

ELSE

   SELECT @wdba_name = ''

 

IF @legal_name IS NOT NULL

   SELECT @wlegal_name = '   AND C.encase_bus_legal_name LIKE ''' + @legal_name + '%'' '

ELSE

   SELECT @wlegal_name = ''

 

IF @verbose IS NOT NULL -- If @verbose is 'Y' (or any non null value) let the user know what SQL was run.

   BEGIN

      CREATE TABLE #ExecutedSQL (TextLine CHAR (255) NULL)

 

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlinsert)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect01)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect02)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect03)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect04)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect05)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect06)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect07)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect08)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect09)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect10)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect11)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect12)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect13)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect14)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect15)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect16)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect17)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect18)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect19)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect20)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect21)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect22)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect23)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect24)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect25)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect26)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect27)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect27a)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect27b)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect27c)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect27d)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect28)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect29)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect30)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect31)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect32)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect33)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect34)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect35)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlselect36)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlfrom)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlwhere01)

      INSERT #ExecutedSQL (TextLine) VALUES (@sqlwhere02)

      INSERT #ExecutedSQL (TextLine) VALUES (@winspection_num) 

      INSERT #ExecutedSQL (TextLine) VALUES (@wcntrlg_insp_num) 

      INSERT #ExecutedSQL (TextLine) VALUES (@wrelated_activity)

      INSERT #ExecutedSQL (TextLine) VALUES (@wbus_loc)

      INSERT #ExecutedSQL (TextLine) VALUES (@wacct_num)

      INSERT #ExecutedSQL (TextLine) VALUES (@wnaics)

      INSERT #ExecutedSQL (TextLine) VALUES (@wdate)

      INSERT #ExecutedSQL (TextLine) VALUES (@wassignment_type)

      INSERT #ExecutedSQL (TextLine) VALUES (@wstandard_violated)

      INSERT #ExecutedSQL (TextLine) VALUES (@winspection_type)

      INSERT #ExecutedSQL (TextLine) VALUES (@wsite_region)

      INSERT #ExecutedSQL (TextLine) VALUES (@wcsho_region)

      INSERT #ExecutedSQL (TextLine) VALUES (@wspecial_tracking)

      INSERT #ExecutedSQL (TextLine) VALUES (@won_appeal)

      INSERT #ExecutedSQL (TextLine) VALUES (@wcsho_id)

      INSERT #ExecutedSQL (TextLine) VALUES (@wsafety_health)

      INSERT #ExecutedSQL (TextLine) VALUES (@wdba_name)

      INSERT #ExecutedSQL (TextLine) VALUES (@wlegal_name)

 

      SELECT TextLine as 'Executed SQL'

        FROM #ExecutedSQL

       WHERE TextLine <> ''

         AND TextLine IS NOT NULL

   END

 

-- Execute SELECT statement that inserts into #TempInspectionList.

exec (@sqlinsert          +

      @sqlselect01        +

      @sqlselect02        +

      @sqlselect03        +

      @sqlselect04        +

      @sqlselect05        +

      @sqlselect06        +

      @sqlselect07        +

      @sqlselect08        +

      @sqlselect09        +

      @sqlselect10        +

      @sqlselect11        +

      @sqlselect12        +

      @sqlselect13        +

      @sqlselect14        +

      @sqlselect15        +

      @sqlselect16        +

      @sqlselect17        +

      @sqlselect18        +

      @sqlselect19        +

      @sqlselect20        +

      @sqlselect21        +

      @sqlselect22        +

      @sqlselect23        +

      @sqlselect24        +

      @sqlselect25        +

      @sqlselect26        +

      @sqlselect27        +

      @sqlselect27a       +

      @sqlselect27b       +

      @sqlselect27c       +

      @sqlselect27d       +

      @sqlselect28        +

      @sqlselect29        +

      @sqlselect30        +

      @sqlselect31        +

      @sqlselect32        +

      @sqlselect33        +

      @sqlselect34        +

      @sqlselect35     +

      @sqlselect36        +

      @sqlfrom            +

      @sqlwhere01         +

      @sqlwhere02         +

      @winspection_num    +

       @wcntrlg_insp_num      +

      @wrelated_activity  +

      @wUBI               +

      @wbus_loc           +

      @wacct_num          +

      @wnaics             +

      @wdate              +

      @wassignment_type   +

      @wstandard_violated +

      @winspection_type   +

      @wsite_region       +

      @wcsho_region       +

      @wspecial_tracking  +

      @won_appeal         +

      @wcsho_id           +

      @wsafety_health     +

      @wdba_name          +

      @wlegal_name)

 

-- Go through every row in the #TempInspectionList table and replace the enreac_related_enfact_id

-- column with a list of the related activities given in the enreac table.

DECLARE inspection_cursor CURSOR

FOR

SELECT insptn_enfact_id

  FROM #TempInspectionList

 

OPEN inspection_cursor

 

FETCH inspection_cursor INTO @insptn_enfact_id

WHILE  @@fetch_status = 0

   BEGIN

      DECLARE related_activity_cursor CURSOR

      FOR

      SELECT enfact_osha_num

        FROM enreac R,

             enfact A

       WHERE enreac_enfact_id = @insptn_enfact_id

         AND enreac_del_date IS NULL

         AND enfact_del_date IS NULL

         AND enreac_related_enfact_id <> @insptn_enfact_id -- Kinda pointless to list an inspection as related to itself.

         AND enfact_osha_num IS NOT NULL

         AND A.enfact_id = R.enreac_related_enfact_id

       ORDER BY enfact_osha_num

 

      OPEN related_activity_cursor

 

      FETCH related_activity_cursor INTO @related_activity_num

      WHILE  @@fetch_status = 0

         BEGIN

            UPDATE #TempInspectionList

               SET enreac_related_enfact_id = LTRIM (enreac_related_enfact_id +

                                                     ' ' + 

                                                     CONVERT (VARCHAR, @related_activity_num))

             WHERE insptn_enfact_id = @insptn_enfact_id

 

            FETCH related_activity_cursor INTO @related_activity_num

         END

 

      CLOSE related_activity_cursor

 

      DEALLOCATE related_activity_cursor

 

      FETCH inspection_cursor INTO @insptn_enfact_id

   END

 

CLOSE inspection_cursor

 

DEALLOCATE inspection_cursor

 
SELECT *

  FROM #TempInspectionList
go


_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200
__________________
_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200
 
Old May 25th, 2004, 03:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Is there any way to chage the UI to use a grid that supports sorting within the grid? THen you just need to return data...

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee
 
Old May 25th, 2004, 03:33 PM
Authorized User
 
Join Date: Jun 2003
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here are some of the 1 to many fields they can search on:

Search by:
Inspection #
Controlling Insp #
Related Activity #
UBI Bus Loc Account #
Search by Date From Date - To Date
Opening Conference Date
Closing Conference Date
Appeal Date
Citation Date
On Appeal
Site Region
CSHO ID
CSHO Region
Safety/Health
....and many more

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200
 
Old May 25th, 2004, 04:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

ANd a grid would allow that...


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee





Similar Threads
Thread Thread Starter Forum Replies Last Post
How big is too big? chroniclemaster1 XML 5 September 17th, 2012 01:07 AM
facing big problem Manish Malhotra Classic ASP Basics 1 June 19th, 2006 01:35 PM
How can I upload the big file shan168 VS.NET 2002/2003 1 April 1st, 2005 09:48 AM
Big models question darrinps BOOK: Expert One-on-One J2EE Design and Development 0 June 4th, 2003 02:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.