Difference between SP and function

Hi All,

It may be sound weird. I want to find the difference between SQL Stored procedure and functions. I knew a couple one is for function, parameter is must where as in SP its not, second is function would return a value whereas procedure wont.

Is there anythign else?That was it! You are on the right track :)|||>> second is function would return a value whereas procedure wont.

not necessarily. even a stored proc can return values ( see OUTPUT PArameters).

Function can return only ONE value where as a stored proc can return multiple values.
performance wise there isnt any diff.

I remember having googled about this once and did come across a couple of articles that xtensively described the differences..so I would say google and you can find more definitive answers.

hth|||I guess "parameter is must where as in SP " he ment for OUTPUT param!|||one use of a function is to use it to return a table object that can be used in another sql statement|||Hi

Thanks you all for your answers. But in the recent inteview which i attended they asked for one more difference between these two apart from those i specified earlier.|||I'm sure you could Google and find all of the information you need.

The main thing about UDFs is that they need to be deterministic -- that is, the same input parameters will always return the same result. So therefore you cannot, for example, directly use GETDATE() in your UDF. Another biggie is that you cannot use either @.@.ERROR or RAISERROR. And another biggie is that dynamic SQL cannot be executed.

I've gotten burnt by all of the above, and others. Some have workarounds, some do not.

Terri

Difference between procedure and function ?

What is the difference between procedure and function

Hi Sahara,

Please read the BOL for more information about them. You can find the answer in the following sections:

Stored procedures:

Designing and Creating Databases > Stored Procedures (Database Engine) > Understanding Stored Procedures >

Functions:

Designing and Creating Databases > User-defined Functions (Database Engine) >

Regards,

Janos

|||

here my findings

Features

Procedures

Functions

Parameters

Supports in, out, in & out

Only supports in

Temp Object

Accessible – You can use the temp tables inside the procedure

Not supported

Create as Temp

Accepted –

Create Proc #MyProc..

Not supported

Select Result

Supported

Not supported

Return

Return integer value

Returns any type of value

On DML Quires

Not allowed

You can embed the function on query

Calling SPs

Allowed

Not Allowed

Calling Another Functions

Allowed

Allowed

Insert/Update/Delete

Allowed

Not Allowed

Or

Only allowed against the table variables

Recursive Operation

Allowed

Allowed

Versioning (grouped)

Allowed

Not Allowed

Schema Binding

Not Allowed

Allowed

Creating Objects

Allowed

Not Allowed

EXEC

Allowed

Not Allowed

SP_EXECUTESQL

Dynamic SQL

Allowed

Not Allowed

GETDATE() or other non-deterministic functions

Allowed

Not Allowed

SET OPTION

Not Allowed

Allowed

Setting Permission

Grant/Deny

Yes

No for scalar functions.

Yes for Table Values/Inline Functions

Difference between indexing txt or word doc files, help!

Hi,
I'm putting together a system and one of the requirements is to have a
searchable CV function.
I've got all the code to load the files on to the image fields, I've indexed
and got it kinda working.
Before I go to far down the road what is your opinion on having txt files
instead of doc files held on the table search? The SQL seems to be more
flexible on
searches rather than on the binary files and the index files themselves are
smaller
..i.e. when I tried a like clause it told me this would only work against a
varchar field
(i'm thinking this may be a schoolboy error so forgive me)
My main concern is I'd have to do the text conversion automatically, any
pointers on this?
Does anyone have any views on the best way to go about this or views on
holding and full searches again word files
Many thanks for any help you can give
Jim Florence
Text means faster indexing times, but not by much. With text you can query
the columns and read the contents, you can't do this with binary.
Search SQL is equally as flexible with text and binary. You can only do a
like against text or char columns.
To do the conversion use filtdump -b (you can get this from the Platform
SDK), or you can use ole-automation against the word documents to extract
the text paragraph by paragraph.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim Florence" <florence_james@.hotmail.com> wrote in message
news:5IKdncUNWPRLowLeRVnyiQ@.pipex.net...
> Hi,
> I'm putting together a system and one of the requirements is to have a
> searchable CV function.
> I've got all the code to load the files on to the image fields, I've
> indexed
> and got it kinda working.
> Before I go to far down the road what is your opinion on having txt files
> instead of doc files held on the table search? The SQL seems to be more
> flexible on
> searches rather than on the binary files and the index files themselves
> are
> smaller
> .i.e. when I tried a like clause it told me this would only work against a
> varchar field
> (i'm thinking this may be a schoolboy error so forgive me)
> My main concern is I'd have to do the text conversion automatically, any
> pointers on this?
> Does anyone have any views on the best way to go about this or views on
> holding and full searches again word files
> Many thanks for any help you can give
> Jim Florence
>
>
|||Hilary,
Many thanks for that, very, very useful. I've started playing with the
indexing service as well to try and find a best fit.
I'll give this a go
many thanks for such a quick and informative response
Regards
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%236F1hKaAGHA.216@.TK2MSFTNGP15.phx.gbl...
> Text means faster indexing times, but not by much. With text you can query
> the columns and read the contents, you can't do this with binary.
> Search SQL is equally as flexible with text and binary. You can only do a
> like against text or char columns.
> To do the conversion use filtdump -b (you can get this from the Platform
> SDK), or you can use ole-automation against the word documents to extract
> the text paragraph by paragraph.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jim Florence" <florence_james@.hotmail.com> wrote in message
> news:5IKdncUNWPRLowLeRVnyiQ@.pipex.net...
>

Difference between db_datareader and db_denydatawriter

Hi! I was just wondering that db_datareader and db_denydatawriter roles
look like doing the same function. I have read some threads on this
topic but they are not very clear. Can I have any comments on the
difference? I will be really obliged.
Thanks in advance
Kind regards,db_denydatawriter explicitly remove the ability for an id to modify any user
data within the database without respect to its ability to read. Read
privileges have to be managed elsewhere.
db_datareader on the other hand allows an id to read all user data within
the database without respect to its ability to modify the data. Data
modification privileges are managed elsewhere.
--Brian
(Please reply to the newsgroups only.)
<sajid_yusuf@.yahoo.com> wrote in message
news:1126004382.797483.258770@.g14g2000cwa.googlegroups.com...
> Hi! I was just wondering that db_datareader and db_denydatawriter roles
> look like doing the same function. I have read some threads on this
> topic but they are not very clear. Can I have any comments on the
> difference? I will be really obliged.
> Thanks in advance
> Kind regards,
>|||db_datareader allows reading data
db_denydatawriter explicitly denies updates, deletes.
User permissions are cumulative with deny taking precedence.
Explicitly denying permissions will prevent the user from
gaining permissions based on their membership in a group or
role (other than sysadmin which can't be denied anything) or
other explicit grants.
Some people put users in both roles to ensure that they can
only read data.
-Sue
On 6 Sep 2005 03:59:42 -0700, sajid_yusuf@.yahoo.com wrote:

>Hi! I was just wondering that db_datareader and db_denydatawriter roles
>look like doing the same function. I have read some threads on this
>topic but they are not very clear. Can I have any comments on the
>difference? I will be really obliged.
>Thanks in advance
>Kind regards,

difference between dates

Hi,
I have two problems.

1)I'm using datediff function to count difference in minutes between
two dates - and it's working fine.
But I would like to ask if there is any function (or maybe U could tell
me how to do it) that is able to count difference between two dates in
minutes, including only days from Monday to Friday and hours from 9:00
am to 5:15 pm.

2)My second question reference to the first one. Let's suppose that
I've managed to count correctly difference between two dates. But for
example: 120332 minutes is not telling me a lot. So I would like to
show this measure in this format DD:HH:MM
(DaysDays:HoursHours:MinutesMinutes) - aggregation function for this
measure is SUM.
I was trying to use MDX language to change 120332 into DD:HH:MM and
show this result in report but it didn't work - I don't know how
to do it correctly. Or maybe U have a better idea how I should do it.

Can You help me?

Here's an approach to the 2nd problem - that of formatting minutes as DD:HH:MM

>>

With

Member [Measures].[TimeInMins] as

120332, FORMAT_STRING = '#,#'

Member [Measures].[TimeDD] as

Format(Int([Measures].[TimeInMins]/1440),

"#:")

Member [Measures].[TimeHHMM] as

Format(TimeSerial(0, [Measures].[TimeInMins] -

(Int([Measures].[TimeInMins]/1440) * 1440), 0),

"HH:mm")

Member [Measures].[TimeFormatted] as

[Measures].[TimeDD] + [Measures].[TimeHHMM]

select {[Measures].[TimeInMins],

[Measures].[TimeFormatted]} on 0

from [Adventure Works]

TimeInMins TimeFormatted
120,332 83:13:32

>>

This article discusses how various values of FORMAT_STRING work:

http://windowssdk.msdn.microsoft.com/en-us/library/ms718137.aspx

>>

Contents of FORMAT_STRING

The FORMAT_STRING property contains a format string that was used to generate the FORMATTED_VALUE property. The contents of FORMAT_STRING vary, depending on the data type of the value.

...

>>

|||Thanks for help!!!
Here is code of working function (in sql server 2005):

MEMBER Measures.Time AS
right("0" + CStr(Int((Int(ET/60))/24)), 2)+ ":"
+ right("0" + CStr(Int(ET/60)-(Int((Int(ET/60))/24))*24), 2)+ ":"
+ right("0" + CStr(ET - (Int((Int(ET/60))/24)*24 + Int(Int(ET/60)-(Int((Int(ET/60))/24))*24))*60 ),2)

And to the first problem i wrote a TSQL function (maybe it isn't the best way - but for my problem is ok ;) )

DECLARE kursor CURSOR FOR
SELECT Case_number_, Arrival_Time, Closed_Time, datediff(day,Arrival_Time,Closed_Time) as roznica FROM data

DECLARE @.Case_number nvarchar(15)
DECLARE @.Arrival_Time datetime
DECLARE @.Closed_Time datetime
DECLARE @.Arrival_Time_help datetime
DECLARE @.Time_begin nvarchar(6)
DECLARE @.Time_end nvarchar(6)
DECLARE @.number_days int
DECLARE @.counter int
DECLARE @.no_weekend int
DECLARE @.dzien nvarchar(15)
DECLARE @.Resolved_Time int
DECLARE @.help_value int

OPEN kursor

FETCH NEXT FROM kursor INTO @.Case_number, @.Arrival_Time, @.Closed_Time, @.number_days

WHILE (@.@.FETCH_STATUS=0)
BEGIN

SET @.number_days =@.number_days +1
SET @.counter=@.number_days
SET @.Arrival_Time_help=@.Arrival_time
WHILE (@.counter>0)
BEGIN
SET @.dzien=datename(weekday,@.Arrival_Time_help)
if(@.dzien='Saturday' or @.dzien='Sunday')
BEGIN
SET @.number_days=@.number_days-1
END
SET @.Arrival_Time_help=DATEADD(day,1,@.Arrival_Time_help)
SET @.counter= @.counter-1
END

SET @.Resolved_Time=0
if(@.number_days=1)
SET @.Resolved_Time=datediff(mi,@.Arrival_Time,@.Closed_Time)
else
BEGIN
SET @.Time_begin=datename(hh,@.arrival_time)+':'+datename(mi,@.arrival_time)
SET @.Time_end='17:15'
SET @.help_value=datediff(mi,@.time_begin, @.time_end)
if(@.help_value>0)
SET @.Resolved_Time=@.help_value

SET @.Time_begin='9:00'
SET @.Time_end=datename(hh,@.Closed_time)+':'+datename(mi,@.Closed_time)
SET @.help_value=datediff(mi,@.time_begin, @.time_end)
if(@.help_value>0)

SET @.Resolved_Time=@.Resolved_Time+@.help_value
END

if(@.number_days>2)
BEGIN
SET @.Resolved_Time=@.Resolved_Time+(@.number_days-2)*495
END

UPDATE data set
Time_Period = @.Resolved_Time
WHERE Case_number_=@.Case_number

FETCH NEXT FROM kursor INTO @.Case_number, @.Arrival_Time, @.Closed_Time, @.number_days

END
CLOSE kursor
DEALLOCATE kursor

difference between dates

Hi,
I have two problems.

1)I'm using datediff function to count difference in minutes between
two dates - and it's working fine.
But I would like to ask if there is any function (or maybe U could tell
me how to do it) that is able to count difference between two dates in
minutes, including only days from Monday to Friday and hours from 9:00
am to 5:15 pm.

2)My second question reference to the first one. Let's suppose that
I've managed to count correctly difference between two dates. But for
example: 120332 minutes is not telling me a lot. So I would like to
show this measure in this format DD:HH:MM
(DaysDays:HoursHours:MinutesMinutes) - aggregation function for this
measure is SUM.
I was trying to use MDX language to change 120332 into DD:HH:MM and
show this result in report but it didn't work - I don't know how
to do it correctly. Or maybe U have a better idea how I should do it.

