SQL SERVER – Different Methods to Know Parameters of Stored Procedure

Suppose you have a stored procedure with some input parameters. There are two methods to know the list of parameters defined in a stored procedure.

Let us create the following stored procedure

CREATE PROCEDURE TEST_PROCEDURE
(
@CUST_ID INT,
@YEAR INT
)
AS
SELECT
@CUST_ID,@YEAR

Now to know the parameters used in the TEST_PROCEDURE stored procedure, you can use the following methods

1 Use SP_HELP system stored procedure

EXEC sp_HELP 'TEST_PROCEDURE'

When you execute the above method, this is the result of the second result set.

Parameter_name Type Length Prec Scale Param_order Collation
------------------ ----------- ----------- ----------- ------------------------------
@cust_id int 4 10 0 1 NULL
@year int 4 10 0 2 NULL

2 Use INFORMATION_SCHEMA.PARAMETERS system view

SELECT
PARAMETER_NAME,DATA_TYPE ,ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.PARAMETERS
WHERE
SPECIFIC_NAME='TEST_PROCEDURE'

The result is

PARAMETER_NAME DATA_TYPE ORDINAL_POSITION
------------------------ ------------ ----------------
@cust_id int 1
@year int 2

If you know any other methods feel free to post as a comment.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Is XP_CMDSHELL Enabled on the Server?

I am not a big fan of using command line utilities in general. But from time to time I do explore and play around with command line tools that make my life easier. Having said that, when working with SQL Server, I do often give out the recommendation of not trying to use the native xp_cmdshell commands with SQL Server. Even the SQL_Server_2012_Security_Best_Practice_Whitepaper talks about xp_cmdshell and recommends to avoid the same if it is possible. These are basic building blocks of security that I highly recommend my customers from time to time.

In this blog, let me show you two ways of doing the same.

TSQL Way

The easier way using standard T-SQL commands is shown below.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- Disable xp_cmdshell option now
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

This works great when you have direct access to the SQL Server box and you want to just do it on the box under question.

PowerShell Way

For the world of administrators who manager literally 10’s or 100’s of servers in a datacenter, doing a line-by-line or a server-by-server T-SQL script doesn’t work. They want something as a script. Below is a PowerShell script that will find out if the xp_cmdshell option is enabled on a server or not.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  | Out-Null;
 $srv = new-object Microsoft.SqlServer.Management.Smo.Server("MSSQLSERVER")
 if ($srv.Configuration.XPCmdShellEnabled -eq $TRUE)
 {
 Write-Host "xp_cmdshell is enabled in instance" $srv.Name
 }
 else
 {
 Write-Host "xp_cmdshell is Disabled in instance" $srv.Name
 }

I am sure this can be run in a loop for all your servers. Here the default instance MSSQLSERVER is being used. I am sure you will be able to run the same script in PowerShell ISE to know your instance status wrt xp_cmdshell. I would highly recommend you to share your experience and how you ever used such scripts in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure

There are some questions which are so old that they should not be asked in the interview and gets old. Here is one of the questions I have spotted so many times in the interview that if co-interviewer asks to the candidate, I often feel bored (well, I was caught yawning last time). Here is the question:

Question: “How do you create stored procedure? and What are the advantages of the stored procedure?”

Answer: Well, as I said, I find this question are age old, so if you search on the internet you will find many different answers. Just make sure you read the answers which are from the latest version of SQL Server as stored procedure have changed a bit since the inception of the same.

Here are few of the advantages of the stored procedure:

  • Execution plan retention and reuse
  • Query auto-parameterization
  • Encapsulation of business rules and policies
  • Application modularization
  • Sharing of application logic between applications
  • Access to database objects that is both secure and uniform
  • Consistent, safe data modification
  • Network bandwidth conservation
  • Support for automatic execution at system start-up
  • Enhanced hardware and software capabilities
  • Improved security
  • Reduced development cost and increased reliability
  • Centralized security, administration, and maintenance for common routines

You can read the original article over here.

If you want to create stored procedure you can create with following simple script:

CREATE PROCEDURE MyFirstSP
AS
SELECT
GETDATE();
GO

You can execute above stored procedure by following a script:

EXEC MyFirstSP
GO

The above stored procedure will return results as a current date time.

 spcreate Interview Question of the Week #013   Stored Procedure and Its Advantages   How to Create Stored Procedure

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – System procedures to know SQL Server Version

There are several ways to know the version of SQL Server. But did you know that there are two system procedures through which you can know the version? They are SP_SERVER_INFO and EXEC XP_MSVER

EXEC sp_SERVER_INFO

If you execute the above, you get a result set with informations about the server. The second will tell you the version number

