I assume this is not deterministic simply because DB_NAME() is not deterministic? If DB_NAME() is not deterministic, why is it not deterministic? ALTER FUNCTION [TheSchema].[udf_IS_PRODUCTION] () RETURNS bit WITH SCHEMABINDING AS BEGIN RETURN CASE WHEN DB_NAME() = 'PRODUCTION' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) ...
I have a query that looks like this: DECLARE Match_Cursor CURSOR FOR SELECT ID,UserKey,TypeCode FROM [DB1].Table1 as t1 OPEN Match_Cursor FETCH NEXT FROM Match_cursor INTO @ID,@UserKey,@TypeCode; WHILE (@@FETCH_STATUS <> -1) BEGIN INSERT INTO #TempTable SELECT t2.Name, t2.Address, t2.Country, @UserKey, @Typ...
Can you tell me what is the need for a stored procedure when there is UDF? ...
I have a query that makes several calls within a SELECT statement to a user-defined function. The function (vfget) returns the value back from key=value pairs contained within a string. Is it possible for the query to just call the function once and store this in a variable so that it can be reused within the same query? Currently my q...
A few examples to show, just incase: Inline Table Valued CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS...
I'd like to do had a dynamic number of one start/end time pairs passed to a function as an input parameter. The function would then use the list instead of just one start, and one end time in a select statement. CREATE FUNCTION [dbo].[GetData] ( @StartTime datetime, @EndTime datetime ) RETURNS int AS BEGIN SELECT @EndTime =...
For a given SQL stored proc or function, I'm trying to obtain its input and output parameters, where applicable, in a Winforms app I'm creating to browse objects and display their parameters and other attributes. So far I've discovered the SQL system function object_definition, which takes a given sysobjects.id and returns the text of t...
How would one go about converting this into a scalar UDF? I'll pass the product_id to the UDF. Running SQL 2k5 SELECT sum(qty) as qty, product_id FROM vProductQuantity WHERE product_id = @product_id GROUP BY product_id ...
I made the following function in SQL Server 2008 earlier this week that takes two parameters and uses them to select a column of "detail" records and returns them as a single varchar list of comma separated values. Now that I get to thinking about it, I would like to take this table and application-specific function and make it more gen...
I'm looking for the most elegant and secure method to do the following. I have a calendar, and groups of users. Users can add events to specific days on the calendar, and specify how long each event lasts for. I've had a few requests from users to add the ability for them to define that events of a specific length include a break, of...
I've got a task which can be only accomplished by construction a QUERY at runtime and executing it with sp_executesql. The result has to be a boolean (0/1 integer) value which I need to return as a result of the function. The only way I found to capture an SP's output is "INSERT INTO [table] EXECUTE [sp]" query, but functions forbid thi...
Hi, How can I find out all the stored procedures that are calling a particular user defined function in SQL Server 2005. Or how to assign a defult value to a parameter in a user defined function so that when a stored procedure calls that function and does not pass any value to that parameter function assumes the default value. Regards...
Hi I wanted a function to find the greatest of a list of String values passed in. I want to invoke it as Select greatest('Abcd','Efgh','Zxy','EAD') from sql server. It should return Zxy. The number of parameters is variable.Incidentally it is very similar to oracle GREATEST function. So I wrote a very simple CLR function (Vs2008) and t...
Okay so im writing a SQL Server 2008 Stored Procedure (maintenance script). In doing so, being a good boy i've done plenty of error handling, checking rowcounts, printing output messages, etc But in doing this, ive found myself writing over and over again something like this: SELECT @RowsAffected = @@ROWCOUNT IF @RowsAffected > 0 BEGI...
Here's my user-defined table type... CREATE TYPE [dbo].[FooType] AS TABLE( [Bar] [INT], ) This is what ive had to do in my table-valued function to return the type: CREATE FUNCTION [dbo].[GetFoos] RETURN @FooTypes TABLE ([Bar] [INT]) INSERT INTO @FooTypes (1) RETURN Basically, im having to re-declare my type definition in the RETU...
I am new to writing SQL fucntions and trying to write a scalar function in T-SQL. The function should insert leading zeros into an input field of numbers the length of the input can vary in length, i,e,. 123, 1234567, 9876543210 and so forth. The input field is defined as varchar(13) here is the code that I have written for the function...
I writing code to determine how many days in a year. I am trying to keep it really simple. I found code that I think is very clean to determine a leap year. I am passing the inputted date using DATEPART(Y,@Year) to the leap year program and some how am not getting the correct results so I has to be in my SQL code to process the input da...
I am still really new to SQL functions. I am trying to figure out how to use the in a SQL program properly. I am wanting to test scalar UDF's that I have created to see that the return the data correctly and can return a large quantity of data in order. I am not sure what is wrong with my syntax in the SQL to use the function as this is...
HERE IS THE FUNCTION: ALTER FUNCTION dbo.FN_GET_QUARTER -- the parameters for the function here ( @FN_Qtr_date datetime ) RETURNS INT AS BEGIN RETURN datepart(qq,@FN_Qtr_date) END HERE IS THE SQL REPORT: IF(SELECT(OBJECT_ID('TEMPDB..#T1'))) IS NOT NULL DROP TABLE #T1 SELECT L_NUMBER, LAST_MAINTENANCE_DATE, ...
I am still learning SQL so this may seem a very odd question, but is this the best way to use CASE within a CASE to check for NULL? @FN_InputDt datetime) RETURNS varchar(3) as BEGIN DECLARE @Result varchar(3), @MonthNo int Set @MonthNo = datepart(m,@FN_InputDt) Set @Result = CASE WHEN @FN_InputDt IS NOT NUL...