Hi Guys, I have a column in SQL Server 2005 that stores a version number as a string that i would like to sort by. I have been unable to find out how to sort this column, although i am guessing it would be some kind of custom function or compare algorithm. Can anyone point me in the right direction of where to start? I may be googling ...
I've searched the web and I've searched the questions here on stackoverflow, but I haven't been able to find a solution. Here is what I'd like to do: Suppose I have the following code in a class module named "MyClass" Option Explicit Dim var1 as integer Sub Initialize(v as integer) var1 = v End Sub Function GetVar1() GetVar1 ...
This is for Excel and VBA. Assume that BondClass has been properly defined in a Class Module. I get a #VALUE! when I type "=GetBondPrincipal()" into an Excel cell. Have I done something syntactically wrong or is this just not possible in Excel/VBA? I ask because what I really want to do is this: http://stackoverflow.com/questions/1354046...
I have a stored procedure which filters based on the result of the DATEADD function - My understanding is that this is similar to using user defined functions in that because SQL server cannot store statistics based on the output of that function it has trouble evaluating the cost of an execution plan. The query looks a little like this...
I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns: Msg 443, Level 16, State 14, Procedure ..., Line ... Invalid use of a side-effecting operator 'RAISERROR' within a function. But the fact is, the function takes...
I'm working with a medical-record system that stores data in a construct that resembles a spreadsheet--date/time in column headers, measurements (e.g. physician name, Rh, blood type) in first column of each row, and a value in the intersecting cell. Reports that are based on this construct often require 10 or more of these measures to b...
DELIMITER $$; DROP FUNCTION IF EXISTS tonumeric $$; CREATE FUNCTION tonumeric() returns numeric BEGIN declare num numeric; set num = to_number('12'); return num; END$$ DELIMITER; $$ When I executed this function, I am facing this error. Error Code : 1064 You have an error in your SQL syntax; check the manual that correspon...
I'm having trouble with the following code. What it should do is echo cats.php followed by example.php but it's not echoing the example.php. Any ideas why this might be happening? $bookLocations = array( 'example.php', 'cats.php', 'dogs.php', 'fires.php', 'monkeys.php', 'birds.php', ); echo $bookLocations[1]; f...
I've never worked with Database Functions, but my current project requires it. I need to put a common sql query into a function so we don't have to type it out in our code hundreds of times. I've got the function created, but I don't know how to use it. Here's the function code: USE [DB_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENT...
So I've had a simple ucwords function for Perl which I've had a while, and wanted to expand it, this is what I've come up with, is this the way I should be building my functions to handle optional parameters? Original: sub ucwords{ $str = @_[0]; $str = lc($str); $str =~ s/\b(\w)/\u$1/g; return $str; } Extended: sub u...
How would one write a function in SQL Server to output a number in words? input: 1 output: one input: 129 output: one hundred twenty-nine ...
Consider the following sequence of events: A view v_Foo is defined A user-defined function GetFoo() is defined that includes all columns from v_Foo (using 'Select * ...') The definition of v_Foo changes and now includes more columns I now want GetFoo() to include the new columns in v_Foo, but it still references the old definition I ...
I'm planning a data warehouse migration to SQL Server 2008, and trying to think of ways to replicate the LAG, LEAD, FIRST_VALUE and LAST_VALUE analytic functions from Oracle in SQL Server 2008. They are not included in SQL Server 2008, although the basic machinery for windowed analytic functions is (e.g. ROW_NUMBER, RANK and DENSE_RANK a...
Hello, I created a UDF that I am using to generate a default value for a column. It works great, but I want to pass another field as a parameter into the function. Is this possible? For example, one of the fields is a DealerID field, and I want to pass in the value of the DealerID field into my UDF because I will use it to calculate t...
So here's the deal. In our database, we wrap most of our reads (i.e. select statements) in table valued functions for purposes of security and modularity. So I've got a TVF which defines one or more optional parameters. I believe having a TVF with defaulted parameters mandates the use of the keyword "default" when calling the TVF like s...
How to pass an array as a parameter for a user defined function in MS Excel VBA? Eventually I want to test that if a given date (dateDay) is in several ranges of dates (arrayVacation): Function CB_IsInRangeArr(dateDay As Date, ParamArray arrayVacation() As Variant) As Boolean ' Test that the array is in the form of 2 columns and n...
Hi, I notice that in some PHP built in functions such as str_replace there are optional input variables. Can I have have optional input variables in my own functions? If so, how? Thanks, Brian ...
Just starting out with LINQ to SQL (you can probably tell - be gentle). I'd like to use the value returned by a user-defined function as an entity class property value and have the the value populated when all the other basic (column) fields are loaded. I know there are ways to use the UDF from a L2S query (here, and here), but I'd pre...
Is it possible that a multi-statement UDF return a User Defined Table Type, instead of a table that is defined within it's return param? So instead of: CREATE FUNCTION MyFunc ( @p1 int, @p2 char ) RETURNS @SomeVar TABLE ( c1 int ) AS I would like to do: CREATE FUNCTION MyFunc ( @p1 int, @p2 char ) RETURNS @SomeVar M...
I have a rather large linq-to-nhibernate query. I now need to add a filter based on a user-defined function written in t-sql that i have to pass parameters into. In my case, I need to pass in a zipcode that the user types in and pass it to the t-sql function to filter by distance from this zip. Is this possible, or do i need to rewri...