Can You help me?

Here's an approach to the 2nd problem - that of formatting minutes as DD:HH:MM

>>

With

Member [Measures].[TimeInMins] as

120332, FORMAT_STRING = '#,#'

Member [Measures].[TimeDD] as

Format(Int([Measures].[TimeInMins]/1440),

"#:")

Member [Measures].[TimeHHMM] as

Format(TimeSerial(0, [Measures].[TimeInMins] -

(Int([Measures].[TimeInMins]/1440) * 1440), 0),

"HH:mm")

Member [Measures].[TimeFormatted] as

[Measures].[TimeDD] + [Measures].[TimeHHMM]

select {[Measures].[TimeInMins],

[Measures].[TimeFormatted]} on 0

from [Adventure Works]

TimeInMins TimeFormatted
120,332 83:13:32

>>

This article discusses how various values of FORMAT_STRING work:

http://windowssdk.msdn.microsoft.com/en-us/library/ms718137.aspx

>>

Contents of FORMAT_STRING

The FORMAT_STRING property contains a format string that was used to generate the FORMATTED_VALUE property. The contents of FORMAT_STRING vary, depending on the data type of the value.

...

>>

|||Thanks for help!!!
Here is code of working function (in sql server 2005):

MEMBER Measures.Time AS
right("0" + CStr(Int((Int(ET/60))/24)), 2)+ ":"
+ right("0" + CStr(Int(ET/60)-(Int((Int(ET/60))/24))*24), 2)+ ":"
+ right("0" + CStr(ET - (Int((Int(ET/60))/24)*24 + Int(Int(ET/60)-(Int((Int(ET/60))/24))*24))*60 ),2)

And to the first problem i wrote a TSQL function (maybe it isn't the best way - but for my problem is ok ;) )

DECLARE kursor CURSOR FOR
SELECT Case_number_, Arrival_Time, Closed_Time, datediff(day,Arrival_Time,Closed_Time) as roznica FROM data

DECLARE @.Case_number nvarchar(15)
DECLARE @.Arrival_Time datetime
DECLARE @.Closed_Time datetime
DECLARE @.Arrival_Time_help datetime
DECLARE @.Time_begin nvarchar(6)
DECLARE @.Time_end nvarchar(6)
DECLARE @.number_days int
DECLARE @.counter int
DECLARE @.no_weekend int
DECLARE @.dzien nvarchar(15)
DECLARE @.Resolved_Time int
DECLARE @.help_value int

OPEN kursor

FETCH NEXT FROM kursor INTO @.Case_number, @.Arrival_Time, @.Closed_Time, @.number_days

WHILE (@.@.FETCH_STATUS=0)
BEGIN

SET @.number_days =@.number_days +1
SET @.counter=@.number_days
SET @.Arrival_Time_help=@.Arrival_time
WHILE (@.counter>0)
BEGIN
SET @.dzien=datename(weekday,@.Arrival_Time_help)
if(@.dzien='Saturday' or @.dzien='Sunday')
BEGIN
SET @.number_days=@.number_days-1
END
SET @.Arrival_Time_help=DATEADD(day,1,@.Arrival_Time_help)
SET @.counter= @.counter-1
END

SET @.Resolved_Time=0
if(@.number_days=1)
SET @.Resolved_Time=datediff(mi,@.Arrival_Time,@.Closed_Time)
else
BEGIN
SET @.Time_begin=datename(hh,@.arrival_time)+':'+datename(mi,@.arrival_time)
SET @.Time_end='17:15'
SET @.help_value=datediff(mi,@.time_begin, @.time_end)
if(@.help_value>0)
SET @.Resolved_Time=@.help_value

SET @.Time_begin='9:00'
SET @.Time_end=datename(hh,@.Closed_time)+':'+datename(mi,@.Closed_time)
SET @.help_value=datediff(mi,@.time_begin, @.time_end)
if(@.help_value>0)

SET @.Resolved_Time=@.Resolved_Time+@.help_value
END

if(@.number_days>2)
BEGIN
SET @.Resolved_Time=@.Resolved_Time+(@.number_days-2)*495
END

UPDATE data set
Time_Period = @.Resolved_Time
WHERE Case_number_=@.Case_number

FETCH NEXT FROM kursor INTO @.Case_number, @.Arrival_Time, @.Closed_Time, @.number_days

END
CLOSE kursor
DEALLOCATE kursor

Difference between [Stored Procedure / Trigger & Function]

hi

can any give me what are the major difference between [Stored Procedure / Trigger & Function] with example

kinds regards

There's no need for examples. The distinctions are very bold.

A stored procedure generally performs query's on one or more tables. i.e. UPDATE, INSERT, DELETE

A trigger is tied to one table and fires based on the type of trigger you use.

A function generally doesn't touch any table but simple performs a task such as complex calculations or string munipulation and returns a value.

Notice I use the term generally and I mean it very loosely. All of these can overlap but the choice of use is based on the desired task.

Adamus

sql

Diff. b/w Stored Procedures and Function?

Diff. b/w Stored Procedures and Function??

When any of them is appropriate to use?

Here|||

What is the difference between a Sub and a Function in VB?

Determining permissions through Stored Procedures

Is it possible in SQL 2005 to determine what rights a user has to a given DB
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx