CLR Table Valued Functions Error
Sep 13, 2006
Hi,
I'm trying to create a CLR functions
this is the Sql Function attribute and the FillRowMethod signature
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "FillRows",IsPrecise=true,
TableDefinition = "SOCIETA nvarchar(55),CLIENTE nvarchar(150),NUMEROCONTRATTO nvarchar(255),FIRMA datetime,CHIUSURA datetime,AUTORIZZATO float"
)]
public static IEnumerable dbf_Create_RiepilogoAccordi(SqlInt32 commessa, SqlInt32 tipo_commessa, SqlInt32 progetto, SqlInt32 DAC, SqlInt32 figura, SqlDateTime dataFatturazioneDa, SqlDateTime dataFatturazioneA)
public static void FillRows(Object obj, out SqlString SOCIETA, out SqlString CLIENTE, out SqlString NUMEROCONTRATTO, out SqlDateTime FIRMA, out SqlDateTime CHIUSURA, SqlDouble AUTORIZZATO)
Whe I try to deploy my function, I get the following error:
Error 1 Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function 'dbf_Create_RiepilogoAccordi' due to column 6. CM.Reports.SIA.RiepilogoAccordi
I get this error whichever combination of name/value I use for column 6
Can someone help me?
Thanks
Marco
View 1 Replies
ADVERTISEMENT
Aug 4, 2006
So I was creating a new table-valued function today which queries some data from a preexisting table. Since this is my first table-valued function, I decided to check out some of the examples and see what I can figure out.
One particular example helped me out a bit until I ran into some data access issues...
http://msdn2.microsoft.com/en-us/library/ms165054.aspx
So I create my function:
[SqlFunction(DataAccess = DataAccessKind.Read,SystemDataAccess=SystemDataAccessKind.Read,FillRowMethodName = "FillMyRow",TableDefinition ="p1 int, p2 int"]
public static IEnumerable getMyTable()
{
using (SqlConnection conn = ....)
{
using (SqlCommand command = conn.CreateCommand())
{
///.... populate command text, open connection
using (SqlDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
customObject1 o = new customObject1();
///... populate o's parameters from reader ...
yield return o;
}
}
}
}
public static void FillMyRow(
object source,
out int p1,
out int p2)
{
customObject1 f = (customObject1)source;
p1 = f.p1;
p2 = f.p2;
}
Notice, this example yield returns the value o upon each iteration of the reader.
Despite the fact that the DataAccess is set to Read I still get the error...
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
I did however get past this error, by creating a collection of customObject1, populated it within the while(rdr.Read()) loop, then return the collection after closing the connection, command and reader.
I assume this error has something to do with the fact that you can't yield return results from within an open reader. Is this error right though in this case? Whats causing it to throw a InvOp Exception? Or is this a bug?
Thanks for the attention.
View 4 Replies
View Related
May 12, 2004
I'm studying for the MCDBA test & understand table valued functions but am struggling to find a good use for them... can anyone explain to me why you'd want to use one over a view?
View 2 Replies
View Related
Aug 11, 2004
Hi I am writting Stored Procedures that have to be built on the base of other tables specially created for this purpose, is it better to create these intermediate tables as views or as functions returning tables? I guess views would be lighter on performance as they would not be created on the fly?
View 2 Replies
View Related
Apr 17, 2008
Hi!
Here's my function. The trouble - I can not make ORDER BY the "visits_count", "properties_count", "enquiries_count" fields.
May be some one could help me with this?
CREATE FUNCTION [dbo].[GetPagedStatistics]
(
@start_index int,
@count int,
@condition nvarchar(255),
@order_field nvarchar(255),
@date_from datetime,
@date_to datetime )
RETURNS @total_stat TABLE (
username nvarchar(255),
first_name nvarchar(255),
last_name nvarchar(255),
properties_count int,
enquiries_count int,
visits_count int,
id_user int)
BEGIN
INSERT @total_stat
SELECT
top (@count)
dbo.users.username,
dbo.users.first_name,
dbo.users.last_name,
ISNULL(COUNT(DISTINCT dbo.advertisement.id_advertisement), 0) AS properties_count,
ISNULL(COUNT(DISTINCT dbo.enquiry_emails.id_enquiry_email), 0) AS enquiries_count,
ISNULL(COUNT(DISTINCT dbo.property_statistics.id_statistics), 0) AS visits_count,
dbo.users.id_user
FROM
dbo.property_statistics RIGHT OUTER JOIN
dbo.advertisement RIGHT OUTER JOIN
dbo.users ON dbo.advertisement.id_user = dbo.users.id_user LEFT JOIN
dbo.enquiry_emails ON dbo.enquiry_emails.id_advertisement = dbo.advertisement.id_advertisement ON
dbo.property_statistics.id_advertisement = dbo.advertisement.id_advertisement
WHERE
1=@condition and
(dbo.advertisement.creation_date <= @date_to and dbo.advertisement.creation_date >= @date_from ) and
(
(dbo.enquiry_emails.creation_date <= @date_to
and dbo.enquiry_emails.creation_date >= @date_from
and dbo.property_statistics.view_date <= @date_to
and dbo.property_statistics.view_date >= @date_from ) or
(dbo.property_statistics.view_date is null) or
(dbo.enquiry_emails.creation_date is null)
) and
(ISNULL(dbo.advertisement.id_parent, 0) = 0)
GROUP BY
dbo.users.username,
dbo.users.first_name,
dbo.users.last_name,
dbo.users.id_user
order by
case when @order_field='username' then dbo.users.username end,
case when @order_field='first_name' then dbo.users.first_name end,
case when @order_field='last_name' then dbo.users.last_name end,
case when @order_field='properties_count' then 1 end,
case when @order_field='enquiries_count' then 1 end,
case when @order_field='visits_count' then 1 end
RETURN
END
View 1 Replies
View Related
Apr 24, 2008
Are there any disadvantages in respect to performance in using table valued functions instead of using a view.
Thanks...
View 3 Replies
View Related
May 1, 2007
Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.
View 7 Replies
View Related
Apr 11, 2006
Hi everyone.I'd like to know how stored procedures and table-valued functions compare when it comes to returning a resultant set of data. I know there is a link somewhere but I can't immediately find it.Thanks.
View 2 Replies
View Related
May 25, 2004
Hello
I am trying to do the following:
1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter.
2. Then, use it like this: "Select * from table T, mstvF1( T.id )"
It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id.
If I do
Select * from table T, mstvF1( 5 ), then it works.
Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T?
Thanks for any help.
View 3 Replies
View Related
Apr 1, 2008
Hello Gurus,
I have a stored procedure that gathers data from three tables and joins them, two of the tables need to have different rowcounts set, ie. pull only a certain number of rows from one table and only a certain number of rows from another table... The number of rows it should pull are stored within a table for each. Let me explain.... these tables hold Exchange storage group and mailstore data for a number of servers. Each server has a table entry with the number of child storage groups and each storage group has a table entry with the number of child mailstores. The tables get updated every two minutes via a program. I need to be able to get the most Data with the correct child counts for each server and storage group.
I believe that i've found a way to do this with a stored procedure that calls a table-valued function. The table-valued function simply filters down the storage group table to it's number of storage groups, ordered by timestamp. I may be way off here, but i can't tell because both the stored procedure and function check out fine but when i execute the stored procedure it gives me the following error:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetExchSGInfo", or the name is ambiguous.
My code is below:
Stored Procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetExchangeData2]
@top INT,
@SID INT,
@SGCount INT,
@ServerName VARCHAR(50)
AS
Set @SID = (SELECT ServerID FROM dbo.Servers WHERE ServerName = @ServerName)
Set @top = (SELECT sum(Children) FROM dbo.ExchangeSG WHERE ServerID = @SID)
Set @SGCount = (SELECT SGCount FROM dbo.Servers WHERE ServerID = @SID)
SET ROWCOUNT @top
SELECT dbo.ExchangeMSData.*, dboExchangeMailStore.*, dbo.GetExchSGInfo(@SID,@SGCount) As ExchangeSG, dbo.Servers.*
FROM dbo.Servers INNER JOIN
ExchangeSG ON dbo.Servers.ServerID = ExchangeSG.ServerID INNER JOIN
dbo.ExchangeMailStore ON ExchangeSG.StorageGroupID = dbo.ExchangeMailStore.StorageGroupID INNER JOIN
dbo.ExchangeMSData ON dbo.ExchangeMailStore.MailstoreID = dbo.ExchangeMSData.MailstoreID
WHERE (dbo.Servers.ServerName = @ServerName)
ORDER BY dbo.ExchangeMSData.[TimeStamp] DESC, dbo.ExchangeSG.[TimeStamp] DESC
SET ROWCOUNT 0
And the Function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetExchSGInfo]
(
@SID INT,
@SGCount INT
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP (@SGCount) *
FROM dbo.ExchangeSG
WHERE ServerID = @SID
ORDER BY [TimeStamp]
)
Can anyone help me?
Thanks.
View 7 Replies
View Related
Sep 6, 2006
I am a bit confused by the difference between a stored procedure and a table-valued function. Can somebody please either give me a simple explanation, or point me at something I can read.
I thought I had it worked out, and had coded some action queries as stored procedures, and I wrote a table-valued function that was effectively an encapsulated SELECT so that SELECT * FROM Spouse(@ID) worked fine. Then I wanted to use a function SpousePair, that was similar to Spouse, to power a Gridview. I discovered that I couldn't. It seems that a SQLDataSource requires either a SELECT statement or a stored procedure. So I wrote a stored procedure SpousePair(@ID1, @ID2).
I find that whereas I tested Spouse with
SELECT * FROM SPOUSE(@ID)
I tested SpousePair with
EXEC SpousePair @ID1 @id2
Now I want to combine these: if I could I would write
SELECT * FROM SPOUSE(@ID) WHERE SPOUSEID NOT IN
(SELECT SPOUSEID FROM SpousePair(@ID1, @ID2))
However this is invalid because you can't put a stored procedure in a Select statement, and SELECT .... NOT IN (EXEC SpousePair @ID1 @ID2) is also invalid.
Is there any alternative to creating a table-valued function, SpousePairA, that is identical to SpousePair but coded as a function. I'm reluctant to do this because then I'll have two bits of quite complicated SQL logic to maintain.
View 4 Replies
View Related
May 25, 2008
Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?
For example, a simple stored procedure is this:
CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN
Execute('Select top 10 * from ' + @tableName + ';')
END
I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
View 11 Replies
View Related
Aug 16, 2006
Hi,
I am trying to write a function which takes a string as input and returns the computed value.
I need to use the output of this function as a coulmn in another select query.
Here is the code (Example: @Equation = '(100*4)+12/272')
create function dbo.calc(@Equation nvarchar(100))
returns float
as
begin
return exec('SELECT CAST('+@Equation+' AS float)')
end
I am getting this error when i compile it
"RETURN statements in scalar valued functions must include an argument"
Any suggestions would be appreciated.
Please respond
Thanks
View 6 Replies
View Related
Mar 19, 2007
From the SQL Server documentation : "The input parameters and the type returned from a SVF can be any of the scalar
data types supported by SQL Server, except rowversion, text,
ntext, image, timestamp, table, or cursor"This is a problem for me. Here's what I'm trying to do :I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.
View 2 Replies
View Related
Jan 20, 2006
I'm trying to create a SQL server 2000 function that returns a scalar value, but I keep getting the error "Return statements in scalar valued functions must include an argument". Online clarification of this error message is no help at all.I've tried all sorts of combinations of the following, without much luck. Can someone point out my dim-witted mistake, please?ALTER FUNCTION dbo.intCoursesPublic (@intCatID as int) RETURNS intASBEGIN RETURN SELECT COUNT(intCourseID) AS Expr1 FROM dbo.tbl_guru_course_list WHERE (intCatID = @intCatID)END
View 4 Replies
View Related
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);
GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT @t VALUES(1);
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
Nov 2, 2006
Hi all,
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?
Thanks, Jos
Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/
PRINT @@VERSION
go
PRINT 'Scalar function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'In-line table-valued function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go
CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN
INSERT INTO @t VALUES (1)
RETURN
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
/*
----------------------------------------------------------------------------------------------------
*/
And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Scalar function with name "sys_" creates ok...
In-line table-valued function with name "sys_" creates ok...
Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.
View 3 Replies
View Related
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'))
SELECT *
FROM Split(@tmp,DEFAULT)
View 1 Replies
View Related
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
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:
SELECT *
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:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- 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
INSERT @retTableSELECT *FROM SP1
RETURN
END
View 2 Replies
View Related
Jan 6, 2004
Hi there,
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN
CREATE INDEX resultcol2_ind ON @Result
-- do some other stuff
RETURN
END
View 2 Replies
View Related
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
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:
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 4 Replies
View Related
Mar 19, 2008
I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.
Code Snippet
declare @IdTable table
(
EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees
/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )
Any help is very much appreciated.
Andrew
View 3 Replies
View Related
Feb 22, 2008
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?
ALTER PROCEDURE [dbo].[rpt]
(
@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'
)
AS
--split CourtIDs into a table
DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')
View 6 Replies
View Related
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?
Thanks
Mangala
View 3 Replies
View Related
Dec 10, 2002
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF.
I've got:
CREATE FUNCTION myFunction(@param1,...,@param4)
RETURNS TABLE
AS
BEGIN
IF @param1='x'
BEGIN
RETURN(SELECT columns FROM TableX)
END
ELSE
BEGIN
RETURN(SELECT columns FROM TableY)
END
END
I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help!
Thanks,
-Ed H.
View 3 Replies
View Related
Mar 10, 2008
Hi,
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 ..
eg..
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
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
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:
A)
select word, sentence, token
from Words,
Sentences,
dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences?
where word = token
resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."
B)
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
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
OR
I want to add another index on ObjectID.
View 6 Replies
View Related
Aug 22, 2007
hi,
I am using a function in sql server 2005 like this:
...... myfunction(... @FlagOn int)
.......
begin
return
(
if(@FlagOn = 1)
select * from.......
else
select * form....
)
end
But it keeps complaining there is some syntax error around if. What is it?
Thanks.
View 5 Replies
View Related
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.
Thanks,
Lee Silverman
JackRabbit Sports
View 1 Replies
View Related
