How To Use User Defined Function In Stored Procedure?

Mar 14, 2006

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

View 3 Replies


ADVERTISEMENT

User-Defined-Function With-in Stored-Procedure??

Apr 14, 2008

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

View 4 Replies View Related

Stored Procedure - User Defined Function.

Jul 20, 2005

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

User Defined Function To Stored Procedure Call?

Apr 7, 2008

Hello,

Can we call stored procedure from user defined function and vice-versa??

Thanks in advance.

View 4 Replies View Related

Strange Problew With User Defined Function Or Stored Procedure

Jul 23, 2005

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

SQL Server 2012 :: Convert Stored Procedure To User Defined Function?

Feb 23, 2015

I 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]....

View 9 Replies View Related

Difference B/w User Defined Function And Strored Procedure

Jan 3, 2008

Pls tell me the main differences b/w user defined function and stored procedure .

View 2 Replies View Related

Stored Proc Vs User-defined Function

Aug 9, 2005

Dear all,After reading the Book Online, I am still confued by when to use store proc or user-defined function. The most obvious different for me is that 1. UDF can return a table    eg select * from dbo.UDF(a, b , c) In real word application, what factor should i consider?Also, any debug tools in sql server ?Ad_dee

View 3 Replies View Related

Calling Managed CLR Procedure From Inside User Defined Function -- How To ?

May 15, 2008

I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.

I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.

I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.

I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.

However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.

Any advice on how to do this?

Thanks,
Bruce

View 1 Replies View Related

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

Feb 4, 2008

Hi,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 Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Stored Procedure Vs User Defined Functions

Apr 4, 2008

Hi All,

My question is :

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

awaiting your reply.

Thanks
Renjith

View 6 Replies View Related

Stored Procedure And User-Defined Functions

Sep 25, 2006

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

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

View 2 Replies View Related

User Defined Data Type Used In Stored Procedure Parameters

Jul 23, 2005

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

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC &&amp; Sp?

Jan 17, 2008

Hi all,

In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--

USE shcDB

GO

CREATE PROC sp_insertNewRecord @procPersonID int,

@procFirstName nvarchar(20),

@procLastName nvarchar(20),

@procAddress nvarchar(50),

@procCity nvarchar(20),

@procState nvarchar(20),

@procZipCode nvarchar(20),

@procEmail nvarchar(50)

AS INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,

@procCity, @procState, @procZipCode, @procEmail)

GO

EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',

'Michigon', '67899', 'PeterWang@yahoo.com'

GO

=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure


EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'


Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

End Class

===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.

Please help and advise me how to correct these problems.

Thanks in advance,
Scott Chang

View 22 Replies View Related

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)

Jan 23, 2008

Hi Jonathan Kehayias, Thanks for your valuable response.

I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.

Here is my response to the last code/instruction you gave me:

I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.

I got the following debug error message:

1) A Box appeared and said: String or binary data would be truncated.

The statement has been terminated.

|OK|

2) After I clicked on the |OK| button, the following message appeared:

This "SqlException was unhandled

String or binary data would be truncated.

The statement has been terminated."

is pointing to the "Throw" code statement in the middle of

.......................................

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

..........

Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.



Thanks,
Scott Chang

The code of my Form1.vb is listed below:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7

command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"

command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"

command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"

command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"

command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"

command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"

command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"

Dim resulting As String = command.ExecuteNonQuery

MessageBox.Show("Row inserted: " + resulting)

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

InsertNewFriend()

End Sub

End Class

View 6 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Help With User Defined Function

Jan 10, 2007

I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date  is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE  FUNCTION GetStartDate (@startdate datetime, @days int)   RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules.  If 4 business days means you count starting tomorrow, set counter to 0.  If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date.  we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
   --If the day is between 2 and 6 (meaning it's a week   --day and the day is not in the holiday table, we'll    --increment the counter   IF (DATEPART(dw, @enddate) between 2 and 6) AND       (@enddate not in           (           select HolidayDate            from tFederalHoliday            where [HolidayYear] = datepart(yyyy,@enddate)         )       )   BEGIN      set @counter = @counter + 1   END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------

View 1 Replies View Related

User Defined Function

Aug 17, 2005

Hi everyone,
    I am tring to pass acomma delimited string to a function  and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this.  How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks

View 1 Replies View Related

User Defined SQL Function

Dec 14, 1999

How can I create a user defined function in SQL and call it inline from a SQL statement? I need the ability to execute a statement such as:

select myFunc(x,y) from table

I need myFunc(x,y) to return a value, not a recordset and use that value in the select statement. Any and all ideas would be appreciated.

View 1 Replies View Related

User Defined Function

Aug 2, 1999

Dear friends,

I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.

Thanks in advance!

Kai

View 2 Replies View Related

User Defined Function Help!

Jun 23, 2004

I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?

CREATE FUNCTION fnShipments ()
RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
AS
BEGIN
INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers

WHERE Shipments.AccountID = vwAccountTransfers.AccountID

AND vwAccountTransfers.EffectiveMonth =

(SELECT MIN(EffectiveMonth)
FROM vwAccountTransfers

WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
(SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth Is Null )
) AS Territory
FROM Shipments

ORDER BY Shipments.AccountID, Shipments.DateInvoice;
RETURN
END

View 1 Replies View Related

User Defined Function

Nov 10, 2004

I need a UDF that will do the following:

User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'

AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF

I would also like now to be my getdate(), but it seems like you can't use it in UDF

View 1 Replies View Related

User Defined Function Help?

Mar 20, 2004

I create the following UDF just for learning purposes and I cannot seem to get it to return a value.

Stored Procedure

REATE PROCEDURE usp_GetXSection

@fWidth float,
@fHeight float,
@fResult float OUTPUT

AS
BEGIN
set @fResult = [dbo].[XSECTION](@fWidth, @fHeight)
END
GO


Ane the following UDF


CREATE FUNCTION XSECTION
(@Width float, @Height float)
RETURNS float
AS
BEGIN
RETURN (@Width * @Height)
END


I call it using ADO in a c++ application, but all returns empty

Any ideas? C++ code to follow.

Mike B


void CFormTemplateSetup::OnCalculate()
{
_CommandPtr pCmd(__uuidof(Command));
_ParameterPtr pWidth(__uuidof(Parameter));
_ParameterPtr pHeight(__uuidof(Parameter));
_ParameterPtr pResult(__uuidof(Parameter));

try
{
pCmd->ActiveConnection = GetDocument()->GetConnection();
pCmd->CommandText = _T("usp_GetXSection");
pCmd->CommandType = adCmdStoredProc;

_variant_t vWidth, vHeight;
vWidth.vt = VT_R4;
vHeight.vt = VT_R4;

vWidth.fltVal = 10;
vHeight.fltVal = 10;

pWidth = pCmd->CreateParameter(_T("@fWidth"), adInteger, adParamInput, sizeof(int), vWidth);
pHeight = pCmd->CreateParameter(_T("@fHeight"), adInteger, adParamInput, sizeof(int), vHeight);
pResult = pCmd->CreateParameter(_T("@fResult"), adDouble, adParamOutput, sizeof(float), vtMissing);

pCmd->Parameters->Append(pWidth);
pCmd->Parameters->Append(pHeight);
pCmd->Parameters->Append(pResult);

_variant_t vResult = pResult->Value;

float fArea = pResult->Value.fltVal;
CString csArea;
csArea.Format("Area = %d", fArea);

::AfxMessageBox(csArea);
}
catch(_com_error& e)
{
::AfxMessageBox(e.Description());
}
}

View 1 Replies View Related

User Defined Function

Apr 17, 2007

Hi,
Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.

View 13 Replies View Related

Need Help With User Defined Function

Apr 6, 2006

I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.

To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.

The access function is as follows:

Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long

If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If

End Function

The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.

I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.

Any direction would be greatly appreciated!

View 6 Replies View Related

Calling The User Defined Function!!!

Aug 28, 2007

hai,
the problem is - I have created a userdefined function using SQL 2000
create function  getfulldate (@date varchar(10))returns datetimeasbegindeclare @getfulldate datetime set @getfulldate  = dateadd (mi,55,@date)
return @getfulldateend
and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03')  from emp
This works fine and what I need was, I need to invoke the user-defined function like
select *, getfulldate('2006-05-03')  from emp    that is, without using "dbo".
If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.
So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.
anyone guide me, thanks!

View 3 Replies View Related

How To Exec SQL User Defined Function?

Aug 4, 2005

Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks

View 2 Replies View Related

User Defined Function Problem

Feb 28, 2006

I have  a sproc that passes an ID to a user defined function. I just realized that the udf can only return scalar type value. I need it to return a varchar(10). How can i do this with similiar functionality. Any ideas?  

View 1 Replies View Related







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