Table Valued Functions - Yield Return Error

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


ADVERTISEMENT

RETURN Statements In Scalar Valued Functions Must Include An Argument ERROR

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

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 View Related

Return Statements In Scalar Valued Functions Must Include An Argument

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

Table Valued Functions : Why?

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

Views Or Table Valued Functions?

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

Help!!! Troubles With Table-valued Functions

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

Table Valued Functions Vs Views

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

Inline-table-valued Functions

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

Stored Procedures Vs. Table-Valued Functions

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

Multi-statement Table-valued Functions

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

Problem With Stored Procedure And Table-valued Functions

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

Differences Between SQL Stored Procedures And Table-valued Functions

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

Passing Table Names To Table Valued Functions

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

Table-valued UDF With Conditional RETURN

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

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:

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

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
)
RETURNS TABLE

[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

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Can't Use The NTEXT Datatype In SQLCLR Scalar-valued Functions

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

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);
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

Return NULL From A CLR Scalar-Valued Function

Jul 11, 2007

Hi,



I have an assembly that contains the following function:



Public Class Lookup



<SqlFunction()> _

Public Shared Function MyTest() As Integer

Return System.Data.SqlTypes.SqlInt64.Null

End Function



End Class



Then in SSMS:



CREATE ASSEMBLY IRS_MyTest

FROM '\machine empmyAssembly.dll'

GO

CREATE FUNCTION dbo.MyTest() RETURNS INT

AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest

GO



when I run:



SELECT dbo.MyTest()



the following is returned:



Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException:

at System.Data.SqlTypes.SqlInt64.get_Value()

at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)

at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()





Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.







Thanks,



Jan.

View 1 Replies View Related

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With Sys?

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

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'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Return And Assigning Values In Functions

Dec 19, 2007

Hi ,

I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ?

For eg I have written the following function which I called from a stored procedure.
BEGIN
--Declare the return variable here
DECLARE @Rows NUMERIC(10)
DECLARE @RETURN_ENABLED VARCHAR(1)
-- Add the T-SQL statements to compute the return value here

SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name
Group By Profile_INdex

IF @Rows > 0
SELECT @RETURN_ENABLED = 'N'
ELSE
SELECT @RETURN_ENABLED = 'Y';


-- Return the result of the function
RETURN @RETURN_ENABLED;

END

Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?

From the stored procedure I'm calling the function inside a CTE.

;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled)
AS
(--Anchor.
select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item ,
dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
From sys_menu_item as tb1
where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help')
And tb1.item_parent= @menu_name
--Members
UNION ALL
select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item ,
dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
from sys_menu_item as tb2 , GetHierarchy
where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help')
And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name
)
select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled
From GetHierarchy

Am I doing it correctly ?

I would appreciated any help you could give me.

Thank you

View 5 Replies View Related

Analysis :: YTD / MTD Functions Return Empty Values Probably Due To Old Test Data

Jun 17, 2015

I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.

View 4 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

Which Of These SELECTs Will Yield Better Performance?

Oct 7, 2004

I'm about to build a batch of procedures that will be returning one row of data that is created by referencing the same table multiple times. The example below is very simplified but you'll see the differences.

Which is better:

Option A:

SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM MyTable a, MyTable b
WHERE a.ID = 10
AND a.Type = 1
AND b.ID = 10
AND b.Type = 2


Or Option B:

SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM MyTable a
JOIN MyTable b ON a.ID = b.ID
WHERE a.ID = 10
AND a.Type = 1
AND b.Type = 2


Thanks!

View 3 Replies View Related

Indexes On Table Variable Of Table Valued Function

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

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

Which Method Of Creating Records Would Yield The Best Performance?

Sep 13, 2004

This question is regarding a "helper app" I'm building to go along with my ASP.NET appplication. It will be inserting/updating records in the database as a nightly process. It is a Windows application built in VB.net.

I have a table which should always only have one of each type of record in it. This table on average will have between 100k and 500k records.

Which operation would be faster and less strain on the server?

a. Use a "if exists" and see if a record of this type already exists, if it does, update it, if not, insert the new one.

b. Unconditionally issue a delete for the record I'm about to insert, then insert the new one.

c. Create a trigger that will delete the old record if a new one is inserted?

Thanks!

View 1 Replies View Related

Update Select Statement To Yield Results

Oct 22, 2014

I have the followinf select statement..

SELECT - 1 AS OrganisationID, '--Please Select--' AS OrganisationName
UNION ALL
SELECT OrganisationID, OrganisationName
FROM tblOrganisation
ORDER BY OrganisationName

Results

OrganisationID OrganisationName
22 Animal
15 Birds
-1 --Please Select--
40 Reptiles
36 Snakes

I want the results to be:

OrganisationID OrganisationName
-1 --Please Select--
22 Animal
15 Birds
40 Reptiles
36 Snakes

How can I update my SQL select statement to yield these results..

View 6 Replies View Related

Temporary Table Vs. Table Valued Function

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

Table-valued Function Run Once For Each Row In A Table Variable.

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







Copyrights 2005-15 www.BigResource.com, All rights reserved