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


ADVERTISEMENT

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

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

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




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

Variable Type Errors When Calling Stored Procedure

May 12, 2008

I currently have a stored procedure that is defined as follows:


CREATE PROCEDURE UpdateSyncLog

@TableName char(100),

@LastSyncDateTime datetime,

@ErrorState int OUTPUT


I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:

exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState

Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).

I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32

The parameter mappings are as follows:

Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1

Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1

The error I am getting when running this execute sql task is as follows:


Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.

What gives?

View 2 Replies View Related

Grant All Function Giving Errors

Feb 22, 2006

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @sp_name AS sysname;
DECLARE syscursor CURSOR FOR
  SELECT name FROM sysobjects
  WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);


OPEN syscursor;
FETCH NEXT FROM syscursor INTO @sp_name;


WHILE (@@FETCH_STATUS = 0)
BEGIN
  EXECUTE ('GRANT all ON ' + @sp_name + ' TO Public');
  FETCH NEXT FROM syscursor INTO @sp_name;
END


CLOSE syscursor;
DEALLOCATE syscursor;


 

 

View 5 Replies View Related

Calling A Function From A Stored Procedure

Oct 26, 2007

Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions.  My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing.  Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool.  The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor).  So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT *
FROM UDFmanufacturer_SearchName('mol')  SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
 I'm stuck.  I haven't been able to find anything that shows me where I'm going wrong.  Any thoughts or suggestions are appreciated. Thanks,Jay

View 4 Replies View Related

Calling Scalar Valued Function From SSIS OleDB Command Transformation

Mar 2, 2007

Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!!

View 8 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 5 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 1 Replies View Related

Calling User Defined Function From Stored Procedure

Jul 30, 2007

When i call user defined function from stored procedure, i am getting an error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.


why this error is happening?.

i am calling this way... dbo.MyFunction(param1,param2)

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

Calling A Stored Procedure Or Function From Another Stored Procedure

Mar 2, 2007

Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



SET NOCOUNT ON



DECLARE @TTL int

SET @TTL = dbo.TTLValue()



DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

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

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

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

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

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

RETURNS TABLE

AS

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

GO

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

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

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

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

Thanks in advance,
Scott Chang

View 8 Replies View Related

Common Table Expression Calling Stored Procedure

Nov 19, 2007

Hi,

I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.

So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.

This is what I want to change:

Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))

Insert into @TempTable
Exec Procedure @Paramter

select @Col = col2 from @TempTable

INTO

With CTE(Col2, Col3, Col4) AS
(

Exec Procedure @Paramter)

select @Col = col2 from @TempTable

Thanks
Punu

View 6 Replies View Related

Table-Valued Function

Aug 8, 2007

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

Thanks

Mangala

View 3 Replies View Related

Join With Table Valued Function

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

Trigger On Table-valued Function?

Jul 20, 2005

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

View 2 Replies View Related

How To Join Using A Table-valued Function?

Oct 22, 2007

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

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

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

The Attempts:
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

Table Valued Function And Constraints

May 29, 2008



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

Example Header:


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

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






ObjectID int ,







Seq int null )




I want the primary key to be pk_Key, ObjectID

OR

I want to add another index on ObjectID.

View 6 Replies View Related

Can I Use If Statement In A Table Valued Function?

Aug 22, 2007

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

Indexing Table-valued Function?

Aug 30, 2007

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

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

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

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

Thanks,
Lee Silverman
JackRabbit Sports

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

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

Oct 20, 2014

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

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

[code].....

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

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

[Code] .....

View 4 Replies View Related

Multi-statement Table-Valued Function

Oct 18, 2007

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

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

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

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

instead of

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

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

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

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

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

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

Thank you.

View 7 Replies View Related

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

Problem Passing A Variable Into A Table-valued Function

Sep 26, 2007

Hi,

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

The syntax is as follows:

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

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

The database version is SQL Server 2000 SP3.

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

View 10 Replies View Related

Inline Table-valued Function With Multi-value Parameter

Jul 18, 2007

Hello everybody,

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

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

Thanks in advance for your answers.

View 7 Replies View Related

Performance Of Table-valued Function And Execution Plan

Mar 31, 2008

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

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

I have created indexes for the related joinning tables.

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

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

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

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

Thanks


View 3 Replies View Related







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