467,915 Members | 1,382 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

How to "Include" another T-SQL Script

Expert Mod 16PB
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 :)
Dec 10 '08 #1
  • viewed: 17184
12 Replies
Expert Mod 16PB
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.
Dec 10 '08 #2
Expert 4TB
Could you save them as stored procedures and call them that way?
Dec 10 '08 #3
Expert Mod 16PB
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 :)
Dec 11 '08 #4
Expert 2GB
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
Dec 12 '08 #5
Expert 4TB
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?
Dec 15 '08 #6
Expert 2GB
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
Dec 15 '08 #7
Expert 4TB
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)?
Dec 15 '08 #8
Expert 2GB
Could you give an example of what you're trying to do?

-- CK
Dec 16 '08 #9
Expert 4TB
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
Expand|Select|Wrap|Line Numbers
  2. WHERE
  3. (
  4.    @myParam = 1 AND (conditions)
  5. OR
  6.    (some other conditions)
  7. )
Dec 16 '08 #10
Expert Mod 16PB
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.
Dec 16 '08 #11
Expert 2GB
A function can return a value like this:

Expand|Select|Wrap|Line Numbers
  2. if MyFunction(@myparameter) = Value
or multiple values that could act like a table like this:

Expand|Select|Wrap|Line Numbers
  2. 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
Dec 16 '08 #12
Expert 4TB
I have quite a few stored procedures that do this:
Expand|Select|Wrap|Line Numbers
  1. WHERE
  2. (--
  3.     (
  4.         @IDNumber in 
  5.         (
  6.             SELECT WO.OwnerID 
  7.             FROM WebOwnership as WO
  8.             WHERE WO.IDType='PowerUser'
  9.         )
  10.         OR
  11.             T.Zone in
  12.             (--Only the correct zone(s)?
  13.                 SELECT O.IDString 
  14.                 FROM WebOwnership as O
  15.                 WHERE 
  16.                     O.OwnerID=@IDNumber
  17.                 AND
  18.                     O.IDType='ClientZone'
  19.             )
  20.     )
  21. AND
  22.     T.ClientID in
  23.     (--Only the correct Company(-ies)
  24.         SELECT O.IDNumber 
  25.         FROM WebOwnership as O
  26.         WHERE 
  27.             O.OwnerID=@IDNumber
  28.         AND
  29.             O.IDType='Client'
  30.         AND
  31.             O.OwnerType='Manager'
  32.     )
  33. )

it would be nice to say like:
Expand|Select|Wrap|Line Numbers
  1. (
  2. @IDNumber in SomeFunc1()
  3. OR
  4. T.Zone in SomeFunc2(@IDNumber)
  5. )
  6. AND
  7. T.ClientID in SomeFunc3(@IDNumber)
I will have to look into this
Dec 16 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Danny Anderson | last post: by
6 posts views Thread by wukexin | last post: by
4 posts views Thread by Exits Funnel | last post: by
18 posts views Thread by Tuckers | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.