April 24, 2012 at 11:51 am
ALTER FUNCTION [dbo].[FormatClinicID] (@vClinicID varchar(30))
RETURNS varchar(30)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @vTrimmedClinicID varchar(30)
set @vTrimmedClinicID = LTrim(RTrim(@vClinicID))
DECLARE @vClinicID_1 varchar(30);
if ( Substring(@vTrimmedClinicID, 1, 3) = 'MD-' )
begin
set @vClinicID_1 = Substring (@vTrimmedClinicID, 4, Len(@vTrimmedClinicID)-3)
end
else
begin
set @vClinicID_1 = @vClinicID
end
RETURN(@vClinicID_1);
END;
I want the above function to trim (MD-) from ClinicID column of a table before the data is inserted into the table. But the column still allows the values with (MD-). Please I need help on what to do.
April 24, 2012 at 11:53 am
Can you just show us some sample data?
April 24, 2012 at 11:56 am
Also, please show us the code you are running using this function.
April 24, 2012 at 12:07 pm
If this question is in continuation of two other (separate threads, on data type mismatch), then function won’t be a proper solution for you. But go ahead with function approach for time being. Once done, please share the code with us; we will help you to place it in stored procedure in better way.
April 24, 2012 at 7:19 pm
My crystal ball tells me:
1. You're calling this function in a loop.
2. In that loop, you're creating each of the target column values in a separate @ local variable
3. At the end, you do a single record INSERT of the built up values.
God forgive me, but that is just horrible and I won't be party to helping you fix your function!
What you should be doing should look something like this:
INSERT INTO WhateverTable (clinicid --, other fields here
)
SELECT CASE SUBSTRING(clinicid,1,3) WHEN 'MD-' THEN SUBSTRING(clinicid,4,LEN(clinicid)) ELSE clinicid END
-- , other fields here
FROM YourSourceTable
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply