Differentiate between a Stored Procedure and a User Defined Function

  • Stored Procedures can or cannot return values but functions have to return a value which is mandatory.
  • Stored Procedures can have input/output parameters but functions only have input parameters.
  • Stored Procedures allows execution of SELECT as well as DML statements but functions allows execution of SELECT statement only.
  • Stored Procedures cannot be called from functions but functions can be called from Stored Procedures.
  • Stored Procedures can handle exceptions by using try-catch block which cannot be used in functions.
  • Transactions can be implemented in stored procedures, but not in functions.
  • Stored Procedures cannot be used in a SELECT statement whereas functions can be used in a SELECT statement.
  • User Defined Functions can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, but stored procedures cannot.
  • Stored Procedures can be used with XML FOR clause but functions cannot be used with XML FOR clause.
  • Stored Procedures can create a table, but cannot return it. Functions can return a table.
  • Stored Procedures can be called independently using EXECUTE or EXEC keyword but functions are called using SELECT statements.
  • User Defined Functions that return tables can be treated as another rowset and can be used in JOINs with other tables. Inline User Defined Functions can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What is a Service in SOA?

A service in Service Oriented Architecture can be defined in various ways. Given below are the common definitions:

Services are core business logic that are protocol-independent, location-agnostic and contain no user state. They are coarse-grained (it can perform its logic and return the result in a single call). Services may be reused across diverse applications.

A Service is a process or technique by which a consumer's requirement is satisfied according to a negotiated contract which includes Service Agreement, Functions Offered and so on.

A Service is the seeking and receipt of a specific outcome of a customer across a range of interactions and touch-points over time.

A Service is basically a process with an interface that permits asynchronous communication with other services.

Basically, services are a group of methods that share a common set of requirements and functional goals. They are called by other parts that need to execute its logic, depending on the outcome (such as data, results of calculations, and so on). The functions have a clearly defined public signature which is published so other code (service clients) can use the functions in the service as a black box. The service operations are invisible — there is no direct interaction with a user and the work is executed as instructed by the given input parameters.

What is Service Oriented Architecture (SOA)?

Service Oriented Architecture (SOA) is a style of programming, an architectural approach in software development, where an application is organized in functional units of code with a given behavior called services. SOA is more than just a collection of well defined services. It’s a methodology encompassing policies, procedures, and best practices that allow the services to be provided and consumed effectively. SOA is a way to build distributed systems where autonomous logic is called using loosely coupled messages via a well - defined interface.

A service is an autonomous (business) system that accepts one or more requests and returns one or more responses via a set of published and well defined interfaces. Each of these individual services can be modified independently of other services to help respond to the ever-evolving market conditions of a business.

Unlike traditional, tightly coupled architectures, SOA implements a set of loosely coupled services that collectively achieve the desired results; in addition, since the underlying implementation details are hidden (service operations are invisible) from the consumer, changes to the implementation won’t affect the service, as long as the contract does not change. This allows systems based on SOA to respond more quickly and cost effectively for the business.

SOA provides an approach based on open standards and generic messages that aren’t specific to any platform or programming language. As a result, a high degree of loose coupling and interoperability across platforms and technologies can be achieved.

Implementation of SOA architecture is needed to solve problems that object orientation alone cannot solve for very large systems with integration between multiple parts.

Service Providers are components that execute some business logic based on predetermined inputs and outputs, and expose this functionality through an SOA implementation.
A Consumer, on the other hand, is a set of components interested in using one or more of the services offered by the providers.
A Repository contains a description of the services, where the providers register their services and consumers find what services are provided.

Fundamentals of SOA

An Introduction to Service-Oriented Architecture

What is Service Oriented Architecture?

What is WCF

Windows Communication Foundation (WCF – formerly known as Indigo) provides a uniform way of developing distributed applications by providing a service-oriented programming model. WCF is the technology in .NET which is used to build Service Oriented Applications (SAO), to exchange messages in various communication scenarios, and to run workflows built from service operations.

WCF provides a runtime environment for services, enabling exposure of Common Language Runtime (CLR) types as services and consumption of other services as CLR types. WCF is Microsoft’s implementation of a set of industry standards defining service interactions, type conversions, marshaling, and the management of various protocols. Consequently, WCF provides interoperability between services.

WCF has an elegant extensibility model which can be used to enrich the basic offering. Most of the WCF functionality is included in a single assembly called System.ServiceModel.dll, located in the System.ServiceModel namespace.

An Introduction to WCF

WCF Intro Demo 1

WCF Intro Demo 2

WCF Intro Demo 3

WCF Intro Demo 4