Get Current Language of SQL Server

Get Current Language of SQL Server

Select @@LANGUAGE as Language

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".