I have a number of UDF (user defined functions) in SQL2005 that are called from various stored procedures.
I did not realise quite how much these slow down retrieval of records, so I'd like to figure out a faster way to create strings. I've looked at CTEs / inline scalar functions but I can't see how I can put any logic in them based on the data I have.
I need to check for NULLs and change a string based on what data I have.
This one below builds the URL for each real estate listing in a nice standardised way that I can use in many procedures, so all pages are referenced exactly the same across the site.
I know I should do this on the web server, but before I go down that route I'd like to know how I could improve things in SQL.
URL STRING
CREATE FUNCTION NW_PROPERTY_URL
(@ID int,
@PType varchar(20),
@Country varchar(30),
@Region varchar(30),
@County varchar(30),
@Location varchar(50),
)
RETURNS nvarchar(500)
AS
BEGIN
DECLARE @URL nvarchar(500)
SET @URL = 'http://www.example.com/' + CONVERT(varchar,@ID) + '/'
IF @PType is not NULL
BEGIN
SET @URL = @URL + RTRIM(LTRIM(@PType)) + '-'
END
IF @Country is not NULL
BEGIN
SET @URL = @URL + RTRIM(LTRIM(@Country)) + '-'
END
IF @Region is not NULL
BEGIN
SET @URL = @URL + RTRIM(LTRIM(@Region)) + '-'
END
IF @County is not NULL
BEGIN
SET @URL = @URL + RTRIM(LTRIM(@County)) + '-'
END
IF @Location is not NULL
BEGIN
SET @URL = @URL + RTRIM(LTRIM(@Location)) + '-'
END
/* check if last character is a space, and remove */
IF (RIGHT(@URL, 1) = '-')
BEGIN
SET @URL = LEFT(@URL, LEN(@URL) - 1)
END
/* add trailing slash */
Set @URL = @URL + '/'
/* replace all spaces with hyphens */
Set @URL = Replace(@URL,' ','-')
Set @URL = Replace(@URL,'--','-')
Set @URL = Replace(@URL,'''','-')
Set @URL = Replace(@URL,',','')
RETURN @URL
END
EXAMPLE
The output would be something like:
www.example.com/34234/House-USA-Florida-Miami-Miami-Beach/
REAL ESTATE TITLE
I also have one that reads language translations from the database based on the input language chosen on the website to create a header title for each real estate listing, which is much more involved.
Is there anything I can do to either speed the function up, or move it within the procedure?
CREATE FUNCTION REALESTATE_TITLE
(
@ForceTitle varchar(200),
@ForSaleOrRent varchar(10),
@Bedrooms int,
@PType varchar(30),
@Location varchar(30),
@Urbanisation varchar(150),
@LandSQM int,
@County varchar(30),
@Country varchar(30),
@Lang varchar(2)
)
RETURNS nvarchar(300)
AS
BEGIN
/* START VARIABLES */
DECLARE @PropertyTitle nvarchar(300)
DECLARE @LandSQM2 nvarchar(30)
DECLARE @lang_BEDROOMS nvarchar(30)
DECLARE @lang_PType nvarchar(30)
DECLARE @lang_FORSALEORRENT nvarchar(30)
DECLARE @lang_LAND nvarchar(30)
DECLARE @lang_WITH nvarchar(30)
/* CHECK FOR TITLE */
IF @ForceTitle is not NULL
BEGIN
SET @PropertyTitle = @ForceTitle
END
ELSE
BEGIN
/* GET TRANSLATION OF 'WITH' AND 'LAND' */
SET @lang_WITH = N' ' + (SELECT LOWER(With2) FROM Langs WHERE Lang=@Lang)
SET @lang_LAND = N' ' + (SELECT LOWER(Land) FROM Langs WHERE Lang=@Lang)
/* FOR SALE OR RENT */
IF @ForSaleOrRent = 'For Sale'
SET @lang_FORSALEORRENT = N' ' + (SELECT LOWER(ForSale) FROM Langs WHERE Lang=@Lang)
ELSE IF @ForSaleOrRent = 'Rental'
SET @lang_FORSALEORRENT = N' ' + (SELECT LOWER(ForRent) FROM Langs WHERE Lang=@Lang)
ELSE
SET @lang_FORSALEORRENT = N''
/* CHECK IF LOCATION IS SAME AS COUNTY */
IF @Location = @County
SET @County = N''
/* URBANISATION AND LOCATION */
IF @Urbanisation is not NULL
BEGIN
SET @Urbanisation = N' in ' + @Urbanisation
SET @Location = N', ' + @Location
IF NOT @County = ''
SET @County = N', ' + @County
END
ELSE
BEGIN
SET @Urbanisation = N''
SET @Location = N' in ' + @Location
IF NOT @County = ''
SET @County = N', ' + @County
END
IF @PType = N'Plot of land'
BEGIN
IF @LandSQM is not null
BEGIN
SET @LandSQM2 = REPLACE(CONVERT(nvarchar, CAST(@LandSQM AS money), 1),'.00','') + N'm2 '
SET @lang_PType = LOWER((SELECT TOP 1 Translation FROM Langs_PTypes WHERE PType = @PType AND Lang = @Lang))
END
ELSE
BEGIN
SET @LandSQM2 = N''
SET @lang_PType = (SELECT TOP 1 Translation FROM Langs_PTypes WHERE PType = @PType AND Lang = @Lang)
END
SET @PropertyTitle = @LandSQM2 + @lang_PType + @lang_FORSALEORRENT + @Urbanisation + @Location + @County
END
ELSE
BEGIN
IF @LandSQM is not null
SET @LandSQM2 = @lang_WITH + N' ' + REPLACE(CONVERT(nvarchar, CAST(@LandSQM AS money), 1),'.00','') + N'm2' + @lang_LAND
ELSE
SET @LandSQM2 = N''
/* BEDROOMS */
IF @Bedrooms is not NULL
IF @Bedrooms = 0
SET @lang_BEDROOMS = N''
ELSE
SET @lang_BEDROOMS = CONVERT(NVARCHAR,@Bedrooms) + N' ' + (SELECT LOWER(Bedroom) FROM Langs WHERE Lang=@Lang) + N' '
ELSE
SET @lang_BEDROOMS = N''
/* REAL ESTATE TYPE */
IF @PType is not NULL
SET @lang_PType = LOWER((SELECT TOP 1 Translation FROM Langs_PTypes WHERE PType = @PType AND Lang = @Lang))
ELSE
SET @lang_PType = N'property'
SET @PropertyTitle = @lang_BEDROOMS + @lang_PType + @lang_FORSALEORRENT + @LandSQM2 + @Urbanisation + @Location + @County
END
END
RETURN @PropertyTitle
END
EXAMPLE
The output would be something like:
5 bedroom House with 1,000 m2 of land in Miami Beach, Miami
