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


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

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

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

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

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

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

Ms Sql Stored Procedures And Functions

Jul 11, 2007

 Hi allTrying to figure out what you use ms sql functions for. I understand stored procedures and how to create them. the question is what is the real purpose of a ms sql function considering everything i have read so far makes me think that there is no valid use for them. You can do almost everything that a function does but in a stored procedure. If somebody can give me a good examplle of a sql function i would appreciate it very much.thanks 

View 7 Replies View Related

Stored Procedures And Functions

Sep 9, 2004

I hope I didnt POST in the wrong group. If I did sorry. Anyhoo, on to my question. I have searched the forums and didnt quite find what I was looking for so here goes..
I have create a function that is supposed to return the "SCOPE_IDENTITY" from a stored procedure that updates the database. I'm kinda lost as to how to get the SCOPE_IDENTITY into the function.
I have the following line in the function:

Dim retcode As Integer = cmd.Parameters.Add("@retcode", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
...
cmd.ExecuteReader(CommandBehavior.CloseConnection)

and the following in the stored procedure

@retcode int=NULL OUTPUT
....
SELECT @retcode =SCOPE_IDENTITY()
RETURN @retcode

If I run the stored procedure by itself I get
@retcode = 135
@RETURN_VALUE = 135
So i know the stored procedure works but how do I get that return value into a asp.net function?

View 6 Replies View Related

Stored Procedures And Functions

Nov 1, 2006

what is the difference between a Stored Procedure and a Function?tia,mcnewsxp

View 1 Replies View Related

Stored Procedures And Functions

Apr 30, 2008



I have the following case.

I have a linked server and I want to execute SQL statements on that linked server which contains millions of records. so I decided to use SELECT * from OPENQUERY(RmtSrv,'SELECT * FROM RmtTbl WHERE Col = ' + @ColValue) but Unforetunatly the query is not succeeded because the OpenQuery doesn't accept parameters.

I used Scalar valued functions and built the query dynamically, but the Scalar valued functions doesn't execute dynamic SQL


ET @LinkedServerName = 'RmtSrv'

SET @RemoteTable= 'RmtTbl'

SET @Query = 'SELECT * FROM OPENQUERY({0}, ''SELECT Col1 FROM {1} WHERE Col2 = ''''{2}'''''') '



SET @Query = REPLACE(@Query, '{0}', @LinkedServerName)

SET @Query = REPLACE(@Query, '{1}', @RemoteTable)

SET @Query = REPLACE(@Query, '{2}', @Col2Value)

EXEC(@Query)

the above code is not executed because the functions doesn't execute dynamic queries.

I deleted the function above and wrote a Stored Procedure with the same code. but I can't query the stored procedure

SELECT GetColValue(Table1.Col1)
FROM Table1.


where the GetColValue is the name of the stored procedure.

Do you have any solution that I can use to perform Remote SQL statements considering the performancewise and the code shall be centralized in certain stored procedures and functions

the Remote server contains millions of records and I will use it from tens of applications and databases.

View 9 Replies View Related

Using User Functions In Stored Procedures

Feb 1, 2005

Hi There,
I've written an inline table-valued function in SQL such as the following:

ALTER FUNCTION dbo.GetCityByID( @CityID int)
RETURNS TABLE
AS
RETURN(
SELECT
Name,
Url
FROM Cities
WHERE (CityID = @CityID) )

suppose that Cities table includes three fields (CityID, Name, Url).

By the way I wrote a store procedure as follow:

ALTER PROCEDURE MyProcedure ( @MyID int)
AS
SELECT
CountryID,
OriginCityID,
DestCityID
FROM
MyTable
WHERE (MyID = @MyID)

The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name
and url of each city by its ID through this stored procedue by making relation to Cities table.
so I call GetCityByID function in my stored procedure like this:

ALTER PROCEDURE MyProcedure ( @MyID int)
AS
SELECT
CountryID,
dbo.GetCityByID(OriginCityID),
dbo.GetCityByID(DestCityID)
FROM
MyTable
WHERE (MyID = @MyID)

this procedure dosn't work an returns error.

What's your solution for getting information from Cities table for OriginCityID and DestCityID?
Thank you in advance.

View 1 Replies View Related

Only Functions And Extended Stored Procedures Can

Jun 18, 2008

Hi all,
In one of my UDF I use the following functions:

.....
and len(@int_date) = 4
and isnumeric(substring(@int_date,5,6)) = 1

when I use the function I get

Only functions and extended stored procedures can be executed from within a function.

Yes, when I comment the two lines the function works fine.
Ehm.... why can't I use these functions in my function ?
Thanks: Peter

View 3 Replies View Related

Views / Stored Procedures / Functions

Dec 1, 2005

Hi All,

Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.

View 7 Replies View Related

Stored Procedures,Triggres And Functions

Jun 13, 2006

surjeet writes "Sir My question is ;
Sir i am a software Engineer.I want to know about the Stored Procedures.Please Give me briefly details and examples of Stored Procedures.How to use Conditions and Literals ,How to use Procedures,Triggres,Functions in Crystal Reports and Suitable method of Data Base Design."

View 3 Replies View Related

User Defined Functions To Stored Procedures

Jun 1, 2004

Hello all:

Running into a brain problem here. I remeber reading an article a while back (2002?) on either Visual Studio Magazine or MSDN Magazine where there was a way to generate Stored Procedures from User Defined Functions. I need this information in order to do my job as it is also a way to cut down on time for this project I am trying to finish. Does anyone have the code or remeber what I am talking about. I just finished Kathleen Dollards article again on using XSLT to generate code but would really like to use the User Defined Functions.

I searched for the article on line but came up dry. Searched through all my magazines but could not find the article. Any help would be greatly appreciated. Bit of topic I guess but still relevant to the board.

Thanks

View 1 Replies View Related

Managing Stored Procedures / Functions For Releases.

Jun 5, 2006

 WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.
What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.
A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure.  Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.
We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...
What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.
We have 8 developers in our team.
If anyone could help or give advice on this, it would be awesome.
Thanks.

View 2 Replies View Related

Stored Procedures And User Defined Functions

Feb 4, 2006

What are the pros and cons of each?

One advantage that I can see withh UDFs is that they are a bit a Views with parameters. You can perform joins on UDF columns (which you cannot do with a Stored Proc). You can do the same with Views but UDFs have the advantage that you restrict the number of rows with a parameterised WHERE (or HAVING) clause.

View 4 Replies View Related

How Tocop Stored Procedures And Functions From One Database To Another?

Oct 9, 2006

Hello,

Is there a way to copy some selected or all stored procedures and functions from one database to another?

Thanks in advance.

Best regards,

View 3 Replies View Related

Diff Between User Defined Functions And Stored Procedures

Jun 19, 2008

Hi to all,            Can any body tell me what is the difference between Stored procedures and User Defined Functions ?             In my assumption Function return a value or table, but SP doesn't return value instead of that SP use select statement or assign value to output statement. This is right?           

View 3 Replies View Related

Starting Out With : Views, Stored Procedures, Functions, Synonyms

Mar 22, 2006

Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time

View 1 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.

Jun 7, 2006

Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)

EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate

It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)

I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?

Thanks you!

View 15 Replies View Related

Pro SQLCLR 2005 CLR Stored Procedures, Functions, And Triggers TOC

Aug 15, 2006

For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.

Chapter 1   Introducing SQLCLR
Chapter 2   Building a Procedure
Chapter 3   SQLCLR Strucutre & Common Tasks
Chapter 4   Creating Objects
Chapter 5   Compare & Contrast
Chapter 6   Replacing TSQL Objects
Chapter 7   Using the Base Library
Chapter 8   Using Procedures in Apps
Chapter 9   Error Handling
Chapter 10 Administration
Chapter 11 Case Study

Here is the link:

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html

Enjoy,

Derek

View 2 Replies View Related

Do You Replicate Views, Stored Procedures, And User Functions?

Jul 22, 2007

Greetings,



We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.



Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?



Thanks for any ideas that you share.



BCB

View 4 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.

May 7, 2008

Hi mister, I have this script sql but I get this error:



Mens. 557, Nivel 16, Estado 2, LĂ­nea 1

Only functions and extended stored procedures can be executed from within a function.

DROP FUNCTION ObtenerTablaPorNombre2

GO

CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))

RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )

AS

BEGIN

DECLARE @cmd nvarchar(max)

DECLARE @params nvarchar(max)

DECLARE @NombreTabla VARCHAR(MAX)

DECLARE @Descripcion VARCHAR(MAX)

DECLARE @CIF VARCHAR(MAX)

SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]

FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla

SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla

--EXEC (@cmd)

SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'

SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '

EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla

RETURN

END

GO

SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function

View 2 Replies View Related

Transact SQL :: Only Functions And Some Extended Stored Procedures Can Be Executed From Within A Function

Sep 11, 2013

I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.

When I run it in my SQL2008R2 environment I get the error:

Only functions and some extended stored procedures can be executed from within a function.

The SQL2008R2 server is new. What can I look for?

Here's the code for the function:

BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END

View 4 Replies View Related

Programmatically Extract Stored Procedures, Views, Tables, And Functions

May 21, 2008



Hi Folks,


I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.

I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?

For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.

For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).

For Views, I need to know the Name(s) and access the Views Source

For functions, I need to know the Name(s) and access the function source.


I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:

select * from sys.objects .. I get an error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.



Thank you in advance.



View 13 Replies View Related

SQL Server 2008 :: Find Not Existing Calls In Stored Procedures Or Functions

Mar 17, 2015

How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.

View 7 Replies View Related







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