What is the difference between stored procedure and function in sql server?

Stored Procedre:

Stored Procedure is a group of sql statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance. (Read more Here)

Function:

Function is not pre-compiled object it will execute every time whenever it was called.

Difference between Stored Procedure and Function

1) Procedure can return zero or n values whereas function can return one value which is mandatory (Read more Here).

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.

3) Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.

4) Functions can be called from procedure whereas procedures cannot be called from function.

5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6) We can go for transaction management in procedure whereas we can’t go in function.

7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

What is function in sql server and uses of it?

Generally functions are used to reduce redundancy of queries. I have two tables QuestionDetails andUserDetails will be like as shown below

QuestionDetails

QuestionId	Subject	Description	CreatedBy
1	Test	simple desc	1
2	Welcome to asp	check forums site	1
3	I have a problem	ssms not working	2

UserDetails

Userid	Username	Location	Designation
1	Sureshdasari	Chennai	SSE
2	Rohinidasari	Chennai	agbsc

Now from QuestionDetails table we need to get question details with createdBy name. To getcreatedby name we can join these two tables but to improve query performance we need to avoid using joins in sql server. To get username instead of using joins we can create function like as shown below

CREATE FUNCTION fn_getusername (@userid int) RETURNS VARCHAR(64) AS BEGIN DECLARE @name VARCHAR(64) SELECT @name=UserName FROM UserDetails WHERE UserId=@userid RETURN @name END

Once we written this function now we need to write the query like as shown below to get question details with username

Select QuestionId, Subject, Description, username=dbo.fn_getusername(CreatedBy) fromQuestionDetails

http://pramadha.com/

What is stored procedure and uses of it in sql server?

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

USE AdventureWorks2008R2;

GO

CREATE PROCEDURE dbo.sp_who

AS

    SELECT FirstName, LastName FROM Person.Person;

GO

EXEC sp_who;

EXEC dbo.sp_who;

GO

DROP PROCEDURE dbo.sp_who;

GO

Advantages of using stored procedures

1. a) a) Stored procedure allows modular programming. 

You can create the procedure once, store it in the database, and call it any number of times in your program. 

1. b) b) Stored Procedure allows faster execution. 

If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times. 

1. c) c) Stored Procedure can reduce network traffic. 

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

1. d) d) Stored procedures provide better security to your data

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure’s statements directly.

In SQL we are having different types of stored procedures are there

1. a) System Stored Procedures
2. b) User Defined Stored procedures
3. c) Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables 

Ex: sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures. 

How function can return table in sql server?

Whether functions will return table as parameter or not?

For this question answer will be Yes functions will return table as parameter by using table valued parameters.  

Generally SQL Server functions will return only one parameter value if we want to return multiple values from function then we need to send multiple values in table format by using table valued functions.

Sample function which return table as parameter

CREATE FUNCTION testmultiplevalues

(

@UserId INT

)

returns table as

return

(

Select * from UserInformation WHERE UserId=@userId

)

call this fn as::

SELECT * FROM dbo.testmultiplevalues(14)

 

http://pramadha.com/

What are the acid properties in sql server?

Atomicity

It is one unit of work and does not dependent on previous and following transactions. This transaction is either fully completed or not begun at all. Any updates in the system during transaction will complete entirely. If any reason an error occurs and the transaction is unable to complete all of its steps, then the system will returned to the state where transaction was started

Consistency

Data is either committed or roll back, not “in-between” case where something has been updated and something hasn’t and it will never leave your database till transaction finished. If the transaction completes successfully, then all changes to the system will have been properly made, and the system will be in a valid state. If any error occurs in a transaction, then any changes already made will be automatically rolled back. This will return the system to its state before the transaction was started. Since the system was in a consistent state when the transaction was started, it will once again be in a consistent state.

Isolation

No transaction sees the intermediate results of the current transaction. We have two transactions both are performing the same function and running at the same time, the isolation will ensure that each transaction separate from other until both are finished.

Durability

Once transaction completed whatever the changes made to the system will be permanent even if the system crashes right after

Whenever transaction will start each will obey all the acid properties.

What is SQL Injection attack and how to prevent it?

Cursors in sql server allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. While loop also same as cursor to fetch set of data and process each row in sql server.

Here it’s very difficult to say which one is better either cursor or while loop because both will do same thing they fetch set of data and process each row at a time. You need to choose either cursor or while loop based on your requirements.

cursor

SET NOCOUNT ON

DECLARE ex_cursor CURSOR FOR SELECT OrderID,CustomerID FROM Orders

DECLARE @oid INT

DECLARE @cname NVARCHAR(50)

OPEN ex_cursor

FETCH NEXT FROM ex_cursor INTO @oid,@cname

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT  (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)

FETCH NEXT FROM ex_cursor INTO @oid,@cname

END

CLOSE ex_cursor

DEALLOCATE ex_cursor

while

DECLARE @Rows INT, @oid INT

DECLARE @cname NVARCHAR(50)

SET @Rows = 1

SET @oid = 0

WHILE @Rows > 0

BEGIN

SELECT TOP 1 @oid = OrderID, @cname = CustomerID FROM Orders WHERE OrderId >= @oid

SET @Rows = @@ROWCOUNT

PRINT  (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)

SET @oid += 1

END

both are doing same thing and taking same time to execute our script. You need to decide either while loop or cursor based on your requirement.

What is SQL Injection attack and how to prevent it?

Union operator will return unique records from tables and Union All in SQL server will return all the records from the tables including duplicate values also.

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

SELECT column1,column2 FROM table1 UNION SELECT column1,column2 FROM table2

Here one more thing we need to remember that is we can use Union Operator for the tables which is having same column names and same data types otherwise it will throw error .

If we want all the records then we need to use UNION ALL Operator.

SELECT column1,column2 FROM table1

UNION ALL

SELECT column1,column2 FROM table2

The main difference between Union and Union ALL operator is

Union operator will return distinct values but Union ALL returns all the values including duplicate values.

http://pramadha.com/

What is SQL Injection attack and how to prevent it?

SQL injection means injecting some SQLcommands in SQL statements to hack your data or delete data or change your data in tables via web page input.

enter text like “10 or 1=1”

It returns all the rows from table because our textbox input value converts query

select Name,Total=value from countrydetails where value =10 or 1=1

In above query it will check for value =10 as well as it will check for 1=1 means always true that’s the reason it will returns all the values from table this way they can inject values to change our queries and access all the values from table.

In another case if user enters value like “10; Drop TABLE countrydetails” in it will drop table from our database because our query will changed like this

select Name,Total=value from countrydetails where value =10; Drop TABLE countrydetails

To avoid these SQL injection attacks always we need to use parameterized queries like as shown below

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(“Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB”))

{

con.Open();

SqlCommand cmd = new SqlCommand(“select Name,Total=value from countrydetails where value =@value”, con);

cmd.Parameters.AddWithValue(“@value”, txtSearch.Text);

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

con.Close();

gvDetails.DataSource = dt;

gvDetails.DataBind();

}

What is cursor in sql server with example?

Cursor in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.

DECLARE cursorname CURSOR

FOR selectstatement — like SELECT OrderID,CustomerID FROM Orders

OPEN cursor

FETCH tablerow FROM cursor

Process Fetched Tablerow

CLOSE cursor

DEALLOCATE cursor

Step 1: Declare Cursor

First we need to declare cursor name that will be used in cursor execution

Step 2: Select Statement

Select statement is used to get data from table to process with cursor

Step 3: Open Cursor

This statement is used to open the cursor to process select statement result set

Step 4: Fetch Rows

Once cursor is opened, rows can be fetched from the cursor one by one

Step 5: Close Cursor

Once our process finished we need to close the cursor

Step 6: Deallocate Cursor

We need to deallocate cursor to delete cursor definition and release resources associated with the cursor

 

DECLARE ex_cursor CURSOR

FOR SELECT OrderID,CustomerID FROM Orders

DECLARE @oid INT

DECLARE @cname NVARCHAR(50)

OPEN ex_cursor

FETCH NEXT FROM ex_cursor INTO @oid,@cname

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT  (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)

FETCH NEXT FROM ex_cursor INTO @oid,@cname

END

CLOSE ex_cursor

DEALLOCATE ex_cursor

Concatenating Column Values into a Comma-Separated List

SELECT Stuff(
(
SELECT ‘, ‘ + stdm.session_code
FROM Table_mapping stdm where stdm.staff_id=’2015′ and NewSession=’AGR-4’
FOR XML PATH(”)
), 1, 2, ”) AS ‘Old Session’