I have a table where I'm storing Lat/Long coordinates, and I want to make a query where I want to get all the records that are within a distance of a certain point. This table has about 10 million records, and there's an index over the Lat/Long fields This does not need to be precise. Among other things, I'm considering that 1 degree ...
The option "RETURNS NULL ON NULL INPUT" for a scalar UDF (see CREATE FUNCTION) stops the function body executing if the parameter is null and simply returns NULL. That is, it short circuits. Does anyone know how it handles multiple parameters? It would be useful to short circuit a function call with multiple parameters, say if the fir...
I have the following function: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[IP4toBIGINT]( @ip4 varchar(15) ) RETURNS bigint WITH SCHEMABINDING AS BEGIN -- oc3 oc2 oc1 oc0 -- 255.255.255.255 -- Declared as BIGINTs to avoid overflows when multiplying later on DECLARE @oct0 bigint, @oct1 b...
I have a table with a column whose values come from an Enumeration. I need to create a TSQL function to convert these values to "Friendly Names" upon retrieval. Examples: 'DateOfBirth' --> 'Date Of Birth' 'PrincipalStreetAddress' --> 'Principal Street Address' I need a straight TSQL UDF solution. I don't have the option of install...
Is it possible for a SQL CLR User-Defined Function to return the data type varbinary(MAX)? In the documentation it mentions: "The input parameters and the type returned from a scalar-valued function can be any of the scalar data types supported by SQL Server, except rowversion, text, ntext, image, timestamp, table, or cursor." - they d...
I have a table that stores user information. The table has a userid (identity) column in it. The table data is queried by a view which is referenced by a lot of sprocs, but we reference something called auid which is created by the UDF below. The UDF is called in the view and sprocs then join or query from the view based on auid. It ...
Hi Folks, I'm trying to generate some sql that is used to calculate some final scores -> think kids at school and their end of year scores. I was going to have about 5 or so Scalar UDF's that, accept some simple values (eg. current score, subjectid, whatever) and then spit out a decimal value. eg. CREATE FUNCTION [dbo].[GetRatingModi...
Hello I created a scalar function in the DB SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_GetUserId_Username] ( @Username varchar(32) ) RETURNS int AS BEGIN DECLARE @UserId int SELECT @UserId = UserId FROM [User] WHERE Username = @Username RETURN @UserId END Now I want to ru...
This question grew out of SQLServer: Why avoid Table-Valued User Defined Functions?. I began asking questions in some of the comments, and the replies to my comments moved off topic. So that you don't have to read the entire discussion: I had never heard it said that user defined functions (UDF) were slow, or to be avoided. Some links...
I'm noticing exponential performance degradation in MySQL query execution times when queries include calls to UDF's in the SELECT or WHERE clauses. The UDF's in question query local tables to return scalar values - so they're not just performing arithmetic expressions, but serving as correlated subqueries. I've fixed the performance pr...
I have a resultset which has ProductURL stored : accessoires/abc/285/P187654 accessoires/abc/285/D18765432 accessoires/abc/285/A1876543 I need to get the final part of the URL i.e. anything that is after the final '/' that appears in the URL. I have a function which gives me the LASTINDEX : SELECT [LAST_INDEX] (ProductURL,'/') But...
My application serializes data into various XML attributes, and depending on data it might send it as text, or as base64. In the latter case, attribute name will be "attribute-base64". So, on SQL server side it is possible to use following convention to decode XML: declare @DataXml xml set @DataXml='<root v="test data"/>' ; --or: set @D...
I'd like to pass a table as a parameter into a scaler UDF. I'd also prefer to restrict the parameter to tables with only one column. (optional) Is this possible? EDIT I don't want to pass a table name, I'd like to pass the table of data (as a reference I presume) EDIT I would want my Scaler UDF to basically take a table of values ...
Is it possible to do an INSERT statement inside a scalar C-language UDF in DB2? I know it sounds weird to have an UDF with side effects like this, but there's a good reason for it. ...
I'm looking to create what I would think is simple. I want a user defined function which takes a row from a table as imput and returns a scalar value. Something like select mt.Id, MyUDF(mt) from M MyTable mt where mt.Price > 0 I understand that I can pass in the Id to the UDF, and then lookup the values from within the ...
I want to write a query kind of like this: CREATE PROCEDURE DupFinder @FirstName varchar(20), @LastName varchar(20) AS SELECT CustId FROM Cust c WHERE [dbo].[fn_MatchConfidence](@FirstName + ' ' + @LastName, [dbo].fn_createCustHash (CustId)) > .8 Running the fn_MatchCondifence User-Defined Func...
From a CLR UDF (C# syntax) is it possible to acquire a more or less direct handle to a built-in TSql function, instead of trying to kludge it into an ADO.NET SQL text like so? // Programming .inside NET CLR UDF: // Is there a more efficient way than this command? SqlCommand cmd... = new SqlCommand( ...'SELECT EncryptByKey(@Param, @Para...
Hi, I am looking for a simple way (UDF?) to establish the similarity between strings. The SOUNDEX and DIFFERENCE function do not seem to do the job. Similarity should be based on number of characters in common (order matters). For example: Spiruroidea sp. AM-2008 and Spiruroidea gen. sp. AM-2008 should be recognised as sim...
Hey all, I have defined a scalar UDF in SQL Server 2005, which does some calculations on the input variables and returns a float as a result. This UDF needs to be called from within a select statement to have that calculation result in the query output for each record. Now, due to the nature of the calculation, an arithmetic overflow ...
Hi all, Which of the following query is better... This is just an example, there are numerous situations, where I want the user name to be displayed instead of UserID Select EmailDate, B.EmployeeName as [UserName], EmailSubject from Trn_Misc_Email as A inner join Mst_Users as B on A.CreatedUserID = B.EmployeeLog...