Showing posts with label functions. Show all posts
Showing posts with label functions. Show all posts

Wednesday, March 21, 2012

differnce between stored Procedure and stored Functions?

Hi all,
What is the Difference between Stored Procedure and Inline Quries..... Is Stored Procedure is faster then Inline Query...
What is the professional Approch...
I m using Inline Quries in my Professional Project with Microsoft Application Block.... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled...
plz guide me.... :)
Thanx
Sajjadwhat is the differnce between stored Procedure and stored Functions ????|||please check first in any search engine.|||Functions are easier to fold into SELECT statements, as they return scalar or table values.
Stored procedures have fewer programming limitations than user-defined functions, but must be run independently and not as part of a select statement.sql

Tuesday, February 14, 2012

Differences between 2000 and 2005

Hi,

Just want to validate that, contrary to 2000, 2005 will tolerate the use of SELECT statements within functions.

Thanks

That's not true. BOTH SQL 2000 and 2005 will allow the proper use of SELECT statements inside the function.

However, neither will allow a SELECT statement to be used to return data to the client outside of the defined function data return (either scalar or TVF.)

|||

Thanks

Are you aware of any difference between the 2 versions (regarding functions) ?

|||

Yes, there is a lot of new functionality that can be employed in User Defined Functions. New T-SQL keywords, xml datatypes, xquery, clr functions, etc. The list of new capabilities is very long.

Can you be more specific about your needs?

|||

Arnie Rowland wrote:

Yes, there is a lot of new functionality that can be employed in User Defined Functions. New T-SQL keywords, xml datatypes, xquery, clr functions, etc. The list of new capabilities is very long.

Can you be more specific about your needs?

Sorry...

I meant besides new functionnalities in SQL Server 2005 (which cannot be used in 2000).

We found the problem...

We were trying to implement a function to bypass the real datetime. So instead of using getdate(), we would use fn_getdate().

fn_getdate() returns either a date from a table or getdate() if no value is found.

In SQL 2000 you cannot use getdate() in a function. That was the source of the error.

Thanks

|||This function works fine in 2005

Code Snippet

IF OBJECT_ID (N'dbo.fn_getdate', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_getdate;
GO
CREATE FUNCTION dbo.fn_getdate ()
RETURNS datetime
AS
BEGIN
DECLARE @.newdate datetime
SELECT @.newdate= CASE WHEN dt.date IS NULL THEN GETDATE() ELSE dt.date END FROM TestTable dt
RETURN(@.newdate)
END;
GO

SELECT dbo.fn_getdate()

Post your code. What exactly is the error you are getting?

|||

WITH EXECUTE AS CALLER

Works with SQL 2000?

|||Sorry, I said 2000 and 2005 and that is incorrect. The code is 2005 specific.

|||This is the best workaround I could think of for 2000:

Code Snippet


IF OBJECT_ID (N'dbo.fn_getdate', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_getdate
GO
CREATE FUNCTION dbo.fn_getdate(@.curdate AS DATETIME)
RETURNS datetime
AS
BEGIN
DECLARE @.newdate datetime
SELECT @.newdate=NULL Insert code here for table
IF @.newdate IS NULL
SET @.newdate = @.curdate
RETURN(@.newdate)
END
GO

SELECT dbo.fn_getdate(GETDATE())


|||

Hi Tom,

Nice workaround... Thanks

I've been through many articles about differences between 8.0 and 9.0 but none mention this one. Do you know the reason behind and are you aware of similar differences?

Thanks again

|||I was not able to find any specific reference to "fixing" this issue. But I knew that worked because I used it a few months ago in 2005, unknowing it did not work in 2000 until I tried it.

I generally stay away from functions because of performance issues. UDF are very costly in execution time. If you can do what you want another way, without a UDF, I would.

|||

Tom Phillips wrote:

I generally stay away from functions because of performance issues. UDF are very costly in execution time. If you can do what you want another way, without a UDF, I would.

What do you mean by "very costly"?

I ran benchmarks and in my case the results show UDFs are slower by about 10%.

|||In your case it might only be 10% slower, for this particular function.

But, if you do something like:

SELECT *
FROM Table
WHERE udf(table.abc)

and Table has 100,000,000,000 rows, it executes the udf for every single row in a table scan mode to find out if it matches the where clause.

|||

Ok I understand.

Thanks!