Solved

Rewriting Stored Procedures as Functions

Posted on 2010-08-13
2
301 Views
Last Modified: 2012-05-10
expersts,  am reading about function. I don understand this
     Its primary purpose is to build intermediate results
plase, can you give me transact sql
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 7

Accepted Solution

by:
bouscal earned 63 total points
ID: 33432002
A function will accept only an input parameter and has only one return value.

A stored procedure will accept input and output parameters and can have multiple output values.


The code below is from MSDN http://msdn.microsoft.com/en-us/library/ms187926.aspx

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ;number ] 

    [ { @parameter [ type_schema_name. ] data_type } 

        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]

    ] [ ,...n ] 

[ WITH <procedure_option> [ ,...n ] ]

[ FOR REPLICATION ] 

AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

[;]



<procedure_option> ::= 

    [ ENCRYPTION ]

    [ RECOMPILE ]

    [ EXECUTE AS Clause ]

Open in new window

0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 62 total points
ID: 33432165
>It's primary function is to build intermediate results

No, I don't think so...
A function is just some stored SQL that returns a single value.  For example, GETDATE() returns the current date and SYSTEM_USER() return the current login. Many functions accept arguments, for example UPPER(string) accepts a string argument and returns the same string converted to upper case.  

SQL server comes with many built-in functions, but also supports user defined functions (UDF's), so you can write your own.  You write those using TSQL  (or any CLR language).  For example, here's a simple function that uses the CAST AND DATEADD builtin functions to return tomorrows date.

Create Function dbo.GetTomorrow ()
Returns date
as
begin
return (dateadd(d,1,cast(cast(getdate() as int) as datetime)))
end
go

select getdate() as today, dbo.getTomorrow() as tomorrow

Generally speaking, stored procedures cannot be converted to functions because functions have many limitations and they return results a different way.  












0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now