November 27, 2013 at 1:01 pm
I have a query that returns a bunch of rows. I have an sp that takes 3 of the columns in the query as parameters that I want to execute for each row. I can do that easily enough with a cursor, but I thought I'd try to eliminate one more task where I fall back on using cursors.
Is there a good way to do that without the cursor?
November 27, 2013 at 1:09 pm
rray 44280 (11/27/2013)
I have a query that returns a bunch of rows. I have an sp that takes 3 of the columns in the query as parameters that I want to execute for each row. I can do that easily enough with a cursor, but I thought I'd try to eliminate one more task where I fall back on using cursors.Is there a good way to do that without the cursor?
most likely, the whole thing could be redesigned to a set based operation do the same work the current stored procedure does on those three parameters;
the more you show, the more we can help; can you show us the proc you want to call?
Lowell
November 27, 2013 at 1:32 pm
Given a query that returns an IPCode (PK), a ProfileTypeCode and a PhoneNumber, and with all other required values constant. ReturnSetInd is passed as 'N', so no need to make allowance for handling that. I can avoid any error that would trigger transaction rollbacks since I'm controlling all input. I could rewrite the SP as long as it does the job.
The sp does a whole ton of validation on the passed parameters. Assuming all is well it does a simple insert:
CREATE PROCEDURE dbo.proc_publicAddPhoneProfile
(
@SessionTokennvarchar(32)='', -- Required
@IPCodeint=-1, -- Required
@ProfileTypeCodeint=-1, -- Required
@FormatCodeint=-1, -- Required
@PhoneNumbernvarchar(255)='', -- Required
@PhoneExtensionnvarchar(255)='', -- Required
@ValidationReferencenvarchar(255)='', -- Required
@PreValidationIndnchar(1)='', -- Optional
@ReturnSetIndnchar(1)='Y' -- Optional
)
AS
BEGIN
SET NOCOUNT ON
-- 300 lines or so of validation
BEGIN TRAN Named_Tran
INSERT dbo.PhoneProfile
(IPCode,
ProfileTypeCode,
StatusCode,
FormatCode,
PhoneNumber,
PhoneExtension,
ValidationReference,
OperatorID,
UpdateDate)
VALUES
(@IPCode,
@ProfileTypeCode,
1,
@FormatCode,
@PhoneNumber,
@PhoneExtension,
@ValidationReference,
@i_OperatorID,
GETDATE())
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN Named_Tran
SET @i_ReturnMessage = dbo.udf_SystemMessage(130760,@i_SessionLanguageCode)
RAISERROR (@i_ReturnMessage, 16, 1)
RETURN @@ERROR
END
IF @PhoneNumber <> ''
BEGIN
INSERT dbo.IPSearch
(SearchValue,
SearchColumn,
IPCode,
ProfileCode,
ProfileTypeCode,
SequenceNumber)
VALUES
(@PhoneNumber,
'PHONENUMBER',
@IPCode,
@i_ProfileCode,
@ProfileTypeCode,
@@IDENTITY)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN Named_Tran
SET @i_ReturnMessage = dbo.udf_SystemMessage(131145,@i_SessionLanguageCode)
RAISERROR (@i_ReturnMessage, 16, 1)
RETURN @@ERROR
END
END
COMMIT TRAN Named_Tran
IF @ReturnSetInd = 'Y'
BEGIN
SELECT @IPCode AS IPCode,
@ProfileTypeCode AS ProfileTypeCode,
@@IDENTITY AS SequenceNumber
END
RETURN @@ERROR
END
November 27, 2013 at 1:40 pm
I guess the big question is:
What kind of validation is performed?
Would it be possible to store the "bunch of rows" to a temp table with an additional column to mark the rows that failed the validation?
If so, you could just use a single INSERT based on that temp table with whatever condition needs to be met.
November 27, 2013 at 4:22 pm
Yes, I could manage that. I think I'm missing an INSERT syntax that would do the insert from the temp table! There may be a gaping hole in my understanding of INSERT!
November 27, 2013 at 5:26 pm
Could you do something like this?
CREATE TYPE T AS TABLE (C1 INT, C2 INT);
GO
CREATE PROCEDURE SP1 (@T T READONLY)
AS BEGIN
-- Do your validations, etc.
SELECT * FROM @T;
END
GO
DECLARE @T T;
-- Populate the results of yoru query into @T
INSERT INTO @T
SELECT 1,2 UNION ALL SELECT 2,3 UNION ALL SELECT 3,4;
EXEC SP1 @T=@T;
GO
DROP PROCEDURE SP1;
DROP TYPE T;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply