Tuesday, August 30, 2011

Difference between stored procedure and functions in SQL Server

0 comments Posted by Rahul Kharde at 3:51 AM


Store Procedure

Function

S.P need not be return a value

Functions MUST return a value

S.P can be called independently using exec keyword whereas

Function is called using SELECT statements.

S.P can be used for performing business logic

Functions are used for computations

Can be used EXEC inside an S.P
 

EXEC command can't be used inside a Function

S.P takes IN and OUT parameters.

Function parameters are always IN, no OUT is possible

Transaction related statement can be handled in S.P

Can't be handled in function.

XML parameters passed in S.P

Can’t pass XML Parameters

S.P can affect the state of the database by using insert, delete, update and create operations.

Functions cannot affect the state of the database which means we cannot perform insert, delete, update

Parsed and Compiled during design time itself

Compiled and executed during run time

Read More »

UNION the results of multiple stored procedures with ASP.NET

0 comments Posted by Rahul Kharde at 1:40 AM

UNION is used to combine the result from multiple SELECT statements into a single result set.

If we have a stored procedure and need to call several different times passing in different parameters each time. We would like to collect the results as a single dataset.

exec MyStoredProcedure 1
UNION
exec MyStoredProcedure 3
UNION
exec MyStoredProcedure 5

this shows an syntax above but got the error ...

Incorrect syntax near the keyword 'Union'.

You'd have to use a temp table like this.

CREATE TABLE #TempTABLE

{

COLUMN1 datatype

COLUMN2 datatype

}

INSERT #TempTABLE

exec MyStoredProcedure 1

INSERT #TempTABLE

exec MyStoredProcedure 2

INSERT #TempTABLE

exec MyStoredProcedure 3


EXAMPLE

create table users

(

userId int identity(1,1) not null,

userName varchar(50) not null,

userPass varchar(20) not null

)

insert into users(userName, userPass) values('UserName1', 'Password')

insert into users(userName, userPass) values('UserName2', 'Password')

insert into users(userName, userPass) values('UserName3', 'Password')

insert into users(userName, userPass) values('UserName4', 'Password')

insert into users(userName, userPass) values('UserName5', 'Password')

insert into users(userName, userPass) values('UserName6', 'Password')


Create the Store procedure

CREATE PROCEDURE Sp_GetUserInformation

@userId INT

AS

BEGIN

SELECT * FROM users WHERE userid=@userId

END

GO


You want to fetch the record whose userid is 1 3 5 and store it into dataset.



CREATE PROCEDURE Sp_Collectalluser

AS

BEGIN

--Create temporary table

CREATE TABLE #TEMP

(

userId INT,

userName VARCHAR(255),

userPass VARCHAR(255)

)

INSERT INTO #TEMP

exec Sp_GetUserInformation 1

INSERT INTO #TEMP

exec Sp_GetUserInformation 3

INSERT INTO #TEMP

exec Sp_GetUserInformation 5

SELECT * FROM #TEMP

END


OUTPUT

After executing the store procedure

UserId

UserName

Userpass

1

UserName1

Password

3

UserName3

Password

5

UserName5

Password


Read More »
 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top