Create variable and use in one stored procedure 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
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.