 |
| 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
|
|
|
|
May 25th, 2004, 03:06 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
May 25th, 2004, 03:12 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
|
|
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
|
|
May 25th, 2004, 03:33 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
May 25th, 2004, 04:01 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
|
|
ANd a grid would allow that...
Hal Levy
Web Developer, PDI Inc.
NOT a Wiley/Wrox Employee
|
|
|
