How to pass parameters to Table Valued Function
I want to do something like Select * from tvfHello(@param) where @param in (Select ID from Users) ...
table-valued-functions
How can I work around SQL Server - Inline Table Value Function execution plan variation based on parameters?
Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in ...
SqlFunction fails to open context connection despite DataAccessKind.Read present
I've got a SqlServer project with a very simple test for a Table-Valued-Function:- [SqlFunction(TableDefinition = "forename nvarchar(50)", FillRowMethodName = "TestFillRow", DataAccess = DataAccessKind.Read)] public static IEnumerable TestConn(int ID) { using (SqlConnection con = new SqlConnection("context connection=true")) { //con...
Why does calling full text search in SQL Server 2008 fail when inside transaction scope?
The following code: var foo = Users.Join( tvf_SearchUsers(queryString), u => u.User_Id, s => s.User_Id, (u, s) => u); Selects users that match the query string based on a table valued function (tvf_SearchUsers), which utilises full text search. This code snippet is part o...
Need help with Sql Server Full Text Search problem.
Hi folks, I have a Full Text Catalog on single table, with three fields defined :- TABLE: Animals Fields: Name, Breed, LatinName. Now, the Catalog seems to be working perfectly. eg. CREATE FUNCTION AnimalSearch ( @Name NVARCHAR(200) ) RETURNS TABLE AS RETURN ( SELECT KEY_TBL.[Key] as Name, KEY_TBL.RANK as Relevan...
How do I use entity framework with hierarchical data?
I'm working with a large hierarchical data set in sql server - modelled using the standard "EntityID, ParentID" kind of approach. There are about 25,000 nodes in the whole tree. I often need to access subtrees of the tree, and then access related data that hangs off the nodes of the subtree. I built a data access layer a few years ago b...
Returning Explicit Open XML results sets from a Table Value Function
I'm trying to return rows in Open XML form from a table value function in SQL Server (2008). I'm occasionally getting errors when I select the results with "for xml explicit" which suggest that the order of the results is not guaranteed to be the same as the order I insert into the output of the TVF. So my first question is: is that th...
SQL massive performance difference using SELECT TOP x even when x is much higher than selected rows
Hi All, I'm selecting some rows from a table valued function but have found an inexplicable massive performance difference by putting SELECT TOP in the query. SELECT col1, col2, col3 etc FROM dbo.some_table_function WHERE col1 = @parameter is taking upwards of 5 or 6 mins to complete. However SELECT TOP 6000 col1, col2, col3...
Table Valued Function where did my query plan go?
I've just wrapped a complex SQL Statement in a Table-valued function on SQLServer 2000. When looking at the Query Plan for a SELECT * FROM dbo.NewFunc it just gives me a Table Scan of the table I have created. I'm guessing that this is because table is created in tempdb and I am just selecting from it. So the query is simply : SEL...
How to get around UDF restriction on side-effecting operators?
Microsoft Books Online (BOL) on Using Change Data explains a misleading error messages for cdc.fn_cdc_get_all_changes_* & cdc.fn_cdc_get_net_changes_* when an invalid, out-of-range LSN (Log Sequence Number) has been passed to them. Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure ...
What's too much SQL Server data and how do I analyze my execution plan
I have a relation between two tables with 600K rows and my first question is, is that a lot of data? It doesn't seem like a lot (in terms of rows, not bytes) I can write a query like this SELECT EntityID, COUNT(*) FROM QueryMembership GROUP BY EntityID And it completes in now time at all, but when I do this. SELECT EntityID, COUNT(*...
Need help with SQL join to a function
Hello! I am trying to enter the following to an UPDATE trigger of QuoteItem: UPDATE QuoteItem SET List = StyleItem.List, CostFactor = StyleItem.CostFactor, Cost = NULL FROM dbo.fn_GetQuoteItemListPrice(QuoteItem.ItemId, QuoteItem.RoomId) AS StyleItem CROSS JOIN (QuoteItem JOIN INSERTED ON QuoteItem.QuoteItemId = INSERTED.Quot...
Table-Valued Functions in ORACLE 11g ? ( parameterized views )
Hi all, I've seen discussions about this in the past, such as here. But I'm wondering if somewhere along the line, maybe 10g or 11g (we are using 11g), ORACLE has introduced any better support for "parameterized views", without needing to litter the database with all sorts of user-defined types and/or cursor definitions or sys_context va...
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous.
Hai guys, I ve used the following split function, CREATE FUNCTION dbo.Splitfn(@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null retur...
TSQL - How to join 1..* from multiple tables in one resultset?
A location table record has two address id's - mailing and business addressID that refer to an address table. Thus, the address table will contain up to two records for a given addressID. Given a location ID, I need an sproc to return all tbl_Location fields, and all tbl_Address fields in one resultset: LocationID INT, ...
Stored procedure with output parameters vs. table-valued function?
Which approach is better to use if I need a member (sp or func) returning 2 parameters: CREATE PROCEDURE Test @in INT, @outID INT OUT, @amount DECIMAL OUT AS BEGIN ... END or CREATE FUNCTION Test ( @in INT ) RETURNS @ret TABLE (outID INT, amount DECIMAL) AS BEGIN ... END What are pros and cons of each approach con...
How to access a method on a generic datacontext which is only created at runtime
I'm creating my generic DataContext using only the connectionString in the ctor. I have no issues in retrieving the table using DataContext.GetTable(). However, I need to also be able to retrieve entities of inline table functions. The dbml designer generates public IQueryable<testFunctionResult> testFunction() { return thi...
Table valued function only returns CLR error
I have read-only access to a database that was set up for a third-party, closed-source app. Once group of (hopefully) useful table functions only returns the error: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features. (severi...
Using a table-value function inside a view in SQL Server
I have a table-value function that works correctly if I try the following query: SELECT * FROM dbo.GetScheduleForEmployee() AS schedule However if I try to create a view with that query I get a "too few parameters" error. Is there a limitation with table-value functions and views? ...
How can I write this Table Valued Function as a Stored Procedure?
I have the following TVF for fulltext search: FUNCTION [dbo].[Fishes_FullTextSearch] (@searchtext nvarchar(4000), @limitcount int) RETURNS TABLE AS RETURN SELECT * FROM Fishes INNER JOIN CONTAINSTABLE(Fishes, *, @searchtext, @limitcount) AS KEY_TBL ON Fishes.Id = KEY_TBL.[KEY] When I'm using this TVF, it doesn't return a collection o...