Is there a way in SQL Server T-SQL to store commonly used SQL
statements in a function, stored proc, or system variable? That way
if that code ever changes I can change it in one place.
If I use the code below in 100 different SQL statements... can I
stored it somewhere and just call it from another SQL statement?
For example, I want to call this very simplified statement 100 times
from different stored procs::
(SELECT A FROM B WHERE C=@MYVAR) FIELDVALUE
I have tried using a function such as:
CREATE FUNCTION myfunction (@MYVAR int)
RETURNS VARCHAR(2000)
AS
BEGIN
RETURN 'SELECT A FROM B WHERE C=' + @MYVAR + ' FIELDVALUE'
END
Then I attempt to call that function from another stored procedure
such as:
CREATE PROCEDURE CallingProcedure
@MYVAR INT
AS
BEGIN
SELECT
dbo.myfunction(@MYVAR)
,(SELECT ANOTHERFIELD FROM ANOTHERTABLE) ANOTHERFIELDVALUE
END
When I run CallingProcedure, it shows the field name returned from the
function as "(SELECT A FROM B WHERE C=@MYVAR) FIELDVALUE" with a
value of null. I want it to be able to show the select statement, so
that I can see a field named "FIELDVALUE".
Thank you
-JD