By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

How to "Include" another T-SQL Script

NeoPa
Expert Mod 15k+
P: 31,535
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
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,535
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

Plater
Expert 5K+
P: 7,872
Could you save them as stored procedures and call them that way?
Dec 10 '08 #3

NeoPa
Expert Mod 15k+
P: 31,535
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

ck9663
Expert 2.5K+
P: 2,878
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

Plater
Expert 5K+
P: 7,872
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

ck9663
Expert 2.5K+
P: 2,878
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

Plater
Expert 5K+
P: 7,872
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

ck9663
Expert 2.5K+
P: 2,878
Could you give an example of what you're trying to do?

-- CK
Dec 16 '08 #9

Plater
Expert 5K+
P: 7,872
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
  1.  
  2. WHERE
  3. (
  4.    @myParam = 1 AND (conditions)
  5. OR
  6.    (some other conditions)
  7. )
  8.  
Dec 16 '08 #10

NeoPa
Expert Mod 15k+
P: 31,535
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

ck9663
Expert 2.5K+
P: 2,878
A function can return a value like this:

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. select column1, column2, column3 from MyFunction(@myparameter)
  3.  
  4.  
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

Plater
Expert 5K+
P: 7,872
Hmmm.
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. )
  34.  

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)
  8.  
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.