SQL Stored Procedures, SQL Functions and datatype TABEL

I was working on a SQL Stored Procedure (SP) that that executes other independent functions to get the main SP to run successfully. So I immediate segregated my independent functions into SQL functions (User Defined Functions, “udf”).

Quickly I ran into some roadblocks, which then I discovered something even cooler.

Let’s start with the problem.

I wanted to handle my main query in by itself so that, maintaining it is lot simpler. So I created SQL UDF that runs the query. To make it easier I made the function return a datatype TABLE. Then my attempt was to pass that datatype TABLE as a parameter back to the main SP.  After hours of reading and figuring out how to get this done, I remember reading somewhere given that my DataBase is SQL 2005, it is not possible. (NOTE: In the same article I think I remember reading, SQL 2008 and above accepts datatype TABLE as a input parameter into a SP. Not exactly the same article but here is an article that worth reading.)

As I pointed out earlier, for obvious reasons, I’m determined to keep my main query separate but now I have to find a way to get it done.

In this case, given that this just return a query results, I settled on having a separate view table and then calling it through SP.

Inside of my SP now I would do something like this, (nothing fancy here)

SELECT TOP 10 Col1, Col2, Col3
FROM ViewTable_vw

Now with some of the query parameters are in the view, it’s not Ideal (because yes, everything is in that view but it’s not portable enough as in if I want to pass my own query parameters to this view) but it’ll get the job done

Later on, I found out that there is way of doing this which is just like the way I want (i.e. easily pass in my own parameters.)

NOTE: below example is taken from this.

First create the udf,

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S INNER JOIN Orders AS O
              ON S.ShipperID = O.ShipVia
        WHERE O.Freight > @FreightParm

then in your SP you pass $500 as you parameter and call it as,

FROM LargeOrderShippers( $500 )

And let say for some reason, you only want the ShipperID you can do that by,

SET @sID = (SELECT ShipperID FROM LargeOrderShippers($500))

OR if you want to use SELECT

SELECT @sID = dbo.[LargeOrderShippers]($500)

Note: above I believe the dbo. and brackets (“[]”) are important in SQL 2005

I think this is very nifty, I know that I’ll be using this very often.

SQL Stored Procedures, SQL Functions and datatype TABEL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s