June 21, 2017 at 1:07 pm
Using an iTVF that produces a scalar value certainly works as a work around, at least in new development... The biggest problem is with existing code. It would be nice to be able to go in and convert existing scalar function with iSVFs w/o having to spend time locating and then altering every single proc that references the existing scalar functions due to syntax differences.
June 21, 2017 at 1:12 pm
Jason A. Long - Wednesday, June 21, 2017 1:07 PMUsing an iTVF that produces a scalar value certainly works as a work around, at least in new development... The biggest problem is with existing code. It would be nice to be able to go in and convert existing scalar function with iSVFs w/o having to spend time locating and then altering every single proc that references the existing scalar functions due to syntax differences.
You sure hit the nail on the head with that one. In my environment, I've done the ones I can, but I have a central repository of functions that can be used by any user in any database. They're so widely used that trying to change them would be insane and border on suicidal.
June 21, 2017 at 1:53 pm
Ed Wagner - Wednesday, June 21, 2017 1:12 PMJason A. Long - Wednesday, June 21, 2017 1:07 PMUsing an iTVF that produces a scalar value certainly works as a work around, at least in new development... The biggest problem is with existing code. It would be nice to be able to go in and convert existing scalar function with iSVFs w/o having to spend time locating and then altering every single proc that references the existing scalar functions due to syntax differences.You sure hit the nail on the head with that one. In my environment, I've done the ones I can, but I have a central repository of functions that can be used by any user in any database. They're so widely used that trying to change them would be insane and border on suicidal.
Ditto!
One of my longer term goals is to go through, one at a time, I identify the worst & most prevalent scalars, create iTVF versions, update all procs & functions (yes, we have scalars referencing scalars 🙁 ), and actually DROP the old scalars...
This guy is #1 on my hit list...
CREATE FUNCTION [dbo].[PadString]
/*
* Names and comments removed to protect the guilty...
*/ ( @Input VARCHAR(MAX) ,
@PaddingChar CHAR(1) ,
@FinalLength VARCHAR(MAX) ,
@LeftOrRight CHAR(1) )
RETURNS VARCHAR(MAX)
AS
BEGIN DECLARE @output VARCHAR(MAX) = ''
--IF LEN(@Input) > @FinalLength
-- SET @Output = NULL
--IF @LeftOrRight = 'L'
-- SET @Output = REPLICATE(@PaddingChar, @FinalLength - LEN(@Input)) + @Input
--IF @LeftOrRight = 'R'
-- SET @Output = @Input + REPLICATE(@PaddingChar, @FinalLength - LEN(@Input))
DECLARE @i INT = 0 SET @input = ISNULL(LTRIM(RTRIM(@input)), '')
SET @PaddingChar = ISNULL(@PaddingChar, '')
SET @FinalLength = ISNULL(@FinalLength, 0)
SET @LeftOrRight = ISNULL(@LeftOrRight, 'L') DECLARE @InputLength INT = LEN(@input)
WHILE @i < @FinalLength - @InputLength
BEGIN
SET @output = @output + @PaddingChar
SET @i = @i + 1
END IF @LeftOrRight = 'R'
SET @output = @input + @output
ELSE
SET @output = @output + @input
-- Return the result of the function
RETURN @output
END
And his replacement...
CREATE FUNCTION dbo.tfn_PadString
/* ======================================================================
06/15/2017 JL, Created to replace both the dbo.PadString scalar function.
====================================================================== */
(
@Input VARCHAR(max) = 'Hello World',
@PaddingChar char(1) = '~',
@FinalLength INT = 20,
@LeftOrRight CHAR(1) = 'L'
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
[Output] = CASE
WHEN LEN(@Input) >= @FinalLength THEN @Input
WHEN @LeftOrRight = 'L' THEN RIGHT(CONCAT(REPLICATE(@PaddingChar, @FinalLength), @Input), @FinalLength)
WHEN @LeftOrRight = 'R' THEN LEFT(CONCAT(@Input, REPLICATE(@PaddingChar, @FinalLength)), @FinalLength)
ELSE 'Non valid L/R char'
END;
June 21, 2017 at 1:59 pm
I can see why. But rewriting the SFs to ITVFs isn't the hard part. For me, the hard part is finding everywhere they're used and fixing all the code. Finding the database code is simple. 🙂 Finding all the front-end code with embedded SQL is hard. :angry::crazy:
June 21, 2017 at 2:05 pm
That's where I'm VERY VERY lucky... It was ordained from up on high, long ago, that applications may only call procs... No app generated, ad-hoc code allowed.
There "may" be a few bits & pieces in some ancillary apps but, to the best of my knowledge, all business critical applications are ad-hoc sql free..
June 22, 2017 at 5:12 am
Jason A. Long - Wednesday, June 21, 2017 2:05 PMThat's where I'm VERY VERY lucky... It was ordained from up on high, long ago, that applications may only call procs... No app generated, ad-hoc code allowed.
There "may" be a few bits & pieces in some ancillary apps but, to the best of my knowledge, all business critical applications are ad-hoc sql free..
Now that's really nice. I wish I were in that situation. You know, you could make the argument that any embedded SQL (other than calling stored procedures) is a violation of policy and the author needs to fix it. I know nobody wants downtime for anything, but it seems that policy can help you in this case.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply