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 ...
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...
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?
...
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...
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...
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...
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...
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...
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 ...
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...
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...
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...
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...