September 16, 2010 at 3:21 pm
Would anyone give some insite on not using cursors and how to use set based logic to do what I want to do in a far faster manner.
Thank you,
Jim
attached is sp, sample data and table defs
September 16, 2010 at 3:42 pm
CREATE PROCEDURE DBO.GPM_SAFETY_INSPECTION_LOAD_INSPECTION(
@LOCATION VARCHAR(50),
@EMPLOYEE VARCHAR(50),
@DATE VARCHAR(10))
AS
DECLARE
@COUNT INT,
@QUARTER INT,
@MONTH INT
SELECT @MONTH = MONTH(GETDATE())
SET @QUARTER = DatePart(quarter, GetDate())
SELECT
@COUNT = COUNT(*)
FROM
GPM_SAFETY_INSPECTION
WHERE
SLOCATION = @LOCATION AND
IYEAR = YEAR(GETDATE()) AND
IQUARTER = @QUARTER
IF @COUNT = 0
BEGIN
-- need a table variable to hold the identity columns
DECLARE @GPM_SAFETY_INSPECTION TABLE (
[Inspection_ID] [int] NOT NULL);
INSERT INTO GPM_SAFETY_INSPECTION(
SLOCATION,
SINSPECTED_BY,
DINSPECTION_DATE,
IYEAR,
IQUARTER)
-- output the assigned identity column value into the table variable
OUTPUT inserted.Inspection_ID
INTO @GPM_SAFETY_INSPECTION
VALUES(
@LOCATION,
@EMPLOYEE,
@DATE,
YEAR(GETDATE()),
@QUARTER)
-- insert into the details table by cross-joining the table variable
-- to the description table.
INSERT INTO GPM_SAFETY_INSPECTION_DETAILS(INSPECTION_ID, SDESCRIPTION)
SELECT t1.Inspection_ID, g.sDescription
FROM @GPM_SAFETY_INSPECTION t1
CROSS JOIN dbo.GPM_Safety_Inspection_Descriptions g
END
GO
Oh crud... just noticed that you posted this in the SQL 7/2000 forum. This code is for SQL 2005+. What version of SQL are you using?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 4:57 pm
Wayne this is a follow on for the posting in SQL 2000 Forum:
Table definition:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GPM_Safety_Inspection]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GPM_Safety_Inspection]
GO
/****** Object: Table [dbo].[GPM_Safety_Inspection] Script Date: 9/16/2010 7:01:08 PM ******/
CREATE TABLE [dbo].[GPM_Safety_Inspection] (
[Inspection_ID] [int] IDENTITY (1, 1) NOT NULL ,
[sLocation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sInspected_By] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dInspection_Date] [datetime] NULL ,
[iYear] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iQuarter] [int] NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GPM_Safety_Inspection_Descriptions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GPM_Safety_Inspection_Descriptions]
GO
CREATE TABLE [dbo].[GPM_Safety_Inspection_Descriptions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[sDescription] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iSort_Order1] [int] NULL ,
[iSort_Order2] [int] NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GPM_Safety_Inspection_Descriptions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GPM_Safety_Inspection_Descriptions]
GO
CREATE TABLE [dbo].[GPM_Safety_Inspection_Descriptions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[sDescription] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iSort_Order1] [int] NULL ,
[iSort_Order2] [int] NULL
) ON [PRIMARY]
GO
http://www.sqlservercentral.com/Forums/Topic986761-9-1.aspx#bm987546
And here is the only data supplied:
SET IDENTITY_INSERT GPM_Safety_Inspection_Descriptions ON
INSERT INTO GPM_Safety_Inspection_Descriptions
(ID, sDescription, iSort_Order1, iSort_Order2)
SELECT '1','Are Material Safety Data Sheets (MSDS) on file and available for all employees?','1','1' UNION ALL
SELECT '2','Work Comp Certificates are posted in each site office','1','2' UNION ALL
SELECT '3','Employees are not engaged in ergonomic hazards, e.g. awkward posture, prolonged repetitive motion, contact stress, etc.','2','1' UNION ALL
SELECT '4','Mechanical aids/equipment such as carts and dollies are provided where needed','2','2' UNION ALL
SELECT '5','Employees work areas are adequately illuminated.','3','1'
In the 2000 forum - it was a problem using the debugger, well that was solved. OP now wants to find a set based technique rather than using a cursor.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply