Where Are User Defined Functions Stored

1

Hi Guys,

I have created a UDF in SQL Server and the function executed successfully yet I do not see it anywhere in my database. Where did my UDF get stored? I have looked in the logical place which is the functions folder. No Joy. Here's the code for the function which includes a DROP statement

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
IF OBJECT_ID (N'udf_MaxCounterValueReached', N'FN') IS NOT NULL
DROP FUNCTION udf_MaxCounterValueReached;
GO
CREATE FUNCTION udf_MaxCounterValueReached(@CurrentValue int, @MaxValue int)

RETURNS Bit
AS
BEGIN
-- Declare the return variable here
DECLARE @MaxValueReached bit

-- Add the T-SQL statements to compute the return value here

If @CurrentValue > @MaxValue
set @MaxValueReached = 1
ELSE
set @MaxValueReached = 0
-- Return the result of the function
RETURN @MaxValueReached

END
GO

2

Make sure you are looking in the database where you created it. The SSMS object explorer may need refreshing. Right-click and refresh.
You can query for the object via t-sql

SELECT * FROM sys.objects where name = 'udf_MaxCounterValueReached';

3

If this isn't your full function - and you intend something quite different you can disregard this recommendation. If, however - this is your function then you can rewrite this to the following:

Create Function dbo.udf_MaxCounterValueReached(@CurrentValue int, @MaxValue int)
Returns bit
As
Begin
Return iif(@CurrentValue > @MaxValue, 1, 0)
End

Ideally - you could then convert this to an inline-table valued function that will perform much better across larger sets of data:

Create Function dbo.udf_MaxCounterValueReached(@CurrentValue int, @MaxValue int)
Returns table
As
Return Select iif(@CurrentValue > @MaxValue, 1, 0) As MaxValueReached
1 Like
4

Jeff, thanks for the assist. I was able to correct the initial problem. The UDF is now visible within my database. Not sure what happened. Likely pointing to the wrong database when I executed the code. Thank you for your suggestions. I will keep them for future reference. I realize my code may not be as clean and elegant as it could be. This project is just a personal project and a learning experience for me. The database is a trading card database and will not grow in size in terms of actual data. In fact, it will likely grow smaller as additional duplicate card data is removed. My goal is to try and convert as much of my existing client side code into equivalent server side stored procedures and functions and then call the stored procedures from my application. My knowledge of SQL Server is basically book based and this project is my attempt to change that by using as much of what SQL Server has to offer in my project as well as increase my knowledge of VB.Net. The function code provided is complete. It performs a very simple check that is part of a larger process. I thought it best to start small. I've never been a big fan of IIF which is why I did not use it. I do appreciate your suggestions and assistance.

5

Yep, don't use IIF, it's a non-SQL extension that just complicates your code.

But do write your functions as efficiently as possible, in particular avoiding local variables unless absolutely necessary, like so:

CREATE FUNCTION udf_MaxCounterValueReached (
    @CurrentValue int, 
    @MaxValue int
    )
RETURNS Bit
AS
BEGIN
RETURN (
    SELECT  CASE WHEN @CurrentValue > @MaxValue  THEN 1  ELSE 0  END  AS ReturnValue
)
END
GO
6

Scott,

Thanks for your assistance. Seems to be quite a helpful crew here and I appreciate it. I've never liked IIF in any language. While it does streamline things a bit, I prefer to use more standard coding constructs such as if else it just avoids confusion. I'm fairly decent database designer and programmer but this little venture breaks new ground for me. I've had past experience with Oracle from the design perspective and only minimal experience with stored procs. The original database was Access. That's my preferred tool for prototyping. I ran into database size limitations with Access because I'm storing multiple BLOB images for each card so I had to upsize to Server. It presented me with a good opportunity to expand my hands on experience with both Server and .Net. Again thanks for the assist.

7

You might want to find it and delete it from that DB! sp_MSForEachDB is a bit fragile, but this should do the job:

DECLARE	@ObjectName sysname = 'udf_MaxCounterValueReached'
DECLARE	@SQL nvarchar(2000) = 'USE [?];SELECT [DBName]=db_name(), type, type_desc FROM sys.objects WHERE name = ''' + @ObjectName + ''''
EXEC sp_MSForEachDB @SQL
8

Not really worried about it. This is not a production environment just my local install on my PC. Thanks anyway

9

There's no reason to avoid a "non-SQL" extension because true portability is a myth. It's like telling someone they can only use the 4 basic functions on their scientific calculator because someone somewhere in the world might not have a scientific calculator. Even if you do relegate yourself to using only "4 functions" on your calculator, the calculator you might need to migrate to might be one that uses Reverse Polish Notation and then you're still dead in the water.

Make it easy on yourself. Use all of the great extensions that every RDBMS has built into it no matter which one it is because, if you do need to port your code to another one, you're going to need to make changes even if you don't.

10

Jeff,

Thanks for your opinions and insight. I would tend to agree with what you've said for the most part. As I said before I'm not a big fan of IIF in any language. I prefer to stick to using the more standard coding constructs. Bear in mind I'm 50 years old and am an old school programmer and can actually say I know what a COBOL program looks like and have coded in COBOL (or should I say, the Federal Government's excuse for COBOL programming. Former DoD employee.) so that should explain my mentality somewhat. I am also fairly well schooled in the more modern approaches as well and can actually say that I've seen and used EVERY version of Windows. So I guess I'm sort of a hybrid. Again, your thoughts and opinions are not wasted and are appreciated.

11

I don't get on with IIF either ... Seems like it was a bodge in the first place (in Excel, or wherever it originated) to have a function before a logic statement.

IIF(A=B,"BBB",IIF(A=C,"CCC",IIF(A=D,"DDD","SomethingElse")))

has got to be easier as a CASE statement.

12

I prefer a CASE statement or a good ole fashion IF Else IF statement any day. It's just easier to follow the logical flow of what's going on. I'm still learning the subtleties of coding stored procedures and functions. I'm always game to learn something new so it's cool.

13

Heh... bear in mind that I'll be 65 this coming October, started programming about the same year you were born, and was also a DoD employee for about 15 years (Raytheon, Submarine Signal Division, Portsmouth, RI) after a 9 year stint in the U.S. Submarine Service. I wrote my first program in high school back in 1967 but I'm certainly not the oldest one out there.

I'm with you on the implied subject that new isn't always better. In fact, I strongly embrace the mantra of "Change is inevitable... change for the better is not". :wink: On the subject of the IIF function, apparently MS agrees because, in execution plans, it renders out as a CASE statement. Although I love the CASE statement, IIF is kind of cool because it reminds me of much older programmability in the form of "If, Then, Else" in one tight little statement. To be sure, though, I am loath to nest IIF functions before my 3rd cup o' joe and normally revert back to CASE statements for such things because they are, indeed, easy to read and understand especially when thoughtfully indented.. :wink:

I'm also loath to do things outside of SQL Server even though a lot of people preach "Just because it can be done in SQL, doesn't mean it should". My typical reply is "Just because it can be done in PowerShell, C#, PERL, PYTHON, or a thousand other languages, doesn't mean it should". :wink: When they come back with "Well, SQL Server isn't the center of the universe", my reply is, "True... but let's turn yours off and see if it's the center of your universe". :grin:

Don't get me wrong. I'm not opposed to using the right tool (ex. I've used PowerShell to greatly simplify an enterprise-wide disk-health morning report) to simplify things but I'm not prone to using something new just because it's new. A really good example of that is the relatively new FORMAT function (44 times slower than CONVERT) and that god-forsaken PIVOT statement (twice as slow as a pre-aggregated CROSS TAB).

14

I agree with a lot of what you said. I do however believe that you should pick the right tool for the given job and that just because it can be done in a certain language does not mean that it should be. Case in point, C Language, one of the most used and abused languages ever created. It was NEVER intended to be used as a high level development language. It was designed as a high level language replacement for low level Assembler and the like. But every Tom Dick and Harry think that C is a general purpose end all be all language. Which it certainly is not. I do however believe that SQL based RDBMS is the best tool for the job it was designed for.

As for me I owned my first computer and wrote my first program when I was about 12 years old. I knew what I wanted to do as a career the first time I ever got to see what a computer program looked like and what it could do. I attended vocational school my Jr. and Sr. years of high school for Computer Data Processing and then went on to attend college at DeVry University where I earned a double Bachelor Degree. I was employed at DSDC Columbus OH as a Info Tech Specialist for 8 years and then spent another 4 years as a federal contractor.

I never served in the military because I'm disabled, but I was raised by a father who served this country for most of his adult life both in the Navy and the Air Force. Thank you for your service Sir..

As for me I enjoy working with databases and SQL. I'm pretty decent at designing databases and better than average with SQL once I get used to the given DBMS system. My knowledge of SQL Server is pretty much book based. My little project is my first attempt at really diving into SQL Server.

Thanks for tasking the time to share your knowledge and experience with me.

15

Awesome feedback! And, to be sure, I wasn't trying to one-up you in any way, shape, or form. I just wanted you to know that a little water had gone under this bridge, as well.

DSDC? Do you mean the "Defense Supply (& Distribution) Center" in Columbus? Thank YOU for helping keep folks in harms way well supplied! A lot of folks don't even know that center exists and they certainly don't know it's not just military supplies but also MWR supplies (Morale, Welfare and Recreation), as well!.

Shifting gears back to SQL Server, you're going to like it a lot especially since they now have many former "Enterprise Only" features in the "Standard Edition" now. They've also made the "Developer Edition" (which is really the full blown Enterprise Edition with special limited licensing) available for download for FREE!. The "help" system has, unfortunately, been setup as a separate download/install, as has been SSMS, but it's awesome and can be as simple or as robust as you'd like it to be through code.

The community of folks for SQL Server is also awesome as you'll find out on this site and sites like SQLServerCentral.com (which is where I usually hang out). If you want, send me a "connect request" on LinkedIn and I'll trade email addresses with you so I can send you more info.

Good talking with you! Keep hitting them hard and far. :wink:

1 Like
16

Is it any good these days? Must say, its years since I've used BoL, which wasn't actually "online" of course, and I now use ... errmmm ... "online books" instead :slight_smile:

I just bash whatever command I need a reminder about into Google. Usually the MS DOCs page is the first hit, but sometimes I glean useful information for the other Google results - well ... I skip over Pinal of course.

Beats me why that is such a popular hangout for the Old Hands. SQLServerCentral's forum software is absolutely dreadful and I think its an abomination that the owners think it is acceptable to waste all that good time, given generously and for free by the contributors. They should feel ashamed that they aren't providing Best-of-Breed forum software.

You're probably going to tell me its improved?!! I gave up posting there ages ago because it was such a waste of my time ... not that the MarkDown <spit!> here is any better - when's the last time you saw a First Post here which had ANY formatting of the SQL code? (Apart from the stuff that MarkDown garbled because it thought it WAS a format instruction?)

Normal service will now resume ... :slight_smile:

17

Yes Sir DSCC. I worked for one of the DoD Central Design Activates. I worked with MOCAS which is one of the DoDs largest contract databases (if you want to call it a "database" TIS and Supra. Sorry excuse for a DBMS. I worked both sides of the house and was a GS-12 when I resigned. I wish that I could have served like my father, but civil service was the best I could do. I'm proud of my service. I was there during Desert Shield and Storm. When that I went down you have no idea of the chaos it created in the contracting world. We were placed on 24 hour call and worked mandatory weekends to insure that our brave men and women had everything they needed when they needed it. My father instilled in me to show gratitude and respect for the brave men and women who serve our nation. So I did my best so they could do theirs. A pleasure speaking with you as well and again, Thank You For Your Service.

18

I actually never said anything about "portability", which is indeed a myth.

I still do think IIF complicates the code by using a different style of coding than traditional T-SQL itself. Just use a standard CASE statement. Also, other SQL developers might have to spend time looking up IIF but already know CASE.

19

I know it's a bit different than IIF but one could say the say thing about the windowing functions, LEAD and LAG, the new SPLIT function (which I won't use only because it doesn't work the way I need it to, not because it's new), simultaneous variable declaration and assignment, or techniques such as "Tally Tables", etc, etc. While I appreciate the plight of Developers (although I believe that IIF was actually introduced to help front end developers because they are already familiar with IIF), they/we do actually have to learn new things now and then.

20

I agree... the forum software on SQLServerCentral was ok and then they changed it. It's worse than it was when they finally got pretty much fixed 10 years ago. I'm really disappointed in it and I'm disappointed in all the people involved because it's a huge setback. They even came out and stated that they made the conscious decision to not support IE at all. Looks like they decided to not support much else either.

I spend a whole lot of time there because of the community that has developed there. I've made a whole lot of friends there since 2003.

1 Like