Result is

attribute_id attribute_name attribute_value
———— ——————– —————-
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2012 – 11.0.3000.0
.
.
.
.
EXEC xp_MSVER

This is also very similar to SP_SERVER_INFO which will show you the version number (second row from the result set)

Result is

Index Name Internal_Value Character_Value
—— ——————————– ————– ——————-
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 720896 11.0.3153.0
.
.
.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What are the Different Ways to Script Procedure with T-SQL

Ever wondered how a simple task can be done multiple ways. You can script a stored procedure in many ways. You can use SSMS tool and use Generate Script option. However, if you want to do it in T-SQL, there are four ways.

Let us create this simple procedure

CREATE PROCEDURE TESTING
(@OPTION INT)
AS
IF
@OPTION=0
SELECT 1 AS NUMBER
ELSE
SELECT
100 AS NUMBER
GO

Now if you want to know the script of the above procedure, you can use the following methods

1 Use SP_HELPTEXT System Stored Procedure

EXEC sp_HELPTEXT TESTING

2 Use ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES view

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='TESTING'

3 Use OBJECT_DEFINITION function

SELECT OBJECT_DEFINITION(OBJECT_ID('TESTING'))

4 Use SYS.SYSCOMMENTS system view

SELECT TEXT FROM SYS.SYSCOMMENTS WHERE ID=OBJECT_ID('TESTING')

All the above four methods will show you the script of the procedure names TESTING.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to use Procedure sp_user_counter1 to sp_user_counter10

helpkeyboard SQL SERVER   How to use Procedure sp user counter1 to sp user counter10There are many performance counters available in SQL Server which can be used to monitor various parameters of SQL Server engine. Have you ever been into a situation where you want to see value in performance counter for a query which you have returned? Imagine a situation where you want to look at fragmentation or numbers of rows in a table over a period of time. And you want to plot the values against standard performance counters available.

SQL Server had this feature from a long time. I noticed it recently while working with one of my friend. He was showing me the impact of a number of database vs the number of threads in the AlwaysOn availability group. He showed me graph and my first question was – how did you plot the number of databases with that counter? The answer was – using a user counter. I did further research and learned something new so sharing that with you in the form of this blog.

There are ten system procedures which are available in SQL Server, which allows user to set specific counter values. They are sp_user_counter1, sp_user_counter2… till sp_user_counter10. That’s why they are called user settable counters. Below is the screenshot of Performance Monitor. (Start > Run > PerfMon)

sp user counter1 SQL SERVER   How to use Procedure sp user counter1 to sp user counter10

These counters can be populated using stored procedures. Counter Object name and stored procedure names are same. This means the values for “User Counter 1” can using sp_user_counter1, values for “User counter 2” can using sp_user_counter2 and so on.

Here is a quick example to demonstrate the usage.
-- Drop database, if already present
USE MASTER
GO
IF EXISTS (
SELECT *
FROM sys.databases
WHERE NAME = 'SQLAuthority'
)
BEGIN
ALTER DATABASE
SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE;
DROP DATABASE SQLAuthority;
END
GO
-- Create New Database
CREATE DATABASE SQLAuthority
GO
-- Create Objects Needed for Demo
USE SQLAuthority
GO
CREATE TABLE TrackMe (i INT)
GO
-- while loop to populate the data and populate the counter
SET NOCOUNT ON
DECLARE
@NumberOfRows INT=0,@NumberOfRows_2 INT=0
WHILE (@NumberOfRows<25)
BEGIN
INSERT INTO
TrackMe VALUES (1), (2), (3)
SELECT @NumberOfRows = COUNT(*) FROM TrackMe
EXECUTE sp_user_counter1 @NumberOfRows        -- 3, 6, 9, 12...
WAITFOR delay '00:00:02'
SELECT @NumberOfRows_2 = @NumberOfRows*2
EXECUTE sp_user_counter2 @NumberOfRows_2    -- 6, 12, 18, 24...
WAITFOR delay '00:00:02'
END
-- loop finished reset the counters
EXECUTE sp_user_counter1 0
EXECUTE sp_user_counter2 0

Here is the Performance counter graph where I have captured the counters.

sp user counter2 SQL SERVER   How to use Procedure sp user counter1 to sp user counter10

Let me explain little logic of the script. My script is plotting number of rows in the table as counter1 and double of that as counter2. There is a delay of 2 seconds added between each counter so that we can see steps. Once the number of rows in the table are around 50, I am ending the loop and resetting the values to zero. That’s when you are seeing a drop at the end.

If you look at the procedure, it is straight forward. Believe me, here is the code.

CREATE PROCEDURE sys.sp_user_counter1 @newvalue INT AS
DBCC
setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @newvalue)

Which means, we can also call undocumented command DBCC SETINSTANCE directly to set value for the counters.

Can you think of more innovative use of drawing value in performance monitor?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – How to Create Stored Procedure in MySQL

MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to create a stored procedure which will accept item_description and returns the total sales_amount for the enire period, you can do it as shown below

DELIMITER $$
CREATE PROCEDURE Test.usp_get_sales
(
param_item_description VARCHAR(100)
)
BEGIN
SELECT
item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
JOIN sales
WHERE item_description=param_item_description
GROUP BY item_description;
END;
$$
DELIMITER
;

Note that the creation of the stored procedure starts with setting the Delimiter $$. The default delimiter for MySQL statements are semicolon so in order to instruct the MySQL engine about the start and end of the stored procedure block, you need to use a different delimiter (which in this case $$ is used).

Now you can execute a stored procedure using CALL keyword as shown below.

Execution 1

CALL usp_get_sales('Television');

When you execute the above code, the result is

Item_description sales_amount
Television 3600.00

Execution 2

CALL usp_get_sales('laptop');

When you execute the above code, the result is

Item_description sales_amount
laptop 6800.00

Note: The parameters do not start with @ like we use in SQL Server. So in order to differentiate between the actual column name and parameter name, the name param_item_description is used. You may need to use different naming conventions as you like.

I have previously written a similar article here: MySQL – How to Create Stored Procedure.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

The above query will return results something very similar to the following:

xpport SQL SERVER   Find Port SQL Server is Listening   Port SQL Server is Running

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

xpport2 SQL SERVER   Find Port SQL Server is Listening   Port SQL Server is Running

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

xpport3 SQL SERVER   Find Port SQL Server is Listening   Port SQL Server is Running

Now over here when you scroll down you will notice Port Details.

xpport4 SQL SERVER   Find Port SQL Server is Listening   Port SQL Server is Running

It is that easy to find the port of the second instance of SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Retrieve Maximum Length of Object Name with sp_server_info

A very common question which I often receive is

“How big my table name can be?”

The answer is simple 128.  You can figure this out by executing stored procedure sp_server_info on SQL Server.

Execute following stored procedure in SQL Server.

EXEC sp_server_info

It will return following resultset.

attribute_id attribute_name attribute_value
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH 128
14 MAX_QUAL_LENGTH 128
15 COLUMN_LENGTH 128
16 IDENTIFIER_CASE MIXED
17 TX_ISOLATION 2
18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
19 SAVEPOINT_SUPPORT Y
20 MULTI_RESULT_SETS Y
22 ACCESSIBLE_TABLES Y
100 USERID_LENGTH 128
101 QUALIFIER_TERM database
102 NAMED_TRANSACTIONS Y
103 SPROC_AS_LANGUAGE Y
104 ACCESSIBLE_SPROC Y
105 MAX_INDEX_COLS 16
106 RENAME_TABLE Y
107 RENAME_COLUMN Y
108 DROP_COLUMN Y
109 INCREASE_COLUMN_LENGTH Y
110 DDL_IN_TRANSACTION Y
111 DESCENDING_INDEXES Y
112 SP_RENAME Y
113 REMOTE_SPROC Y
500 SYS_SPROC_VERSION 11.00.3000

In the result set you can notice there is a row with information TABLE_LENGTH and the value is 128. There are few other information related to server is also listed in the result set.

Though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character. You can read the detailed blog post over here Maximum Allowable Length of Characters for Temp Objects is 116 – Guest Post by Balmukund Lakhani.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Select Columns from Stored Procedure Resultset

It is fun to go back to basics often. Here is the one classic question:

“How to select columns from Stored Procedure Resultset?”

Though Stored Procedure has been introduced many years ago, the question about retrieving columns from Stored Procedure is still very popular with beginners. Let us see the solution in quick steps.

First we will create a sample stored procedure.

CREATE PROCEDURE SampleSP
AS
SELECT
1 AS Col1, 2 AS Col2
UNION
SELECT
11, 22
GO

Now we will create a table where we will temporarily store the result set of stored procedures. We will be using INSERT INTO and EXEC command to retrieve the values and insert into temporary table.

CREATE TABLE #TempTable (Col1 INT, Col2 INT)
GO
INSERT INTO #TempTable
EXEC SampleSP
GO

Next we will retrieve our data from stored procedure.

SELECT *
FROM #TempTable
GO

Finally we will clean up all the objects which we have created.

DROP TABLE #TempTable
DROP PROCEDURE SampleSP
GO

Let me know if you want me to share such back to basic tips.

Reference: Pinal Dave (http://blog.SQLAuthority.com)