July 27, 2004 at 9:28 am
Hi,
No doubt this is obvious to everyone but me, but I'm pretty new so be kind!
I have a sp which pulls a recordset into a cursor based on a variable that was passed.
As the cursor moves through the recordset it assigns the value of the recordset fields to variables.
Depending on the values of a couple of the variables Im changing the value to something else. I'm then inserting the resulting variables into a Temporary Table for later retrevial.
I FETCH the next row with the cursor and continue doing the above again until I reach the end of the recordset.
Right at the end of the sp I do a SELECT * FROM #TemporaryTable so that the data is sent back and I can see it.
Is this the best/only way I can go about doing this. I know cursors should be avoided wherever possible, ditto temp tables, but I can't think of any other way of doing this.
I'd be grateful for any suggestions. If requried I'll post the sp.
Windows 2008 Server | SQL Server 2008
July 27, 2004 at 9:33 am
It's too open ended a question to answer. If possible, a single or a few queries might be better, Without seeing some code or knowing what you are trying to do, it's hard to say. The size of the data (# rows) also matters.
July 27, 2004 at 9:40 am
Below is the SP in question. The number of rows returned won't ever by higher than 100 and thats high estimate.
Basically the only thing I'm doing here is changing the value returned in the StageType field to be nothing if the last row returned the same value.
CREATE PROCEDURE [dbo].[spJobStatus_Default] @ID varchar(38) AS
SET NOCOUNT ON
DECLARE @StageType varchar(50), @MessageDateTime varchar(50), @status varchar(50), @JobID varchar(50), @Conformance varchar(50), @VehicleReg varchar(50)
DECLARE @LoadID varchar(50), @PODBy varchar(50), @HYPERLINK varchar(50), @RefID varchar(50), @Old nvarchar(50), @StageTypeReturn nvarchar(50)
CREATE TABLE [#StatusTemp] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[StageType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MessageDateTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Conformance] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VehicleReg] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoadID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hyperlink] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RefID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_#StatusTemp] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
DECLARE TableCursor CURSOR FOR
SELECT (CASE
WHEN S.StageType = 'C' THEN 'Collection'
WHEN S.StageType = 'A' THEN 'Trunk ' + Convert(varchar(2),S.Stage-1)
WHEN S.StageType = 'F' THEN 'Full Move'
WHEN S.StageType = 'D' THEN 'Delivery'
ELSE
'Office'
END),
LEFT((CONVERT(varchar(10),M.MessageDate,103) + ' ' + CONVERT(varchar(10),M.MessageTime,108)),16) AS MessageDateTime,
M.Status, M.JobID, M.Conformance, L.VehicleReg, M.LoadID, M.PODBy, M.RecID AS HYPERLINK, M.RefID
FROM MessagesIN AS M
LEFT OUTER JOIN JB_JobStages AS S ON M.RefID = S.JobID AND M.ID = S.StageID
LEFT OUTER JOIN Manifests AS L ON M.LoadID = L.LoadID AND M.CompanyID = L.CompanyID
WHERE M.RefID = @ID AND ((M.LoadID IS NOT NULL AND M.StatusCode = 0 ) OR (M.LoadID IS NULL AND M.StatusCode = 0 ) OR (M.LoadID IS NOT NULL AND M.StatusCode 0))
ORDER BY S.Stage, S.StageType, M.MessageDateTime ASC
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @StageType, @MessageDateTime, @status, @JobID, @Conformance, @VehicleReg, @LoadID, @PODBy, @HYPERLINK, @RefID --Pull the first record into the variables
IF @@Fetch_Status = 0
BEGIN
WHILE @@Fetch_Status = 0 --While we are not at the end of the recordser (ie: -1) then build the SQL
BEGIN
IF @Old = @StageType
BEGIN
SET @StageTypeReturn = ' '
END
ELSE
BEGIN
SET @StageTypeReturn = @StageType
END
INSERT INTO #StatusTemp (StageType, MessageDateTime, Status, JobID, Conformance, VehicleReg, LoadID, PODBy, HYPERLINK, RefID)
VALUES (@StageTypeReturn, @MessageDateTime, @status, @JobID, @Conformance, @VehicleReg, @LoadID, @PODBy, @HYPERLINK, @RefID)
SELECT @Old = @StageType
FETCH NEXT FROM TableCursor INTO @StageType, @MessageDateTime, @status, @JobID, @Conformance, @VehicleReg, @LoadID, @PODBy, @HYPERLINK, @RefID --Move to the next record.
END
END
CLOSE TableCursor
DEALLOCATE TableCursor
SET NOCOUNT OFF
SELECT * FROM #StatusTemp
DROP TABLE #StatusTemp
SET NOCOUNT OFF
GO
Windows 2008 Server | SQL Server 2008
July 27, 2004 at 10:04 am
If you can't do some ordering and looking for the previous value in the query (and I'm not sure you can), then this will work. For 100 rows, not sure it matters or is worth spending too much time on.
July 28, 2004 at 12:44 am
I am not sure there is much that you can do here unless you have some kinda logic that compares the previous value with the next one. In which case what you have done seems to be just fine.
Maybe you can use a Table Variable (Refer BOL) if you are not comfortable using a Temp Table...Cant guarantee that it will be very beneficial tho. But its a good practice for sure.
Also if you have the records sorted which you do, you can go in for a while loop rather than a cursor after dumping the records into a table (either temp or table variable).
Lemme know if this helps.
Cheers!
Arvind
July 28, 2004 at 1:47 am
Thanks, everyone.
I wasn't really looking for someone to code me anything up. It does work and return the right results. I just wanted either a) re-assurance that this was not a really bad way to go about things. b) A suggestion of an alternative method.
dnivrav, I'll investigate the While loop as this will be more efficient I would think.
Regards,
Kevin.
Windows 2008 Server | SQL Server 2008
July 28, 2004 at 5:35 am
OK I agree there is no reason to drag this out because of the size of the table returned. But the Ques. came to me as "Is it any better to make this into an UDF?" After all the temp table creation seems to be optimized in UDF's with automatic table return. And some of the script would then maybe able to be done with simple update statement with a simple loop.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply