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

SQL IF/CASE Statement

By now I’m sure you must have came across multiple situations, where you have to use a IF(in SQL terms CASE) condition. I know I’ve and to be honest I’m actually a big fan of it.

If you are new and want to learn about IF/CASE in SQL, I think you should first start off w/ a basic like this and then to dive into more in depth, try out this and this. These articles are well written w/ easy to follow examples.

Yesterday, I have to create an very interesting query and of course IF/CASE came to the rescue. However, this query involves little bit of calculations so after some web search, I’m glad I found this (and especially the 4th example).

SQL IF/CASE Statement

SQL query result into a table

I find this pretty useful plenty of time.

First an INSERT,

<cfquery name="Into_tempWorkHistory" datasource="myDataSource">
INSERT INTO toTable(WONUM, WorkerType, Worker, WorkStartDate)
SELECT WONUM, WorkerType, Worker, WorkStartDate
FROM fromTable

Second, an UPDATE could be also done via query result, read more

SET WorkEndDate = T.Date
WHERE T2.WONUM = '#ARGUMENTS.WONUM#' AND T2.Action = 'R' AND T2.WorkerType = '#WorkerType#'
AND T2.Worker IN (SELECT T1.Worker
WHERE T1.WorkerType = '#WorkerType#'

*NOTE: Make sure not to use ALIAS on the UPDATE. And above UPDATE query is a mere example only, above update will do the job however result query doesn’t behave as expexted .

Added Bonus: I also found some nifty new (SQL 2005 and above) features. Have a look.

SQL query result into a table

Dynamically get SQL query result row numbers

I think this is super useful. Here is my example,

<cfquery name="qGetTHIS_FBS" datasource="myDataSource">
SELECT ROW_NUMBER() OVER(ORDER BY M.Item_Nm DESC) AS rNo, M.Item_Nm, M.Manufacturer, M.Description, M.CaseSize, M.Unit_Desc, M.Case_Desc, F.VolumeQTY, F.UOM, P.UnitSize
FROM M_Table M, P_Table P, F_Table F
WHERE M.Item_Nm = P.ItemNo AND F.PO = P.PO AND F.PO = '#ARGUMENTS.PO#' AND M.InUse = 'Y'

Read more and learn more from here.

Dynamically get SQL query result row numbers