Stored Procedure vs Function in SQL Server

Stored Procedure:- Stored procedure in sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task. 

Function:- Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

In the same way there are many differences between a stored procedure and function as below ,

Stored Procedure	Function
Can return output parameters	Can’t return parameters
Can be used to read and modify data	Can only read data, cannot modify the database
To run an SP Execute or Exec is used, cannot be used with SELECT statement	Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER)
Cannot JOIN a SP in a SELECT statement	Can JOIN a UDF in a SELECT statement
Can use Table Variables as well as Temporary Tables inside an SP	Cannot use a Temporary Table, only Table Variables can be used
Can create and use Dynamic SQL	Cannot use a Dynamic SQL inside a UDF
Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP	Cannot use transactions inside a UDF
Support RAISEERROR and @@ERROR	No support for error
Can use used with XML FOR clause	Cannot be used with XML FOR clause
Can use a UDF inside a SP in SELECT statement	Cannot execute an SP inside a UDF
Cannot be used to create constraints while creating a table	Can be used to create Constraints while creating a table
Can execute all kinds of functions, be it deterministic or non-deterministic	Cannot execute some non-deterministic built-in functions, like GETDATE()
Returns multiple result sets	Can’t returns multiple result sets
Syntax: CREATE PROCEDURE usp_MyProcedure AS BEGIN -- SELECT * FROM <Table> END GO	Syntax: CREATE FUNCTION dbo.FunctionName     (parameter_name data_type [=default] [, n]) RETURNS scalar_data_type [WITH function_option] AS BEGIN -- Function_body -- RETURN scalar_expression END

 

Keep learning,,,,,,

Split String in SQL Server

How to split a string based on the delimiter(any character), this is the requirement we get when we are working with sql server.
In this article i am going to show you, how to that.

 Apart from split the string , i am showing how many records(Rows) we get by using RowID.
 I am writing one User Defined Function, because we can use it any where.


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitString](@String varchar(8000), @Delimiter char(1))      
returns @temptable TABLE (items varchar(8000),RowId INT)      
as      
begin      
    declare @idx int      
    declare @slice varchar(8000)      
    DECLARE @RowID INT
    set @RowID=0
    select @idx = 1      
        if len(@String)<1 or @String is null  return      
     
    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      
         
        if(len(@slice)>0)
        set @RowID= @RowID+1
            insert into @temptable(Items,RowId) values(@slice,@RowID)      
 
        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return      
end

Now you can use the select statement to test it, by passing
string =Asp.net,C#,sql
 Delimiter=,(Comma)

SELECT  items,Rowid FROM dbo.SplitString('Asp.net,C#,sql',',') 
By using this statement you will get all.

If you want specific part from the string use where condition, as
SELECT  items, Rowid FROM dbo.SplitString('Asp.net,C#,sql',',')  where rowid=3

Happy Programming,,,,,,,

Web Services and it's History

What is Web Service?

  

Web Service is an application that is designed to interact directly with other applications over the internet. In simple sense, Web Services are means for interacting with objects over the Internet.

Web Service is

  Language Independent
     Protocol Independent
     Platform Independent
     It assumes stateless service architecture.
     Web services are application components
     Web services communicate using open protocols
     Web services are self-contained and self-describing
     Web services can be discovered using UDDI
     Web services can be used by other applications
     XML is the basis for Web services

Before that lets understand bit on how web service comes into picture.

The basic Web Services platform is XML + HTTP.

History of Web Service or How Web Service comes into existence?

As i have mention before that Web Service is used for interacting with objects over the Internet.

1. Initially Object - Oriented Language comes which allow us to interact with two object within same application.

2. Then comes Component Object Model (COM) which allows interacting two objects on the same computer, but in different applications.

3. Then comes Distributed Component Object Model (DCOM) which allows interacting two objects on different computers, but within same local network.

4. And finally the web service, which allows two object to interact internet. That is it allows interacting between two objects on different computers and even not within same local network.

Example of Web Service

       Weather Reporting: You can use Weather Reporting web service to display weather information in your personal website.

       Stock Quote: You can display latest update of Share market with Stock Quote on your web site.

         News Headline: You can display latest news update by using News Headline Web Service in your website.

         In summary you can any use any web service which is available to use. You can make your own web service and let others use it. Example you can make Free SMS Sending Service with footer with your companies advertisement, so whosoever use this service indirectly advertise your company... You can apply your ideas in N no. of ways to take advantage of it.