In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:


USE pubs


CREATE FUNCTION dbo.AuthorsForState(@cState char(2))



RETURN (SELECT * FROM Authors WHERE state = @cState)


And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:


USE pubs


SELECT * FROM shcInLineTableFN


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies


Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):

USE AdventureWorks;


IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;


CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE


-- Columns returned by the function


FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL



-- Returns the first name, last name, job title, and contact type for the specified contact.



@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information


@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =


-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)


FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)


FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)


FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)



SET @ContactType =


-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'


-- Return the information to the caller



INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;





I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Table-Valued Function Result Vs. Calculation Table

Jun 6, 2006


I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

 DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

View 3 Replies View Related

Table-Valued Function

Aug 8, 2007

I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this?



View 3 Replies View Related

Join With Table Valued Function

Mar 10, 2008


I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method ..
SELECT A.Col1,A.Col2,B.Col1,B.Col2
FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B
ON A.Col1 = B.Col1

any body help me ... thanx in advance..

View 3 Replies View Related

Trigger On Table-valued Function?

Jul 20, 2005

Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.

View 2 Replies View Related

How To Join Using A Table-valued Function?

Oct 22, 2007

Hi there. I've hit some gap in my SQL fundementals. I'm playing with table-valued functions but I can't figure out how to join those results with another table. I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this. What am I misunderstanding here?

The Given Objects:
function Split(stringToSplit, delimiter) returns table (column: token)
table Words (column: Words.word) -- table of predefined words
table Sentences (column: Sentences.sentence) -- table of sentences; tokens may not be in Words table, etc

The Problems:
1) how do I query a set of Sentences and their Tokens? (using Split)
2) how do I join tables Sentences and Words using the Split function?

The Attempts:
select word, sentence, token
from Words,
dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences?
where word = token

resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

select word, sentence
from Words, Sentences
where word in (select token from dbo.Split(sentence, ' ')) -- correlated subquery?

resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

View 6 Replies View Related

Table Valued Function And Constraints

May 29, 2008

Is it possible to define a constraint for Primary Key on more than 1 column or an alternate index on a column in a return table from an inline table valed function?

Example Header:

alter FUNCTION [dbo].[fntMetaFrame] (@ii_CompanyID int)

RETURNS @tbl_MetaFrame TABLE ( pk_Key int Identity(1,1) primary key,

ObjectID int ,

Seq int null )

I want the primary key to be pk_Key, ObjectID


I want to add another index on ObjectID.

View 6 Replies View Related

Can I Use If Statement In A Table Valued Function?

Aug 22, 2007

I am using a function in sql server 2005 like this:
...... myfunction(... @FlagOn int)

if(@FlagOn = 1)
select * from.......
select * form....

But it keeps complaining there is some syntax error around if. What is it?


View 5 Replies View Related

Indexing Table-valued Function?

Aug 30, 2007

I am using a multi-statement table-valued function to assemble data from several tables and views for a report. To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row. Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns.

The problem I'm having is that the UPDATEs are taking forever to execute. I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns.

In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function.

Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions? I think that would improve the UPDATE performance dramatically.

Lee Silverman
JackRabbit Sports

View 1 Replies View Related

T-SQL (SS2K8) :: Create A Table Valued Function?

Oct 20, 2014

I would like to create a table valued function using the following data:

create table #WeightedAVG
Segment varchar(20),
orders decimal,
calls int
insert into #WeightedAVG


I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,

CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
col1 varchar(25),
WeightedAVG float

[Code] .....

View 4 Replies View Related

Multi-statement Table-Valued Function

Oct 18, 2007

I'm creating a Multi-statement Table-Valued Function...

Is it possible to insert variables into the table? In other words, is it possible
to have something like

@value1 varchar(10)
@value2 varchar(10)

<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2

instead of

<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE

Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do

col1 col2
Mike 10
Mike 20
Ben 50
John 15
John 25
John 35

The table that my function needs to create should look like this
col1 col2 col3
Mike 10 1
Mike 20 2
Ben 50 1
John 15 1
John 25 2
John 35 3

I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.

Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.

Thank you.

View 7 Replies View Related

Problem Passing A Variable Into A Table-valued Function

Sep 26, 2007


i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver( as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View 10 Replies View Related

Inline Table-valued Function With Multi-value Parameter

Jul 18, 2007

Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View 7 Replies View Related

Performance Of Table-valued Function And Execution Plan

Mar 31, 2008

I am using SQL2005 EE with SP1. The server OS is windows 2K3 sp2

I have a table-valued function (E.g. findAllCustomer(Name varchar(100), gender varchar(1)) to join some tables and find out the result set base the the input parameters.

I have created indexes for the related joinning tables.

I would like to check the performance of a table-valued function and optimize the indexing columns by the execution plan.

I found the graphic explanation only show 1 icon to represent the function performance. I cannot find any further detail of the function. (E.g. using which index in joinning)

If I change the function to stored procedure, I can know whether the T-SQL is using index seek or table scan. I also found the stored procedure version subtree cost is much grether that the table-valued function

I would like to know any configureation in management studio can give more inform for the function performance?


View 3 Replies View Related

Must Declare The Scalar Variable In Table-valued Function

May 18, 2007

Hi, I'm having trouble with this multi-statement table-valued function:

-- Add the parameters for the function here
@dateStart DATETIME,
Insurer VARCHAR(50),
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid91120 MONEY DEFAULT 0,
Insurer VARCHAR(50),
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid91120 MONEY DEFAULT 0,

SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
WHERE Pims.Insurer = @arTemp.Insurer AND
Pims.NABP = @arTemp.NABP AND
Pims.NumRx = @arTemp.NumRx


I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View 10 Replies View Related

SQL Server 2008 :: Create A Table Valued Function That Fetch Through The Table?

Apr 24, 2015

I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique

EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

View 9 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

( )
RETURNS @retTable
@Field1 int,
@Field2 varchar(50)
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal


View 2 Replies View Related

Table-valued Function Does Not Accept Chinese Characters As Parameter

Nov 15, 2007

I have a table-valued function in mssql 2005 as below:
ALTER FUNCTION fn_test{   @test nvarchar(1000)}RETURNS@TEMP TABLE{   test nvarchar(1000)}ASBEGIN   INSERT INTO @TEMP   SELECT @test
Everytime, I passed in chinese character (@test), such as 測驗, the function will return ????.  What should I do for the table-valued function, so that the chinese character can be passed in?  Please help.
Note: I can search and get the chinese characters if I use stored procedures; and the columns in the tables can store chinese chararcters as well.   Only table-valued function is not working with the chinese characters.  Is it a bug from MSSQL 2005?

View 4 Replies View Related

SQL Server 2012 :: Passing Parameters To Table Valued Function?

Mar 13, 2014

I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.


ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
RETURNS @Banner_Orion_Employee_Comparison TABLE



I execute this:

select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')

and get:


View 7 Replies View Related

SQL Server 2008 :: Recursive CTE On Inline Table Valued Function

Jun 22, 2015

I have a recursive CTE on an inline table valued function. I need to set the MAXRECURSION option on the CTE, but SQL Server is complaining with "Incorrect syntax near the keyword 'OPTION'".

It works fine on non-inline function. I couldn't find any documentation indicating this wasn't possible.

I can use the MAXRECURSION option in call to the function

SELECT * FROM MyFunction ()

but that means that the user needs to know the "MyFunction" uses recursive CTE, which defeats the purpose of the abstraction.

View 5 Replies View Related

SQL Server 2012 :: Removing Cursor In Table Valued Function

Oct 16, 2015

I need removing cursor in my table valued function with alternate code.

ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
@containerSqlId SMALLINT,
@containerIncId INT

[Code] ....

View 2 Replies View Related

Transact SQL :: Passing Column To Table Valued Function And Getting Could Not Be Bound

Nov 10, 2015

I'm running 2014 enterprise and getting an error on this form of a says the multi part identifier "mns.col3" could not be bound.  I'm aware that a cross apply would be more appropriate but i'm just prototyping and probably going to move to a set based approach anyway.The udf returns a table.

select mns.col1,
from table1 mns
left join dbo.udf_udf1(@firstofmonth,@lastofmonth, mns.col3) x
on 1=1

View 3 Replies View Related

System.DirectoryServices Performance Issue In Table-valued Function

Jan 16, 2007

I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).

Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?

Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class

View 9 Replies View Related

Can A Stored Procedure Called From An Inline Table-Valued Function

Oct 5, 2006


I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View 4 Replies View Related

T-SQL (SS2K8) :: Table Valued Function For Active Directory Group Membership

Jun 8, 2012

For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?

The following works fine:

FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a

The following gives me the error:

Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
@group nvarchar(255)

[Code] .....

View 7 Replies View Related

SQL Server 2012 :: Return Random Records In A Table-valued Function?

Dec 19, 2013

My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.

I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).

CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT

[Code] ....

But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:

Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.

I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...

The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)

but that's clearly not working. Is there a way to do this without resorting to a cursor?

View 9 Replies View Related

SQL Server 2012 :: Remote Table-valued Function Calls Are Not Allowed

Sep 19, 2014

FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)
WHERE <Condition>

The above SQL Script ran successfully up to yesterday. But today its throws the below error message.

Remote table-valued function calls are not allowed.

Now i have modified the SQL script as follows

FROM [LinkedServer].[Database].dbo.[TableName] WITH (NOLOCK)
WHERE <Condition>

I want to know how the 1st SQL script runs successfully up to yesterday.

View 7 Replies View Related

T-SQL (SS2K8) :: Inline Table-valued Function - A Severe Error Occurred

Jan 21, 2015

I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)

The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:

CREATE TYPE test_list AS TABLE(a int);
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
DECLARE @t test_list;
SELECT * FROM test_function(1, @t);

When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)

The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:

- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)

- The SELECT statement has a CTE

- The TVP is accessed within the CTE

- The outer FROM clause references the CTE

- There is also a scalar parameter on the function (scalar type does not seem to matter).

- The scalar parameter precedes the TVP in the parameter list.

So I have an easy work-around: put the TVP first in the parameter list.

View 5 Replies View Related

SQL Server 2008 :: Table Valued Function Where Parameter Has Multiple Values

Jan 29, 2015

Is it possible to pass multiple values to a TVF, such as using an IN clause?

View 6 Replies View Related

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors

Apr 6, 2007

We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.

Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,


public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );



return (IEnumerable)myList;



When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.

Code SnippetEXEC GetObject 'Item'

Is there some sorf of trick I am missing?

Thank you!

View 3 Replies View Related

Transact SQL :: Table Valued Function Returns Nothing When Single Column Selected

Apr 21, 2015

We run std 2008 r2.  I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested. 

But when he selects * he gets a single row.

I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it.  

View 8 Replies View Related

Copyrights 2005-15, All rights reserved