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
