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


ADVERTISEMENT

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

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

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

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

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

Oct 5, 2006

Hi,

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

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

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

SQL Server 2012 :: How To Insert One To Many Valued Parameters From A Stored Procedure Into A Table

Jan 14, 2015

I have a SP with Parameters as:

R_ID
P1
P2
P3
P4
P5

I need to insert into a table as below:

R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5

How can I get this?

View 2 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,

IsDeterministic=false)]

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

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

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

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

Error With Multi-Valued Report Parameter Using Stored Procedure

Dec 19, 2007

Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:

CREATE PROCEDURE spprodsales
@productid int

AS

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

RETURN


And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

So, can anyone please help me out possibly using the same stored procedure as above.

Thanks,
Kripa

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

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

Differnce Between Stored Procedure And Stored Functions?

May 22, 2006

Hi all,
What is the Difference between Stored Procedure and Inline Quries...... Is Stored Procedure is faster then Inline Query...
What is the professional Approch....
I m using Inline Quries in my Professional Project with Microsoft Application Block..... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled...
plz guide me..... :)
Thanx
Sajjad

View 3 Replies View Related

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

Need Help On Stored Procedure Using XML Related Functions

Apr 20, 2007

Hi all,I came across the below SQLServer stored procedure, I understand that it manipulates some XML data, but am not sure what it does.. Could any one pls explain me on the below procedure, Also, i do not see 'values' clause in the insert statement in the below procedure, is it valid to use in this way? (as per the syntax, 'values' clause is NOT optional)CREATE PROCEDURE [dbo].[OM_addObjData] @xml_Doc_sample varchar(max),@objID intASBEGIN--set @xml_Doc_sample = '<?xml version="1.0" encoding="utf-8"?><ArrayOfEXEData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><EXEData><EXEName>exeName</EXEName><EXECommand>exeCommand</EXECommand></EXEData></ArrayOfEXEData>'declare @docIdx intEXECUTE sp_xml_preparedocument @docIdx OUTPUT, @xml_Doc_sampleinsert into OM_EXEselect @objID, exeName, exeCommand, IdEXE, EXEFrom OPENXML(@docIdx, N'/ArrayOfEXEData/EXEData', 2) With OM_EXEEXECUTE sp_xml_removedocument @docIdx ENDThanks in Advance,Shefali Mihir

View 2 Replies View Related

Select, And Insert Functions In A Stored Procedure

Feb 2, 2006

I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1

View 6 Replies View Related

Stored Procedure Vs User Defined Functions

Apr 4, 2008

Hi All,

My question is :

Why we are using udf inside stored procedures ?
Will it make any performance faster for the stored procedure to execute ?

awaiting your reply.

Thanks
Renjith

View 6 Replies View Related

Stored Procedure And User-Defined Functions

Sep 25, 2006

lokesh writes "1) What are the differences between "Stored Procedure" and "User-Defined Functions"?

2) Places where we use/don't use Stored Procedure/User-Defined Functions."

View 2 Replies View Related

Asynchronous Call In Stored Procedure / SQL Functions

May 9, 2008

Problem: Have to call SPs reside in 2 SQL server DBs which will return the table information, need to merge and perform sort the result and return the final result set. this is something like wrapper SP calling other SPs asynchronoulsy.

Can this be possible in SP / SQL functions to make asynchronous calls?

If yes can you please guide me to achive this.

If no, any alternative approach, or the reason why.

I appreciate your views on this.


-Hemanth

View 3 Replies View Related

Using MatLab Built-in Statistical Functions Inside SQL Server Stored Procedure

May 17, 2007

Hi everybody,



I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)



Thanks in advance!



JorgeHG.

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

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

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

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







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