How to "Include" another T-SQL Script

[NeoPa]

I am looking to piece together a few T-SQL scripts into a single script in a way similar to the c "include BlahBlah.H" feature.

I've searched Online books and Googled but everything comes up with solutions that invoke an external script via OSQL or ISQL rather than a continuation of inline code (which is what I'm after).

It may well be that the reason is that this is not a supported feature. If so, please let me know. My experience level is that I get to play in here once every two or three years, so I get quite rusty.

If there is a more helpful answer that's fine too of course, but I'd like the view of someone with the current experience :)

[NeoPa]

I found http://bytes.com/groups/ms-sql/83051...call-sql-files in the archives. I suspect this means there is no support for this idea in T-SQL.

If anyone knows anything to the contrary then please post. Otherwise, I suspect this question can be considered to be answered.

[Plater]

Could you save them as stored procedures and call them that way?

[NeoPa]

I suppose if I got my thinking around how SQL Server generally works, that would be the best solution. I'm coming at it from a sort of different mind-set (file system based) because I'm not in SQL Server mode at the moment.

I think that is actually the logical solution. Thanks Plater :)

[ck9663]

That's actually the answer to your question. In C, the compiled program will not be able to see other module that are not included during compilation. Unless it's another executable that you want to call.

In SQL Server, once the function or stored proc is created, and you have the necessary rights, you can use it. If there are multiple copies of these modules in separate database or even within the database but of different owner, qualifying the call will allow you to use it.

-- CK

[Plater]

I *am* curious how you would actually use a set of results returned from a stored procedure.
I cannot seem to find a way to use a SELECT statement on it, so unless it returns a scaler, how can it be used?

[ck9663]

Depending on what value want returned. You can either use a ReturnCode technique or use a Return Parameter (aka pass by reference) or use a Function instead of stored proc.

Happy Coding.


-- CK

[Plater]

So there is no way to use a SELECT statement on a stored procedure results (it makes sense I guess since you never know what will be returned, one result set, two sets, etc etc)?

[ck9663]

Could you give an example of what you're trying to do?

-- CK

[Plater]

Well for instance in a number of queries i have a sub query like:
SomeColumn in (SELECT SomeSimilarColumn FROM SomeTable WHERE (conditions) )

And it would be nice to have that elsewhere so if I make changes to it, everything that uses it is all set.

That sounds like a function I guess except that the subquery has been known to take parameters from its encompassing stored procedure.
Such as

 
WHERE
(
   @myParam = 1 AND (conditions)
OR
   (some other conditions)
)
 

[NeoPa]

I'm not at work today so I can't check this very easily (and I'm rusty as I mentioned), but I think a Stored Procedure is some code which doesn't return a value at all. Like a subroutine, but not a function.

If returned values are required, then a User Defined Function is required and that can return SELECTed results as a cursor if necessary. Otherwise it can return scalar values in more ordinary formats (int; bigint; nvarchar; etc; etc).

Please correct me if I'm wrong. I certainly could be, but that's my understanding.

[ck9663]

A function can return a value like this:

 
if MyFunction(@myparameter) = Value
 
 

or multiple values that could act like a table like this:

 
select column1, column2, column3 from MyFunction(@myparameter)
 
 

If you need the value of these columns as returned like a table, you can either use a cursor or some other technique to process each row and column.

As always, your function could be a single or a group of t-sql statements and, yes, you change it once and everything that uses it gets affected.

Hope I make sense :)

-- CK

[Plater]

Hmmm.
I have quite a few stored procedures that do this:

WHERE
(--
    (
        @IDNumber in 
        (
            SELECT WO.OwnerID 
            FROM WebOwnership as WO
            WHERE WO.IDType='PowerUser'
        )
        OR
            T.Zone in
            (--Only the correct zone(s)?
                SELECT O.IDString 
                FROM WebOwnership as O
                WHERE 
                    O.OwnerID=@IDNumber
                AND
                    O.IDType='ClientZone'
            )
    )
AND
    T.ClientID in
    (--Only the correct Company(-ies)
        SELECT O.IDNumber 
        FROM WebOwnership as O
        WHERE 
            O.OwnerID=@IDNumber
        AND
            O.IDType='Client'
        AND
            O.OwnerType='Manager'
    )
)
 

it would be nice to say like:

(
@IDNumber in SomeFunc1()
OR
T.Zone in SomeFunc2(@IDNumber)
)
AND
T.ClientID in SomeFunc3(@IDNumber)
 

I will have to look into this