table-valued-functions

Pass table as parameter to SQLCLR TV-UDF

We have a third-party DLL that can operate on a DataTable of source information and generate some useful values, and we're trying to hook it up through SQLCLR to be callable as a table-valued UDF in SQL Server 2008. Taking the concept here one step further, I would like to program a CLR Table-Valued Function that operates on a table of ...

Linq-to-SQL with a table valued UDF user defined function

I am new to Linq and trying to get a handle on how to bind a drop down to a SQL user defined function. //Populate the Pledge dropdown var db = new App_Data.MyDBDataContext(); int? partnerID = Convert.ToInt32(Request.QueryString["PartnerID"]); var pledges = from p in db.ufn_AvailablePledge...

How to prevent null values for table-valued function parameters?

I have a TSQL Table-Valued Function and it is complex. I want to ensure that one of the parameters cannot be null. Yet, when I specify NOT NULL after my parameter declaration I am presented with SQL errors. Is it possible to prevent a parameter of a Table-Valued Function to be assigned null by the calling SQL? ...

How to create an ADODB Recordset From a Table Valued Function with Named Parameters

This works: Dim rst As New ADODB.Recordset rst.Open "SELECT * FROM dbo.ftblTest(1,2,3)", CP.Connection, adOpenKeyset, adLockReadOnly But it would be nicer to do this: rst.Open "SELECT * FROM dbo.ftblTest(@Param1=1,@Param2=2,@Param3=3)", CP.Connection, adOpenKeyset, adLockReadOnly If I try the second method I get the error: "paramet...

The full-text query parameter for Fulltext Query String is not valid

I am using Full Text Search with LINQ in my application and as this is not supported by LINQ I use a table-valued function workaround. The function is created on SQL Server 2008. Surprisingly, I get error “The full-text query parameter for Fulltext Query String is not valid” when I search for a simply text e.g. “manager” I used SQL Ser...

Executing a Table-Valued Function from a Stored Procedure with multiple Table-Valued Parameters being passed through?

I've got a stored procedure that executes some repetitive code, so I decided to make the redundant code into a table-valued function. The problem that I'm encountering is: Msg 137, Level 16, State 1, Procedure Search, Line 98 Must declare the scalar variable "@myTVP". A simple example of the SQL code that I'm using is: CREATE TYPE [d...

Can I return foreign key columns from table valued functions?

I want to use a table valued function and call it from within multiple stored procedures rather than repeat the same query across all the stored procedures, but for compatibility with a legacy VB6/UltraGrid app I need to preserve the foreign key references from the original tables. Within the table valued function I can specify a PRIMAR...

TVF UDF does not return the same data as SELECT

Calling the UDF like so: SELECT product_name, SUM(quantity) AS SumQty, SUM(face_value) AS SumFaceValue, SUM(net_cost)AS SumNetCost, SUM(face_value - net_cost) AS SumScripRebate, organization_name FROM getSalesSummary(@GLSCOrgId, @BeginDate, @EndDate) getSalesSummary GROUP BY product_name, organi...

Why can't use INSERT EXEC statement within a stored procedure called by another stored procedure?

Hi, First I try to explain the circumstances. I store the the filter expression in one column separated by line breaks. The base idea was this: SELECT 'SELECT ''' + REPLACE(topic_filter,CHAR(10),''' UNION ALL SELECT ''') + '''' FROM dbo.topic_filter T WHERE T.id = @id FOR XML PATH('') After this I simply execute this string to ...

Wrong order in Table valued Function(keep "order" of a recursive CTE)

Hello, a few minutes ago i asked here how to get parent records with a recursive CTE. This works now, but I get the wrong order(backwards, ordered by the PK idData) when i create a Table valued Function which returns all parents. I cannot order directly because i need the logical order provided by the CTE. This gives the correct order(f...

Extremely slow Table-Valued-Function with recursive CTE inside.

Hello, I've cretaed a TVF which returns a table with parent-records from a recursive CTE here. That works excellent and the result is available directly. Now i wanted to get the child-records(they have the same FK as the current record's PK). The problem is that it takes 1:10 minutes to get 22 child-records for a given id. Why is this s...

Multi-part identifier could not be bound

I know that there are several questions around this exception on SO, but nothing seen that helps me. I have following query giving me a "Multi-part identifier 'claim.fiData' could not be bound"-Exception: SELECT claim.idData FROM tabData as claim INNER JOIN dbo._previousClaimsByFiData(claim.fiData) AS prevClaim ON prevClaim.idData...

Call TVF on every record of a table and concat results

I thought that must be obvious but I can't figure it out. Say there is a table tblData with a column ID and a table-valued-function (_tvf) that takes an ID as parameter. I need the results for all ID's in tblData. But: SELECT * FROM tblData data INNER JOIN dbo._tvf(data.ID) AS tvfData ON data.ID = tvfData.ID gives me an error: T...