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
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 @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.