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