DIFFERENCES BETWEEN STORED PROCEDURE AND FUNCTION IN SQL SERVER

1. You have to execute a stored procedure and you have select from the function.

2. You cannot use a stored procedure in a where clause.You can use a function in a where clause.

3. They don’t return a value by default.They only returns an integer value saying whether the sp is executed sucessfully or not.
   Functions by default return a value.The return value can be a single value or a table.

4. If you want to return any value use output parameters in stored procedures.NO concept of output parameters in functions as they by default returns a value.

5. Stored procedures are better for performance as compared to function because they retain the execution plan and reuses it for the next execution.You cannot achieve this in functions.

6. Functions are used basically used for computations but stored procedures are used for performing business logic.

7. Functions do not return ntext, image and timestamp data types as return type but stored procedure returns all types of data.

8. Only functions and extended stored procedures can be executed from within a function.You can execute a function from a stored procedure.

9. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function cannot be used for this.

10.XML and output parameters cannot be passed to functions whereas it can be with stored procedures.

11.Transactions related statements can be used in Stored procedures but functions.

12.Stored procedure can call another function and a stored proc.Function can only call another function or extended stored procedure.Function cannot call a stored procedure.

13.Functions cannot run independently. They have to be part of a SQL statement.Stored procedures can run independently.

14.You cannot create a temp table in a function but you can create a temp table in a stored procedure.

15.Functions wont allow insert , update , delete from tables. You can perform these operations in a stored procedure.

16.Stored procedures can create table variable and cannot return the table variable.But the function can create, update and delete the table variable. It can return table variable.

17.Stored procedure can have the dynamic sql statement and which can be executed using sp_execute sql statement.But the function cannot execute the sp_executesql statement.

18.Stored procedure allows getdate() or other non-deterministic functions can be allowed.The function will not allow using non-deterministic functions like GETDATE().

Correct me if any of the above point requires modifications.

Pawan Kumar
Pawankkmr@hotmail.com

CHANGE TABLE NAME / INDEX NAME / COUMN NAME USING A QUERY IN SQL SERVER 2005

1.sp_rename changes the name of a user-created object in the current database.

2.Object can be a table, index, column.

Examples are given below.

RENAMING A TABLE

EXEC sp_rename ‘Employee’ , ‘NewEmployee’
GO

OUTPUT
______

Caution: Changing any part of an object name could break scripts and stored procedures.

–Check the new table

SELECT * FROM NewEmployee

OUTPUT
________

2    Gaurav    80000
3    Saurabh    20000
5    Neelam    18000
6    Saurabh    24000

_________________________________________________________________________________________
_________________________________________________________________________________________

RENAMING A COLUMN

EXEC sp_rename ‘NewEmployee.EMPNAME’, ‘NEWEMPNAME’, ‘COLUMN’;

OUTPUT
______

Caution: Changing any part of an object name could break scripts and stored procedures.

–Check the new column name in the table definition

SELECT * FROM NewEmployee

Sp_help NewEmployee

OUTPUT
______

id             int    no    4    10       0        no    (n/a)    (n/a)    NULL
NEWEMPNAME    varchar    no    100                      yes    no    yes    SQL_Latin1_General_CP1_CI_AS
SALARY       decimal    no    9    18       0        yes    (n/a)    (n/a)    NULL

_________________________________________________________________________________________
_________________________________________________________________________________________

RENAMING AN INDEX

CREATE an index first
CREATE NONCLUSTERED INDEX MyIndex ON NewEmployee (NEWEMPNAME ASC );
GO

–Now change the name of the index

EXEC sp_rename ‘NewEmployee.MyIndex’, ‘MynewIndex’, ‘INDEX’;
GO

OUTPUT
________

Caution: Changing any part of an object name could break scripts and stored procedures.

Sp_help NewEmployee

OUTPUT for indexes only
_____

MynewIndex    nonclustered located on PRIMARY    NEWEMPNAME
PK__EMPLOYEE__2DA7A64D    clustered, unique, primary key located on PRIMARY    id

_________________________________________________________________________________________
_________________________________________________________________________________________

Pawan Kumar
Pawankkmr@hotmail.com

EXAMPLE OF RECURSIVE Common Type Expression ( CTE )

A Common Table Expression (CTE) is a temporary, named result set.
Once created, a CTE can then be used in subsequent statements in the
same SQL batch.
This provides two benefits. First, it allows queries to be written in a
more clear and concise manner. Second, because CTEs can refer to
themselves,
they allow the creation of recursive queries. In previous versions of
SQL Server, stored procedures could call themselves, but SQL statements
could not.
CTEs therefore represent a new and powerful addition to the T-SQL language.

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query.
We can use CTE as Recursive CTE (Common Table Expression). Following
example shows how we can use recursive CTE. Here we are creating list
of numbers.

WITH NumberList(Num) as
(
SELECT 1
UNION ALL
SELECT Num + 1
FROM NumberList
WHERE Num < 1000
)
SELECT TOP 50 * FROM NumberList

Pawan Kumar
Pawankkmr@hotmail.com

FIND CREATED AND LAST MODIFIED DATE OF A STORED PROCEDURE

You can find the details about these things in sys.objects table.

It is very helpful in finding when was the last time the stored procedure is modified.

Please see the example below.

USE HRMS;

SELECT NAME AS ‘Stored Procedure Name’,
CREATE_DATE ,
Modify_Date ,
type_desc
from sys.objects
WHERE TYPE = ‘P’ AND is_ms_shipped = 0 AND NAME = ‘aesp_AddLeaveBalance’

Also visit Select * from sys.objects for further details.

Pawan Kumar
Pawankkmr@hotmail.com

IN CLAUSE FOR MULTIPLE COLUMNS

If the expression is a collection of mutiple columns or literals ,The solution is to combination is converting all items to varchar and concatinate them into
one string expression.Concatination we have to do this on both side (OUTER and INNER Query ) of the IN operator.

Examples for the same are given below.

SELECT * FROM EMPLOYEE WHERE CAST(EMPNAME+CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200))
 IN
( SELECT CAST(EMPNAME+CAST(SALARY AS VARCHAR(200)) AS VARCHAR(200)) FROM EMPLOYEE )

SELECT * FROM EMPLOYEE WHERE CAST(EMPNAME+ SPACE(1) + CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200))
 IN
( SELECT CAST(EMPNAME+ SPACE(1) +CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200)) FROM EMPLOYEE )

SELECT * FROM EMPLOYEE WHERE CAST(EMPNAME+ ‘|’ +CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200))
 IN
( SELECT CAST(EMPNAME+ ‘|’ +CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200)) FROM EMPLOYEE )

If you have any other solution , please let me know. We all are here to learn.

Pawan Kumar
Pawankkmr@hotmail.com