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
   )
AS
BEGIN
   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
   RETURN
END

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

SELECT *
FROM LargeOrderShippers( $500 )

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

DECLARE @sID INT
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

Levenshtein Distance Algorithm

Using the aforementioned algorithm I was able to make a more meaningful string search.  It’s quite amazing and works like a charm.

I highly encourage you to read about this. If the wiki is kinda “too much” for you (I know it was for me.) try this.

And for more practical SQL knowledge, this is what I modeled after mine.

Levenshtein Distance Algorithm

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
WHERE WONUM = '#ARGUMENTS.WONUM#'
</cfquery>

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

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

*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'
<cfquery>

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
 FROM TABLE1 AS T1,
    (SELECT DISTINCT ItemNo, SUM(UnitCount) AS uCount
    FROM TABLE2
    WHERE PO = '#ARGUMENTS.PO#'
    GROUP BY ItemNo) AS T2
 WHERE T2.ItemNo = T1.ItemNo 
 </cfquery>

(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

Something you shouldn’t be doing in SQL View tables

Hands down everyone agrees SQL view tables are very nifty and make life so much easier (for programmers, that is).

Today SQL Team’s Jeff posted a very educational article about when it comes to use SQL view table. It’s something I can’t believe that happens, so Thank you. I bet this is going to be useful for many more.

BTW, I highly recommend subscribing to SQL Team because they always have good stuff to share.  

Something you shouldn’t be doing in SQL View tables

Remove everything after first space – SQL query

[Ok ok, I know this blog is not mainly about coding but the amount of time I spent on this SQL query, I figured it needs some kind of recognition and I figured I can’t be the only one so thought of sharing.]

So bare with me, if you are not into coding, and if you are, this query may be simple, but for me I feel triumphant as I managed to do this by myself. (Of cause with online reading, and feel free to use this query in codings.)

Before getting to the query let me explain the scenario.

You have a table with list of names of Manufactures and may be some manufacture’s name may contain 2 ports. (Ex. XXX International). So if you want just to get the first name (XXX) you can use the query below.

[NOTE: these coding if MS SQL, I’m sure with a little bit of tweaking you can make to work for your Dbase.]

{—–

SELECT DISTINCT left(Manufacturer,PATINDEX(‘% %’, RTRIM(LTRIM(Manufacturer)))) as FirstName

FROM tbl

UNION

SELECT DISTINCT Manufacturer

FROM tbl

WHERE PATINDEX(‘% %’, RTRIM(LTRIM(Manufacturer))) = ‘0’

—–}

First part, trims left and right of ‘Manufacturer’ (to make sure not leading and trailing spaces) then gets the position where the first space is(that’s what PATINDEX does in MSSQL, I think in some dBases it’s called POSITION, check it out) and then get the string LEFT to the first space. [The reason why I fell in love with this query is coz, it’s universal i.e. this query works no matter how long the first name is]

What’s interesting about the first part was, if the Manufacturer only has a one name, then first part of the query will NOT return them. So that’s where second part come into play.

Second part, get’s all Manufactures missed in the first part i.e. All Manufacturers with only one name. That’s why I’m making PATINDEX = 0, coz for Manufacturers with only name PATINDEX is 0.

So a simple UNION command, unions and give me the results I wanted.

Please comment and suggest if you think you know a better way of achieving this[I’m sure there is] and like I said before feel free to use this in your codings.

Remove everything after first space – SQL query