Problem: a table of coordinate lat/lngs. Two rows can potentially have the same coordinate. We want a query that returns a set of rows with unique coordinates (within the returned set). Note that distinct is not usable because I need to return the id column which is, by definition, distinct. This sort of works (@maxcount is the number of...
In Excel, there's a function called "MAX" that accepts numbers and returns the largest one in the set. Is there a function in T-SQL that duplicates this functionality? I haven't been able to find one, and I've written a UDF that does it for me, but I thought it was worth asking. Here is the function I've been using: CREATE FUNCTION dbo...
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...
Hi folks, I have some sql statements that calculates some numbers. It's possible (with bonus points/penalty points) that a person could get a final score OVER 100 (with bonus points) or UNDER 0 (with penalties). How can i make sure that the value calculated, if it exceeds 100 it get's maxed at 100. Alternatively, if the score ends up ...
Hello all, I'm having trouble getting my user defined function operating properly. I am running on SQL Server 2000. I am trying to return a table of all users that have an even balance in a "BillingTransactions" table. Our transactions are specified by the RecordType field; 0 for purchase, 1 for payment. So what I'm trying to do is get...
I have this user defined function. public partial class UserDefinedFunctions { static int i; [SqlFunction(IsDeterministic = true)] public static SqlSingle f() { return new SqlSingle(1.3F); } }; But it only works if i is readonly. Why? ...
I'm reasonably new to NHibernate and everything has been going pretty well so far but I've come across a problem I'm not exactly sure of how to go about solving. Basically I need to filter by the output of a User Defined function. If I was writing in SQL this is what I'd write: declare @Latitude decimal declare @Longitude decimal declar...
I am using SQL Server 2000 and feeling stuck about how to do this: I have a stored procedure that returns a single resultset which is bound to a gridview in a .Net web app. Now I'd like to have a "table" so I can use it in an existing VB6 application like this: SELECT * FROM myTable ...where the schema of "myTable" is reflects the...
Hello, I have a stored procedure in which i want to create a user defined function - Split (splits a string separated with delimiters and returns the strings in a table), make use of the function and finally drop the function. My question is that whether i can create a user defined function inside a stored procedure and drop it finally...
def horizline(col): for col in range (col): print("*", end='') print() def vertline(rows, col): for rows in range (rows-2): print ("*", end='') for col in range (col-2): print(' ', end='') print("*") def functionA(width): horizline(width) vertline(width) horizline(widt...
Consider this simple user defined function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate DATETIME ) RETURNS DATETIME BEGIN RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME) END whi...
I have a user defined function. In that function I declared a variable of type datetime. I am assigning the result of a query into this variable. And I am returning this assigned value. It looks like delimiter$$ drop function if exists getQEDate$$ create function getQEDate() returns datetime begin declare qedate datetime; select ...
My function ALTER FUNCTION GetProductCount (@CatID int) RETURNS int AS BEGIN DECLARE @return_value int EXEC @return_value = [dbo].[USP_Products_GetList] @ProductName = NULL, @ProductID = NULL, @Description = NULL, @CatID = @CatID, @CatName = NULL, @Price1 = NULL, @Price2 = NU...