To View a Stored Procedure

To View a Stored Procedure

Syntax:

To view the definition of a stored procedure:

sp_helptext procedure_name

To view the information about a stored procedure:

sp_help procedure_name

To view the dependencies of the stored procedure:

sp_depends procedure_name

procedure_nameIs the name of the stored procedure.

SQL Server allows us to view the definition, information, and dependencies of a stored procedure.

Examples :

Code:

sp_helptext spNewAvgGrade;

Output:

CREATE PROCEDURE spGetAvgGrade (@Course INTEGER)

AS

SELECT AVG(Std_Grade) as AverageGrade FROM Students

WHERE Std_Course = @Course

Explanation:

In the above example, the sp_helptext displays the text of the spNewAvgGrade stored procedure.

Language(s): MS SQL Server

Code:

sp_help spNewAvgGrade;

Output:

Name    Owner  Type      Created_datetime

spNewAvgGrade             dbo        stored procedure            2003-09-14 23:53:13.810

Parameter_name            Type      Length  Prec       Scale     Param_order

@Course             int           4              10           0              1

Explanation:

This example displays information about the stored procedure.

Language(s): MS SQL Server

Code:

sp_depends spNewAvgGrade;

Output:

In the current database, the specified object references the following:

Name    Type      Updated              Selected              Column

dbo.Students    user table            no           no           Std_Course

dbo.Students    user table            no           no           Std_Grade

Explanation:

This example shows the dependencies of the stored procedure.

Language(s): MS SQL Server

Using @@ROWCOUNT ( System Variable)

Using @@ROWCOUNT ( System Variable)

@@ROWCOUNT  : It is a very useful system variable that returns the number of rows read/affected by the previous statement. It’s frequently used in loops and in error handling.

Remember that it returns the number of rows affected by the previous statement. Every statement. Look at this piece of code:

Example :

BEGIN TRY

    SELECT TOP 10 * FROM sys.objects

END TRY

BEGIN CATCH

    SELECT TOP 5 * FROM sys.objects

END CATCH

SELECT @@ROWCOUNT

You might think that the result of @@ROWCOUNT would be 10 since that’s the previous command that was run. You might also think it would be 5 since that’s the last command in the code, right? However you would be wrong on both counts. The TRY/CATCH block is actually the last command, so the result of @@ROWCOUNT is 0.

SELECT TOP 10 * FROM sys.objects

Select @@ROWCOUNT

In this case, the result of @@ROWCOUNT is 10.

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View.

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View.

System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

sp_helptext can be passed any SQL Server object that contains code, for example stored procedures, views, functions, triggers etc. It then returns the code for that object.

Syntax :

sp_helptext @objname 

sp_helptext Stored Procedure or functions or Views or triggers

Modifying a Stored Procedure

Modifying a Stored Procedure

If you need to modify an existing stored procedure, simply replace the CREATE with ALTER. Let's add a space in between "Latest" and "Tasks" (i.e. make it "Latest Tasks") and add the Description field:

ALTER PROCEDURE LatestTasks @Count int AS

SET ROWCOUNT @Count

SELECT TaskName AS "Latest Tasks", Description, DateCreated

FROM Tasks

ORDER BY DateCreated DESC

Execute a Stored Procedure

Executing a Stored Procedure

Now that if you've created your stored procedure, any time you want to execute it, you need to call it using either EXECUTE or EXEC. If the stored procedure requires parameters you provide those after the procedure name. Like this:

EXECUTE LatestTasks

EXEC LatestTasks

EXEC LatestTasks @Count = 5

Example : In the following example, we execute the stored procedure twice at the same time. The first time we call it, we pass in a @Count of 3. The second time we pass in a value of 5.

The screenshot shows that by passing the paramater (and a value), the stored procedure returns results based on the value that we provide. The top result set returns 3 rows because we passed in a value of 3. The second result set returns 5 rows because we provided a value of 5:

