Calling Commonly Used SQL (reusable code)

[jduhler@hotmail.com]

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 CallingProcedur e
@MYVAR INT
AS
BEGIN

SELECT
dbo.myfunction( @MYVAR)
,(SELECT ANOTHERFIELD FROM ANOTHERTABLE) ANOTHERFIELDVAL UE

END

When I run CallingProcedur e, 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

[Hugo Kornelis]

Re: Calling Commonly Used SQL (reusable code)

On Thu, 31 Jul 2008 09:54:03 -0700 (PDT), jduhler@hotmail .com wrote:

(snip)

>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

Hi JD,

You are telling SQL Server to return a string. SQL Server will do that,
without bothering to see if that string happens to be a valid query.

If you want to return the result of the query, you'll have to execute
it, store the result, and than return the result.

CREATE FUNCTION dbo.myfunction (@MYVAR int)
RETURNS VARCHAR(2000)
AS
BEGIN;
DECLARE @Res vanrchar(2000);
SET @Res = (SELECT A FROM B WHERE C = @MYVAR);
RETURN @Res;
END;

Note however that encapsulating reused code in seperate functions may
save on maintenance, but you pay the price in performance. SQL Server
can no longer optimize the queries as good as it would otherwise do.
Take for instance this query:

SELECT Col1, Col2
FROM SomeTable
WHERE SomeColumn = @SomeValue;

You can consider to "refactor" this to use two functions, one to fetch
the Col1 value and one to fetch the Col2 value, like this:

SELECT dbo.GetCol1From Table(@SomeValu e) AS Col1,
dbo.GetCol2From Table(@SomeValu e) AS Col2;

But the result will be that SQL Server has to access the same table
twice, reading the same row both times. You have just doubled execution
time!

And it gets even worse if joins are involved.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[--CELKO--]

Re: Calling Commonly Used SQL (reusable code)

It is a little hard to tell what you want from the skeleton code you
posted, but SQL has VIEWs. These are "virtual tables" and they are
declared by

CREATE VIEW <view name[(<column name list>)]
AS
<table expression>; -- usually a SELECT statement

You use them as if they were base tables in your queries. You can drop
or alter a view, so that the next time it is referenced, any changes
will be in effect.

[Erland Sommarskog]

Re: Calling Commonly Used SQL (reusable code)

(jduhler@hotmai l.com) writes:

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

Unfortunately very simplified examples from your real-world chores
can be very misleading. It's not at all really clear to me what
you want to achieve.

But generally, T-SQL as a general programming language is not as powerful
as a modern object-oriented language. Its strengths lie elsewhere.

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

As I said, I don't really understand what you are trying to achieve,
but it looks you like you could make use of a preprocessor. No, there
is no such thing in SQL Server, but if you have access to C++, running
the SQL code through the C preprocessor and then load it with SQLCMD
is not that difficult.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx