I have a very long stored procedure with a number of blocks of logic in it that insert to different tables. Here's one such block
I have the following table with a unique constraint on 'data'
[id] [int] IDENTITY(1,1) NOT NULL
[data] [varchar](512) NULL
This block attempts to insert a value to 'data'. if that value is unique, it is inserted. In all cases the relevant data id is returned
BEGIN TRY
INSERT INTO Data SELECT @data;
END TRY
BEGIN CATCH
END CATCH
SET @data_id = (SELECT id FROM Data WHERE data = @data);
When I include this block of code in my original stored procedure, it runs fine. However, for the sake of neatness I and DRY, I thought I'd abstract it out to a sub-procedure, as the same block is called in a few other SPs
ALTER PROCEDURE [dbo].[q_Data_TryInsert]
@data nvarchar(512),
@id INT OUTPUT
AS
BEGIN
BEGIN TRY
INSERT INTO Data SELECT @data;
END TRY
BEGIN CATCH
END CATCH
SET @id = (SELECT id FROM Data WHERE data = @data);
END
I then call this abstracted SP like so
EXEC [q_Data_TryInsert] @data, @data_id OUTPUT
The abstracted SP slows down the whole process my several orders of magnitude, even though the code is the same.
Why is this happening?
please change
to
And change
to
EDIT: to get what you need the store procedure needs to be reworked in this way