Create a Stored Procedure

Create a Stored Procedure

To create a stored procedure, you need to use the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure. If your stored procedure is going to accept parameters, they need to be included after the name.

CREATE PROCEDURE myStoredProcedure AS

...

OR

CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS

...

Example : The following code creates a stored procedure called "LatestTasks". It accepts a parameter called @Count. Whenever you call this stored procedure, you pass the @Count parameter along with a number, which determines how many rows you want returned. Here's the code:

CREATE PROCEDURE LatestTasks @Count int AS

SET ROWCOUNT @Count

SELECT TaskName AS LatestTasks, DateCreated

FROM Tasks

ORDER BY DateCreated DESC

Run this code in the SQL Server Management Studio and you'll see that it gets created under the Stored Procedures node as "LatestTasks".

Benefits of Stored Procedures

Benefits of Stored Procedures

1. Modular programming : You can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).
2. Performance :Stored procedures provide faster code execution and reduce network traffic.

• Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.
• Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.

3. Security : Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Adding a Column to a Table in SQL Server

Adding a Column to a Table in SQL Server

To add a new column to a table, follow this formula:

ALTER TABLE TableName

ADD ColumnName Properties

Here is an example:

ALTER TABLE StaffMembers

ADD Address varchar(100) NULL

When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, and that allows empty entries, would be added to the table named StaffMembers.

Different ways to replace NULL in SQL SERVER

Different ways to replace NULL in SQL SERVER

Consider the Employees table below.

Write a LEFT OUTER SELF JOIN query, which produced the following output.

In the output, MANAGER column, for Todd's rows is NULL. I want to replace the NULL value, with 'No Manager'

Replacing NULL value using ISNULL() function: We are passing 2 parameters to IsNULL() function. If M.Name returns NULL, then 'No Manager' string is used as the replacement value.

SELECT E.Name as Employee, ISNULL(M.Name,'No Manager') as Manager

FROM tblEmployee E

LEFT JOIN tblEmployee M

ON E.ManagerID = M.EmployeeID

Replacing NULL value using CASE Statement:

SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN 'No Manager'

   ELSE M.Name END as Manager

FROM  tblEmployee E

LEFT JOIN tblEmployee M

ON   E.ManagerID = M.EmployeeID

Replacing NULL value using COALESCE() function: COALESCE() function, returns the first NON NULL value :

SELECT E.Name as Employee, COALESCE(M.Name, 'No Manager') as Manager

FROM tblEmployee E

LEFT JOIN tblEmployee M

ON E.ManagerID = M.EmployeeID

SQL Server PRINT Keyword

SQL Server PRINT Keyword

To display something in plain text as a result of a statement, type PRINT followed by what to display. Therefore, PRINT uses the following formula:

PRINT 'WhatToPrint'

The item to display can also be an operation or the result of an operation. If you want to display a character, a word, or a sentence, include it between single-quotes. If you want to include a single-quote in your statement, double it;

How to convert varchar data type column to int in SQL Server while querying?

How to convert varchar data type column to int in SQL Server while querying?

There are two ways to convert varchar data type column to int while writing query statements.

1. CAST
2. CONVERT

Examples :

CONVERT(int,discountfee)

CAST(discountfee as int)

Here discountfee is column name and int declared as data type for column varchar data type discountfee.

SQL Server NULLIF function

SQL Server NULLIF function

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows:

NULLIF ("expression 1", "expressions 2")

It is the same as the following CASE statement:

SELECT CASE ("column_name")

  WHEN "expression 1 = expression 2 " THEN "NULL"

  [ELSE "expression 1"]

  END

FROM "table_name";

For example, let's say we have a table that tracks actual sales and sales goal as below:

Table Sales_Data

Store_Name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:

SELECT Store_Name, NULLIF (Actual, Goal) FROM Sales_Data;

Result:

Store_Name      NULLIF (Actual, Goal)

Store A                                 NULL

Store B                                     40

Store C                                     25

SQL Server USE Keyword

SQL Server USE Keyword

The USE keyword is used to select a database in SQL Server. The syntax is as follows:

USE "database_name";

For example, if you want to connect to a database called "World", you can type in the following:

USE World;

SQL Server: TOP WITH TIES

SQL Server: TOP WITH TIES

TOP clause is commonly used to get top required rows from a result set. Beauty of this clause is that it can be used with WITH TIES clause, to retrieve all similar rows to base result set.

Syntax :

[

      TOP (expression) [PERCENT]

      [ WITH TIES ]

]

Arguments:

expression: is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT: indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES: specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example:

DECLARE @TEST TABLE(

ID int, Amount int)

INSERT INTO @TEST VALUES

(1,10),(2,30),(3,60),

(4,50),(5,50),(6,70),

(7,20),(8,70),(9,70),

(10,44),(11,80),(12,90)

Below is the result-set using TOP without and with clause WITH TIES.

Shadow Copy In SQL Server Without Using Create Command

Shadow Copy In SQL Server Without Using Create Command

Various methods of copying a table with a new name using a select statement in SQL Server. It takes a lots of time to create new empty table and defining all field names again. Various methods are explained below.

Creating Table in SQL Server

create table StudentDetails

(

roll_num int,

F_Name varchar(15),

L_Name varchar(15),

City varchar(15)

)

Insert values in above table and table will look like below image

Method I

Select all columns into new table but this way of copying will not copy constraints and indexes. This technique will copy all the columns of the source table (StudentDetails) to the destination table (StudentDetailsCopy).

Method II

Select only the columns we want into the new table. Suppose we want to copy roll_num, F_Name, City columns from StudentDetails table into the StudentDetailsCopy1 table. Constraints and indexes will not be copied.

Method III

To copy the structure of the source table into another new table with new name. Constraints and indexes will not be copied.

Here, 1=2 will prevent the data from being copied from StudentDetail to the StudentDetailCopy2 table.

Using try catch in SQL Server stored procedures with example

Using try catch in SQL Server stored procedures with example

To handle exceptions in SQL Server we can use TRY…… CATCH blocks. To use TRY…… CATCH blocks in stored procedure we need to write the query like as shown below

BEGIN TRY

---Write Your Code

END TRY

BEGIN CATCH

---Write Code to handle errors

END CATCH

In TRY block we will write our queries and in CATCH block we will write code to handle exceptions. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are

ERROR_LINE() - This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() - This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - This function will return state number of error message which cause to raise error.

ERROR_PROCEDURE() - This function will return name of the procedure where an error occurred.

ERROR_MESSAGE() - This function will return the complete text of the error message which cause to raise error.

Check below sample query to handle errors in stored procedure

CREATE PROCEDURE uspTryCatchTest

AS

BEGIN TRY

    SELECT 1/0

END TRY

BEGIN CATCH

    SELECT ERROR_NUMBER() AS ErrorNumber

     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState

     ,ERROR_PROCEDURE() AS ErrorProcedure

     ,ERROR_LINE() AS ErrorLine

     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

Output :

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure ErrorLine ErrorMessage

8134                  16        1                    NULL                     2 Divide by zero error encountered.

Difference between Stored Procedures and Functions

Difference between Stored Procedures and Functions

Stored Procedures	Functions
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.
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().

Difference between Stored Procedures and Views

Difference between Stored Procedures and Views

Stored Procedures	Views
Does not accepts parameters	Accept parameters
Can be used as a building block in large query.	Can not be used as a building block in large query.
Can contain only one single Select query.	Can contain several statement like if, else, loop etc.
Can not perform modification to any table.	Can perform modification to one or several tables.
Can be used (sometimes) as the target for Insert, update, delete queries.	Can not be used as the target for Insert, update, delete queries.