# 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.