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 CTE tables

Recently I had to tackle a some-what impossible task. Well, it’s not impossible-impossible but I wanted to get it done in a more non-traditional way.
Long story short, it involves hierarchy structure converted into a sql rational db, a query can be run to get results.
It may sounds simple in theory but it is a challenging task depending on the complexity of the hierarchy.
So to get some insight and to review my work I came across, something useful yet never knew function in sql.
It’s called ‘Command Table Expressions’ (CTE).
It’s pretty nifty and I’m sure it’ll cone in handy any day now.
Read about it.

sql CTE tables

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

SQL connecting One Query to another

Just by looking at it seems like this is just an SQL join (or in Coldfusion just a QoQ) but I ran into a complication because one query contains SQL arithmatic and other doesn’t. Obviously SQL JOIN is also out of the picture.

So this is my solution,

<cfquery name="q1" datasource="myDataSource">
 SELECT T1.ItemNo, T1.UnitSize, T2.uCount, (T1.UnitSize * T2.uCount) AS ItemUsedAmt
    (SELECT DISTINCT ItemNo, SUM(UnitCount) AS uCount
    GROUP BY ItemNo) AS T2
 WHERE T2.ItemNo = T1.ItemNo 

(my answer is inspired with internet research and I wish i wrote down the website that lead me to this solution.)

SQL connecting One Query to another