Stored Procedure And User-Defined Functions
Sep 25, 2006lokesh 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."
ADVERTISEMENT
Stored Procedure Vs User Defined Functions
Apr 4, 2008Hi 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
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 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.
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 Related(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure
Feb 4, 2008Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies View RelatedTransact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?
Nov 5, 2015Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies View RelatedUser-Defined-Function With-in Stored-Procedure??
Apr 14, 2008Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help.
Code:
CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2))
RETURNS VARCHAR(2)
AS
BEGIN
IF (LEN(@sValue) < 2)
SET @sValue = '0' + @sValue
RETURN @sValue
END
Thanks...
Stored Procedure - User Defined Function.
Jul 20, 2005Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn
View 1 Replies View RelatedHow To Use User Defined Function In Stored Procedure?
Mar 14, 2006Hello friends,
I want to use my user defined function in a stored procedure.
I have used it like ,
select statement where id = dbo.getid(1,1,'abc')
//dbo.getid is a user defined function.
procedure is created successfully but when i run it by exec procedurename parameter
I get error that says
"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getid", or the name is ambiguous."
Can any body help me?
Rgds,
Kiran.
Stored Procedure And Calling User Defined Function
Sep 29, 2007I 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 :>
Stored Procedure And Calling User Defined Function
Sep 29, 2007I 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 :>
Calling User Defined Function From Stored Procedure
Jul 30, 2007When 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)
User Defined Function To Stored Procedure Call?
Apr 7, 2008Hello,
Can we call stored procedure from user defined function and vice-versa??
Thanks in advance.
User-defined Functions - Help!
Sep 3, 2004Hi,
I have a search returning many 'car' records (regno, mileage, color, etc)
In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.
Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.
I have written a function as such (with some help!):
ALTER Function getAccs(@regNo varChar(20))
RETURNS varChar
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END
I was hoping that I could simply use this in the 'car' SELECT statement.
ie:
SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList
FROM tableBla etc
I'm not even sure if the function works - How can I test in SQL analyzer?
any help much appreciated,
Pete
User Defined Functions In SQL 7.0
Oct 11, 2000Hi,
I know that we cannot have User Defined Functions in SQL 7.0, but is there a work around. I am trying to standardize the date according to the time zone using a function and use it as part of a select statement.
I cannot believe that is not possible in SQL 7.0, can somebody tell me what is the work around, I am running against time. I really appreciate any help on this one.
Satish.
User Defined Functions And OLE
Aug 18, 2006Hi everyone
I am implementing an encrypted data system whereby captured data is encrypted using MS CAPICOM when written to DB and decrypted when displayed. It works well. However, each time you write the data back on update the encryption is different from the previous time.
I have a search screen that allows users to enter text data and submit it looking for matches. Of course, the user is entering unencrypted text and the DB data is encrypted.
This means that you can't encrypt the input data before you try to match because the encryption alogorithm used on the input data will not match that which was used to encrypt the DB data.
Are you with me so far?
So, you have to compare the uncencrypted input data with DECRYPTED DB data - in other words, decrypt the DB data on the fly within the where clause of your query.
I have accomplished this by writing a UDF that instantiates an instance of the CAPICOM encryption module and calling the UDF when applying the query
eg where udf(columnname1) = 'inputtext' or udf(columnname1) = 'inputtext'.
It works, I get the results that I want.
But, alas, the performance has taken a search severe hit.
Has anyone else ventured down this road?
Is there a better way of doing this?
Thanks
Ray
User Defined Functions
Jan 27, 2004hi all,
I made myself a user defined function, it works great, but is there a way to make it available to all databases?
cheers,
alex
User Defined Functions
Feb 19, 2004When I define a UDF in SQL 2000 I can't execute it without qualifiction. Here's the code:
CREATE FUNCTION GetLastShipDate(@DO_No CHAR(10))
RETURNS CHAR(10)
-- DROP FUNCTION GetLastShipDate
BEGIN
DECLARE @v_last_ship_date CHAR(10)
SELECT @v_last_ship_date = COALESCE(CONVERT(CHAR(10), MAX(Asn.Asn_Ship_DateTime), 110), '')
FROM Asn,Asn_Do
WHERE Asn_Do.Asn_Number=Asn.Asn_Number
AND Asn_Do.DO_Number = @DO_No
AND Asn.ASN_STATUS in ('SENT','RESENT')
RETURN @v_last_ship_date
END
So I have to execute this with:
SELECT dbo.GetLastShipDate('T010215004')
I want to execute it with
SELECT GetLastShipDate('T010215004')
What am I doing wrong?
User Defined Functions
Apr 15, 2007Hi,
Iam new to sql server. I want to create a user defined function to get date in a year(starting day of a week-i.e sunday date) when we give week number as argument. can any body help on this issue?
User-Defined Functions In DTS
Jul 23, 2005Is there a way to call a SQL user-defined function in a DTS, perhapsin an ActiveX transformation? I want the destination table to have avalue that was modified by a pre-defined function.-Noelle
View 1 Replies View RelatedUser Defined Functions
Jul 6, 2006Hi everyone,
Today while using user defined functions, I noticed that we must write dbo. before the function name which I did not conceive why it is used like that. Why are there an exception for functions ?
Why do we not have to use the same thing while using SP or not user defined functions ?
Thanks
User Defined Functions
Nov 15, 2006declare @statusID varchar(100)
set @statusID = '1, 2'
--this works
select id from SplitWords(@statusID, ',')
--this doesn't
declare @sql nvarchar(2000)
set @sql = 'select * from Mast where ' + 'phmStatusID in ( select id from ' + 'SplitWords(@statusID, '', '')' + ') '
exec sp_executesql @sql
is what I'm trying to do not allowed? if so, whats the right way to do this?
any help appriciated. thanks.
Help With User Defined Functions
Jul 18, 2007Dear all,
I was given a project to transfer our database into sql server database.
In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .
Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \let int4 be a user definded function.
If i can€™t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?
Thanks in advance,
Best regards,
Hellen
Strange Problew With User Defined Function Or Stored Procedure
Jul 23, 2005I am trying to add a simple case statement to a stored procedure oruser defined function. However when I try and save thefunction/procedure I get 2 syntax errors. Running the query in queryanalyser works fine and a result is given with no syntax errors. Ibelieve its something to do with the spaces in the field names. Not mychoice as its an existing system I have to work around. Any helpgreatly appreciatedSQL QueryDECLARE @pfid VARCHAR(100)SET @pfid = '000101'SELECTCaseWHEN GetDate()BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfidUser Defined Function (Errors Line 11 & 15)CREATE FUNCTION dbo.get_Margin(@pfid VARCHAR(100), @dtNow DATETIME)RETURNS DECIMAL ASBEGINDECLARE @Return as DECIMALSET @Return = (SELECTCaseWHEN @dtNowBETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfid)RETURN @ReturnEND
View 3 Replies View RelatedUser Defined Data Type Used In Stored Procedure Parameters
Jul 23, 2005I have several stored procedures with parameters that are defined withuser defined data types. The time it takes to run the procedures cantake 10 - 50 seconds depending on the procedure.If I change the parameter data types to the actual data type such asvarchar(10), etc., the stored procedure takes less that a second toreturn records. The user defined types are mostly varchar, but someothers such as int. They are all input type parameters.Any ideas on why the stored procedure would run much faster if notusing user defined types?Using SQL Server 2000.Thanks,DW
View 13 Replies View RelatedLearning About User Defined Functions
May 26, 2006I am getting some trial by fire here as I need to update some user defined functions in an application a co-worker built. He has since left and I've been asked to take over this project since I had a VB 6.0 and regular ASP class 6 years ago ... obviously quite a transition! I have started to figure out a bit but I can't find much on creating my own User Defined Functions and/or the best ways to test ones that I need to edit. My first one came back with this after I added it to SQL Server 2005 through Enterprise Manager... Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.fn_User_Data'.Thanks for any tips,Andrew
View 1 Replies View RelatedUser Defined Functions In SQL Server 7.0
Nov 4, 2004hai,
I have 3 fields in my table say (F1, F2, F3). I want to get the max value out of the three fields for each row. I can create a user-defined function which accepts 3 arguments and then return the max value if i am using SQL Server 2000. But now i am using only SQL Server 7.0 (it does not support user-defined functions :confused: )
So any one could kindly let me know how could i do it in SQL Server 7.0
Thnks in advance
User Defined Functions And EXEC
Mar 5, 2008I'm using MS SQL Server 2005.
I want to simulate a table, using a Multi-statement Table-Value User-Defined Function, but I need the function build the SQL statement from scratch each time so I can dynamically define values like the table it references. The only way I know how to run the query after it has been defined in this manner is to run an EXEC command. However I'm getting an error basically saying that the EXEC command is off limits in a User Defined Function.
The Exsact Error is:
>[Error] Script lines: 1-108 ------------------------
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Is there some way to get around this limitation of User Defined Functions. Or perhaps a way to simulate the functionality of Multi-statement Table-Value User-Defined Functions in a Store Procedure, specifically the ability to run where statements, or transform the data on the fly without re-writing the Stored Procedure every time.
The code I’m trying to run is below.
(Note: The code works as a stored procedure, so I'm sure that the core of the statment is correct)
CREATE FUNCTION [dbo].[TrendLine]
(
@Summary as smallint,
@Start as datetime,
@End as datetime,
@Table as varchar(100),
@X as varchar(100),
@Count as varchar(100),
@Duration as varchar(100)
)
RETURNS
@TrendLineTable table (
DATE_DAY datetime
, EQ_REGION varchar(25)
, EQ_MARKET_CLUSTER varchar(30)
, Y float
, X int
, DURATION float
, FORMULA varchar(100)
, a float
, b float
, EX int
, EY float
, EX2 int
, EXY float
, N int
)
AS
BEGIN
DECLARE @SQL as varchar(3000)
, @CountText as varchar(150)
, @StartText as varchar(50)
, @EndText as varchar(50)
SET @StartText = 'cast( ' + char(39) + cast( @Start as varchar(20) ) + char(39) + ' as datetime ) '
SET @EndText ='cast( ' + char(39) + cast( @End as varchar(20) ) + char(39) + ' as datetime ) '
IF @Summary = 1
BEGIN
SET @CountText = 'sum'
END
ELSE
BEGIN
SET @CountText = 'count'
END
SET @SQL = 'INSERT INTO @TrendLineTable
DECLARE TrendlineC cursor for
SELECT a.DATE_DAY
, s2.EQ_REGION
, s2.EQ_MARKET_CLUSTER
, ( ( EY - ( b * EX ) ) / N ) + ( b * X ) AS Y
, X
, Y AS DURATION
, cast( b as varchar(100) ) + ' + char(39) + 'x + ' + char(39) + ' + cast( ( EY - ( b * EX ) ) / N as varchar(100) ) AS FORMULA
, ( EY - ( b * EX ) ) / N AS a
, b
, EX
, EY
, EX2
, EXY
, N
FROM (
SELECT EQ_REGION
, EQ_MARKET_CLUSTER
, sum( X ) AS EX
, sum( Y ) AS EY
, sum( X2 ) AS EX2
, sum( XY ) AS EXY
, count( X ) AS N
, ( ( count( X ) * sum( XY ) ) - ( sum( X ) * sum( Y ) ) ) / ( ( count( X ) * sum( X2 ) ) - POWER( sum( X ), 2 ) ) AS b
FROM (
SELECT ' + @X + ' AS DATE_DAY
, EQ_REGION
, EQ_MARKET_CLUSTER
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
, cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
, POWER( row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ), 2) X2
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) * cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + ' ( ' + @Count + ' ) AS XY
FROM ' + @Table + '
WHERE ' + @X + ' >= ' + @StartText + '
AND ' + @X + ' < ' + @EndText + '
GROUP BY ' + @X + '
, EQ_REGION
, EQ_MARKET_CLUSTER
) s1
GROUP BY EQ_REGION
, EQ_MARKET_CLUSTER
) s2
INNER JOIN (
SELECT ' + @X + ' AS DATE_DAY
, EQ_MARKET_CLUSTER
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
, cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
FROM ' + @Table + '
WHERE ' + @X + ' >= ' + @StartText + '
AND ' + @X + ' < ' + @EndText + '
GROUP BY ' + @X + '
, EQ_REGION
, EQ_MARKET_CLUSTER
) a ON s2.EQ_MARKET_CLUSTER=a.EQ_MARKET_CLUSTER'
EXEC ( @SQL )
RETURN
END
User Defined Functions In SQL Server 7.0
Jul 20, 2005Hi all!!Does anybody know how I can create a function in SQL 7.0?? I have tocreate functions that return a value that can be used in a selectstatement. I think SQL Server version 7.0 doesn't support CREATEFUNCTION, does it?Ex:Select MyFunction(Parameter)From MyTableThanks a lot,
View 2 Replies View RelatedCalling User-defined Functions In Another DB
Jun 28, 2006I have a number of databases that require a set of common functions. I'd like to place all those functions in a central DB. I'm having trouble calling them using the syntax FunctionDB.GetParamLength() for example, which works within the FunctionDB database.
Any ideas/suggestions? I really don't want to maintain seperate copies of the functions across 5+ databases.
SQL Server 2012 :: Convert Stored Procedure To User Defined Function?
Feb 23, 2015I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.
I have tried to create function, but the error I am facing is select statement can not return data.
CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
[code]....
User-Defined String Functions Transact-SQL
May 3, 2005Ladies and Gentlemen,
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards