1
\$\begingroup\$

This finds a value via a stored procedure then runs a second process in the same stored procedure rather than calling a second stored procedure.

When a call is 'simple' i.e. not Where x y then... I keep the stored procedure, even when I need to get a variable, in the same procedure.

I'd like to see if the concept that works is the best concept, has any bad habits or can be improved.

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetImage]
(
-- Add the parameters for the function here
@SiteID varchar(100)
)
-- Is the below required? 
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
--We limit the length of the image name on import of images
DECLARE @SiteImage varchar(100)
--Client changes visible ID so we thus need to find 'Database ID' (SiteID) 
DECLARE @SiteIDOriginal varchar(100)
-- Add the T-SQL statements to compute the return value here
--Get 'Database' ID
SELECT [@SiteIDOriginal] = SiteID FROM TblPropertyDetails WHERE ClientsSiteID=[@SiteID] 
--Find the ImageName based on the Original ID (Database ID) 
SELECT @SiteImage = ImageName FROM TblImages WHERE SITEID = @SiteIDOriginal
-- Return the result of the function
RETURN @SiteImage

END
\$\endgroup\$
0
2
\$\begingroup\$

You can make it shorter by having a single query, thus avoiding the extra variable @SiteIDOriginal and possibly make it a little faster:

ALTER FUNCTION [dbo].[GetImage]
(
    -- Add the parameters for the function here
    @SiteID varchar(100)
)
-- It is required as it specifies return value type
RETURNS varchar(100)
AS
BEGIN
    DECLARE @SiteImage varchar(100) = (
        SELECT ImageName
        FROM TblImages Img 
            JOIN TblPropertyDetails PD ON PD.ClientsSiteID = Img.SiteID 
        WHERE PD.ClientsSiteID = @SiteID
    ) 

    RETURN @SiteImage
END
GO

Other than that, something more of aesthetics:

  • try to have uniform formatting (blanks between variables and operators etc.)
  • use indentation (I usually treat SQL as Java or C# code where every "begin" and "end" trigger a new level of indentation). This is particularly useful when reading rather large objects.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.