Store Procedures && Functions
Feb 10, 2006hi,
what is the different between store procedure and function?
when it is right to use sp and when functions?
thanks in advanced
ADVERTISEMENT
Difference Among Store Procedure, Functions And Views
Feb 15, 2006Hi,
What is the basic difference between
store procedure, Functions and views
Thanks
Procedures V. Functions
Jul 23, 2005The place where I work is moving to MS SQL Server from Lotus Notes. Ihave done a lot of coding in Lotus Notes, and have, I suppose,intermediate skills in basic SQL -- queries, insert, updates, tabledesign, etc. I have a couple of questions, however. First, storedprocedures vs. functions. In my world, a function is a body of codethat returns a value; a procedure is a body of code that does thingsbut does not return a value (other than an error if it fails). At firstblush, MS SQL seems to follow this distinction. But further reading ofthe Books Online and paper books (like Advanced T-SQL for SQL Server2000) demonstrate that stored procedures also return values. So, whyuse one over the other? Apparently, I can debug SQL stored procedures(haven't tried yet) and it looks like I can't debug functions. On theother hand, I can use a function in an SQL statement but I can't use aprocedure (I think). So, if I want to take two columns and do somehand-waving over them to make a third column for each row in a row set,I need to use a function.I guess the above gives you a sense for what little I know. What arepeople's thougts on functions vs. stored procedures. What's a good bookthat might give me better insight into these sorts of issues withoutspending a lot of time on what a left outer join or a derived table is?Thanks.BD
View 7 Replies View RelatedMs Sql Stored Procedures And Functions
Jul 11, 2007Hi 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 RelatedStored Procedures And Functions
Sep 9, 2004I 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?
Stored Procedures And Functions
Nov 1, 2006what is the difference between a Stored Procedure and a Function?tia,mcnewsxp
View 1 Replies View RelatedStored 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.
Using User Functions In Stored Procedures
Feb 1, 2005Hi 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.
Viewing Created Procedures And Functions
Sep 11, 2006I'am using SqlServer 2005..
I'am able to view created tables via:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;
How do I do the same with created procedures and functions? i.e how do I view all procedures and functions using SELECT query?
Only Functions And Extended Stored Procedures Can
Jun 18, 2008Hi 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
Views / Stored Procedures / Functions
Dec 1, 2005Hi 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.
Stored Procedures,Triggres And Functions
Jun 13, 2006surjeet 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."
User Defined Functions To Stored Procedures
Jun 1, 2004Hello 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
Stored Procedures Vs. Table-Valued Functions
Apr 11, 2006Hi 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 RelatedManaging 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.
Stored Procedures And User Defined Functions
Feb 4, 2006What 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.
How Tocop Stored Procedures And Functions From One Database To Another?
Oct 9, 2006Hello,
Is there a way to copy some selected or all stored procedures and functions from one database to another?
Thanks in advance.
Best regards,
Diff Between User Defined Functions And Stored Procedures
Jun 19, 2008Hi 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 RelatedStarting Out With : Views, Stored Procedures, Functions, Synonyms
Mar 22, 2006Hi,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 RelatedOnly Functions And Extended Stored Procedures Can Be Executed From Within A Function.
Jun 7, 2006Hi, 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!
Pro SQLCLR 2005 CLR Stored Procedures, Functions, And Triggers TOC
Aug 15, 2006For 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
Do You Replicate Views, Stored Procedures, And User Functions?
Jul 22, 2007Greetings,
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
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.
May 7, 2008Hi 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
Differences Between SQL Stored Procedures And Table-valued Functions
Sep 6, 2006I 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.
Transact SQL :: Only Functions And Some Extended Stored Procedures Can Be Executed From Within A Function
Sep 11, 2013I 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
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.
SQL Server 2008 :: Find Not Existing Calls In Stored Procedures Or Functions
Mar 17, 2015How 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 RelatedStore Procedures
Jan 27, 2004hi everyone :)
I've got a store procedure(sp) on the database side ( MS SQL 2000 )
and want to access the sp via ASP.net
so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.
I've tested the SP and it does work fine
can you just give me a template of how to access the sp from ASP.net
mant thanks indeed
M
Store Procedures For DBA
Mar 16, 2008Can anyone plz tell me what kind of store procedures DBA's needs to write or DBA needs to know?
View 2 Replies View RelatedStore Procedures
Jan 11, 2006Hi:Does anyone know if IBM (or any other IT Software company) offers storeprocedure classes anywhere in the country?ThanksMarc
View 2 Replies View RelatedStore Procedures Return
Apr 28, 2000Hi guys, I want to know if it is possible to retrieve the fieldnames that a
Stored Procedure return, without executing this StoreProc.
Thank you
TSQL And ASP - Store Procedures Help!!!!!!
Dec 1, 2000All,
I have beating my head over this and have asked questions to clear this up in small bits.
In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:
--Normal ASP header
<%
myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'
'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable
Set SysMainQuery=Server.CreateObject("ADODB.Recordset" )
SysMainQuery.open "Select * from tbl_Date", strDSNPath
howmanyfields=SysMainQuery.fields.count -1
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>"
NEXT
response.write "</tr>"
' Now loop through the data
DO WHILE NOT SysMainQuery.eof
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=SysMainQuery(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue=" "
END IF
response.write "<td valign='top' NOWRAP><Font Size=2>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
SysMainQuery.movenext
'howmanyrecs=howmanyrecs+1
LOOP
response.write "</table></font><p>"
' close, destroy
SysMainQuery.close
set SysMainQuery=nothing
%>
</body></html>
The stored procedure takes the input an returns the sql statement:
Alter Procedure MainSysLogQuery
@myDates nvarchar(200) = '',
As
declare @oSql nvarchar(4000)
declare @viewtmp nvarchar(3000)
declare @querytmp nvarchar(3000)
declare @wheretmp nvarchar(3000)
declare @ordertmp nvarchar(3000)
declare @tmplen int
Set @wheretmp = ''
Set @tmplen = 0
if @myDates != ''
Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''
If len(@wheretmp)>0
Set @wheretmp = ' WHERE ' + @wheretmp
print @wheretmp
Set @viewtmp = 'SELECT *'
Set @querytmp = ' FROM tbl_All_SysData'
Set @ordertmp = ' ORDER BY LongDate DESC'
Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp
PRINT @oSQL
Exec(@oSQL)
Go
The problem is I keep getting:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure....
The Errors are on on the recordset.eof, recordset.movenext, etc....
Has anyone ever run into this?
Thanks
~Lee
SQL 6.5 Security For Store Procedures
Sep 30, 1999How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database.
They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that
they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.
TIA for all you responses