SQL Server Security Best Practices

Introduction

One of the primary responsibilities of Database Administrator is to Secure all SQL Server which they manage. SQL Server Security in itself is a very vast topic hence this article outlines some of the SQL Server Best Practices which Database Administrators can follow to Secure SQL Servers in their environment.

Regularly Audit SQL Server Logins

Database administrators should enable login auditing feature of SQL Server on all instances which they manage. Once this feature is enabled SQL Server will write all the failed and successful login information in SQL Server Error Logs. It is always a best practice to auditing failed SQL Server logins. Read the following article which explains “How to Auditing SQL Server Logins”.

Limited Privileges to SQL Server Service Account

Database administrator should always avoid running SQL Server Services under the context of Local Administrator, Local System or a Domain Administrator Account. All SQL Server Services should be configured to run under a minimal privileged Local Domain Account. SQL Server Service account should always be given Full Control Permissions on Data, Logs and Backup Directory to perform read and write activities. In case if you wish to change the default backup directory in SQL Server after installation then read the following article which explains “How to change the default database backup folder location in SQL Server”.

Always Setup Complex Passwords for SQL Server SA Account & SQL Server Logins

If you have configured SQL Server Instance to run under Mixed Mode Authentication then as a best practice one should always set a complex password for a System Administrator (SA) account. As a best practice one should change the SA password at regular intervals. Always use a windows account which has System Administrator to perform day-to-day maintenance activities on SQL Server. If there is more than one DBA in a team then it is recommended to create a Windows Domain Group and add all DBAs to that group and then give the windows domain group System Administrator Privileges on SQL Server. Read the following article which explains “How to identify currently used SQL Server authentication mode”.

Enforce Password Policies and Password Expiration for SQL Server Logins

It is always recommend to Enforce Password Policies and Password Expiration for SQL Server Logins when you use Mixed Mode Authentication. This will allow you to set complex password for a SQL Server Login. Read the following article which explains “How to enforce password policies and password expiration for SQL Server Logins”.

MORE SQL SERVER PRODUCT REVIEWS

FREE SQL SERVER WHITE PAPERS & E-BOOKS
FREE SQL SERVER PRODUCTS AND TOOLS

SIGN UP TODAY FOR MYTECHMANTRA.COM NEWSLETTER
LIKE US ON FACEBOOK   |  FOLLOW US ON TWITTER

Avoid Using SQL Server Authentication and Promote the Usage of Windows Authentication

As a best practice, to connect to a SQL Server instance one should always use Windows Authentication. The simple reason being when you use windows authentication the user passwords are authenticated by the active directory for windows logins thereby leveraging password policies set by your organization.

Periodic review of Windows and SQL Server Logins

As a best practice database administrators should perform a periodic review of all Windows and SQL Server Logins on every instance of SQL Server which they manage. This way you can remove logins which are no more relevant on the server. DBAs should very clear document all the logins along with each respective privilege within the Disaster Recovery document.

Encrypt SQL Server Database Backups

One of the primary responsibilities of a Database Administrator is to make sure all the databases are backed up regularly and the backups are restored on Disaster Recovery environments to make sure they are usable when they are needed the most. However, at the same time you need to make sure the database backups are encrypted to avoid the misuse. Learn more about Transparent Data Encryption Feature of SQL Server to know how to encrypt the database backups in SQL Server.

Secure Database Backup Folder from Unauthorized Access

Database backups are very critical and its DBAs responsibility to safeguard them from unauthorized user access. As a best practice access to database backup folders should be restricted and only those who really need access should be granted access. Unauthorized access to backup folders can be very dangerous as users can delete critical backup files, copy the backups to unauthorized locations etc.

Running SQL Server in Different Port other than the Default Port

By default SQL Server uses the TCP/IP Port 1433 for the Database Engine. As a best practice DBAs should change the TCP/IP Port on which SQL Server Database Engine will be listening. These changes must always be done using SQL Server Configuration Manager.

Disable SQL Server Browser Service

As a Security Best Practice database administrators should Disable SQL Server Browser Service when running the default instance of SQL Server. If there are any named instances of SQL Server then one must explicitly specify the port number within the connection strings to connect to the named instance. SQL Server Browser Service uses by default UDP Port 1434 for connectivity.

Hide an Instance of SQL Server

Database administrators can also hide an instance of SQL Server. This can be easily done by using SQL Server Configuration Manager. The SQL Server Browser Service basically enumerates instances of the Database Engine installed on the computer thereby enabling applications to browser for a server. Read the following article which explains “How to Hide an Instance of SQL Server”.

Encrypting Connections to SQL Server

Database administrators can enable Secure Sockets Layer (SSL) to encrypt the data which is transmitted across a network between the client application and the instance of SQL Server. SSL encryption increases the security of data which is transferred across the network between an application and SQL Server. However, one need to understand that enabling encryption will slow down the performance a bit. Hence it is always recommended to perform all testing before implementing in a production environment. Read the following article which explains “How to encrypt connections in SQL Server”.

Install Latest Service Packs and Hot fixes

As a best practice Database Administrators should always patch the SQL Servers which they manage with the latest service packs and hot fixes. Read the following article which has a complete list of all the Service Packs, Hot fixes and Cumulative Updates released by Microsoft for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Check the above links to see which patches are missing on your SQL Server Instances.

Dedicated SQL Server Connection

Dedicated SQL Server Connection is one very important feature which DBA’s must consider enabling it across all servers which they manage. This feature can help you troubleshoot issue rather than just restarting SQL Services during emergencies. Read the following article which explains “How to Use Dedicated Administrator Connection in SQL Server”.

Disable All the Unused SQL Server Features

As a best practice database administrators should always disable unused features of SQL Server such as OLE AUTOMATION, XP_CMDSHELL and OPENROWSET etc to reduce surface area attacks. These features can be easily be disabled or enabled using SQL Server Configuration Manager if you are using SQL Server 2005. If you are using a higher version of SQL Server then these features can be easily managed using Policy Based Management Feature which was initially introduced in SQL Server 2008.

Conclusion

SQL Server Security in itself is a vast topic and in this article we have discussed few of the settings which can be easily implemented to improve the overall security of the SQL Servers which you manage day-to-day in your work.

Read more: http://www.mytechmantra.com/LearnSQLServer/SQL-Server-Security-Best-Practices/#ixzz3h4cLiiFs
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

Question asked by one of my DBA friends.

One of my DBA friends asks me some SQL related Question. Here I am trying to explain it in this article

What are necessary steps should be taken for query execution time faster, suppose a Query when it's executing taking time 3min now I want to execute this query within 30secs how?

Answer: 

There are lots of factors related to make quay execution faster. My strong suggestion is to understand the execution plan to execute your query in better ways.

Some common factors that we all knows, related to SQL query executions are mentioned bellow.

a.    Don't use "SELECT * " in a SQL query. That means use the proper columns name that you needed not overload the query by using *. That added extra expenses to data retrieval.

b.    Don't use extra table join that you don't needed in your SQL statement.

c.    Don't use COUNT(*) in Sub query, instead use EXIST or NOT EXIST Clause.

      -- Do not Use

                    SELECT column_list

                    FROM   table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)

                    -- Use This

                    SELECT column_list

                    FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)

d.    Avoided joining between two types of columns like "INT" and "FLOAT" and  

      don't try to use CONVERT or CAST.

e.   Try to avoid dynamic SQL

f.    Try to avoid Temporary table

g.   Don't use IN or NOT IN clause in SQL statement, instead use EXISTS or

      NOT EXISTS

h.   Avoid LIKE instead you can use full text search.

i.    Try to use UNION or UNION ALL to implement OR operators.

j.    Don't calls any function in SELET statements, I mean try to avoid.

k.   Try to avoid correlated sub query

l.    Try to use stored procedure to execute all your T-SQL statement.

m.  Use VIEWs but always use WITH SCHEMA BINDING options

n.   Last but most important, solid idea related to Index. If necessary use

      guided index in your SQL join operations.

What is the difference between 'SET' and 'SELECT' in SQL?

Answer:

DECLARE @i INT

--Type1

SELECT @i=Roll FROM MyTab WHERE Name='RAJA'

--Type2

SET @i=(SELECT Roll FROM MyTab WHERE Name='RAJA')

Consider the above two SQL statements, the variable @i have the same value. So, what is the difference between two statements?

1.     From SQL server 7.0 Microsoft recommends to use the SET statement only, in such kind of above operations.

2.     SET is ANSI standard way to assign the value of a variable.

3.     BY SELECT we can assigned multiple value to multiple variable within a single statements. Like this.

DECLARE @i INT,

                           @k VARCHAR(MAX)  

          SELECT @i=Roll, @k=SName FROM MyTab WHERE SName='RAJA'

4.     SELECT has some difficulties like, if the above statements return more than one rows it not return any error. But if you use SET in the above example it give you error like "Sub query returns more than one values".

So at the conclusions, I recommended you to use SET and not to go at SELECT when assigning variables.

   What are the basic differences between SSMS 2005 & SSMS 2008?

Answer:

As per me, there are no differences; the difference is the database engine Limitations of SQL 2005 and SQL 2008. SQL 2008 gives you the better functionality and performance then SQL 2005.

Dynamic SQL in SQL Server

In this article, I am giving a quick overview about Dynamic Queries in SQL Server. After completing this article you will understand:

What is Dynamic Queries?

How can we write and Execute Dynamic Queries? 
What is difference between EXEC and SP_EXECUTESQL?

Please give your valuable suggestions and feedback to improve this article.

What is Dynamic Queries?

Dynamics Queries is nothing but SQL Query which is generated dynamically, stored in a variable and executed on the fly. So I can say Dynamic Queries is a term used to mean SQL code that is generated programatically by your program before it is executed.

How can we write and Execute Dynamic Queries?

Generally in our application we use hard coded SQL queries, but at sometime there is a need to dynamically create SQL Statement; so We build the sql statement as a string, then store it in variable and execute against an active database connection.

There can be many ways to generate Dynamic SQL, but at the end our SQL statements must be correct. If dynamically generated SQL Statement is not correct then it will never work.

There are three ways to execute Dynamic Queries.

1.       Write a Query with Parameter

2.       Using Exec()

3.       Using SP_ExecuteSQL

Write a Query with Parameter

This first approach is very easy to implement, In this approach we pass the parameters values into the WHERE clause of SQL query. The parameters value will be stored in the variable. This is also called as parameterized query.

Let’s first create a Employees table and populate some data then we will understand how does the above approach work?

USE TEACHMESQLSERVER

GO

IF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='EMPLOYEES')

DROP TABLE EMPLOYEES

GO

CREATE TABLE EMPLOYEES(EMPNAME VARCHAR(255), DEPARTMENT VARCHAR(100))

GO

INSERT INTO EMPLOYEES VALUES('RAM','HR')

INSERT INTO EMPLOYEES VALUES('KUMAR','IT')

INSERT INTO EMPLOYEES VALUES('SHYAM','HR')

INSERT INTO EMPLOYEES VALUES('GHANESH','IT')

INSERT INTO EMPLOYEES VALUES('GAUTAM','HR')

INSERT INTO EMPLOYEES VALUES('SUMIT','HR')

INSERT INTO EMPLOYEES VALUES('GOGNA','MARKETING')

GO

SELECT * FROM EMPLOYEES

Let's say we need to find all records from the Employees table where Department is HR. This can be done easily such as the following example shows.

SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='HR'

It can be written as below

DECLARE @DEPARTMENT VARCHAR(255)

SET @DEPARTMENT='HR'

SELECT * FROM EMPLOYEES WHERE DEPARTMENT = @DEPARTMENT

The above query is nothing but a Dynamic Query, which is executed with a parameter. As you can see parameter value is stored in variable and when we pass it into the Dynamic Sql statement we don’t quote it.

Using EXEC

We can build the SQL statement but It doesn't allow parameters like we used in previous example. We have to build the parameter in string and then concatenate it with the SQL statement. It never cache the execution plan but creates the execution plan for each individual query.You will understand what does this means later in this article.

Lets modify the previous query so that we can use it in EXEC.

DECLARE @DEPARTMENT VARCHAR(255), @DYNAMICQUERY VARCHAR(MAX)

SET @DEPARTMENT='''HR'''

SET @DYNAMICQUERY = 'SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='

EXEC(@DYNAMICQUERY+@DEPARTMENT)

As you can see from this example handling the @department value is not at straight forward, because you also need to define the extra quotes in order to pass a character value into the query.  These extra quotes could also be done within the statement, but either way you need to specify the extra single quotes in order for the query to be built correctly and therefore run.

Using SP_ExecuteSQL

sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql. With this approach you have the ability to still dynamically build the SQL query, but you are also able to still use parameters as you could in example under write query with a parameter. In this approach there is no need to put extra quotes on parameters like we did in previous example. only you have to create sql statement with parameter and store it in variable. In addition, with using this approach you can ensure that the data values being passed into the query are the correct data types.

DECLARE @DEPARTMENT NVARCHAR(255), @QUERY NVARCHAR(MAX)

SET @DEPARTMENT='HR'

SET @QUERY ='SELECT * FROM EMPLOYEES WHERE DEPARTMENT =@DEPARTMENT'

EXEC SP_EXECUTESQL @QUERY ,N'@DEPARTMENT NVARCHAR(255)', @DEPARTMENT=@DEPARTMENT

As you can see from the above example, we have created the dynamic query and stored in @query variable. We are using sp_executesql to execute the dynamic query. we are passing the parameter without quotes.

A little complex example

Suppose I ask you to create a Table name as DynamicTable which has one column name as Query data type is varchar then how will you create it. Yes you are correct, you will write a create table SQL statement as below.

USE TEACHMESQLSERVER

GO

CREATE TABLE DYNAMICTABLE (QUERY VARCHAR(255))

Above SQL statement is a simple SQL statement, which can be run dirtectly.

I am inserting some SQL Statements in DynamicTable table.

INSERT INTO DYNAMICTABLE VALUES ('CREATE TABLE TEMP ('+'DUMMY VARCHAR(MAX))'); -- SQL STATEMENT TO CREATE A TEMP TABLE

INSERT INTO DYNAMICTABLE VALUES ('INSERT INTO TEMP VALUES (''THIS IS INSERTED BY THE FIRST RUN'')'); -- SQL STATEMENT TO INSERT A RECORD IN TEMP TABLE

INSERT INTO DYNAMICTABLE VALUES ('INSERT INTO TEMP VALUES (''THIS IS INSERTED BY THE SECOND RUN'')'); -- SQL STATEMENT TO INSERT A RECORD IN TEMP TABLE

INSERT INTO DYNAMICTABLE VALUES ('INSERT INTO TEMP VALUES (''THIS IS INSERTED BY THE THIRD RUN'')'); -- SQL STATEMENT TO INSERT A RECORD IN TEMP TABLE

GO

SELECT * FROM DYNAMICTABLE 

Now what I want is to execute all the SQL Statement present in DynamicTable table. The SQL Statement present in the DynamicTable can be think as Dynamic SQL Statement because it was generated and stored in the table.

Solution

DECLARE @FirstRecord INT , @LastRecord INT, @DynamicQuery VARCHAR (255),@MYRANK INT

SET @FirstRecord=1

SELECT @LastRecord = COUNT(*) FROM DYNAMICTABLE

WHILE @FirstRecord<= @LastRecord

BEGIN

SELECT @DynamicQuery=QUERY,@MYRANK=MYRANK FROM (SELECT QUERY, ROW_NUMBER() OVER(ORDER BY QUERY) AS MYRANKFROM DYNAMICTABLE ) A WHERE MYRANK=@FirstRecord

EXEC(@DynamicQuery)

SET @FirstRecord =@FirstRecord +1

END

GO

SELECT * FROM TEMP

As you can see, our Dynamic query were successfully executed. In first Dynamic Query We created a table name as TEMP and then inserted three records.

What is Difference between Exec and sp_executesql and which one to use?

Exec

·         Exec doesn’t allow sql statement to be parameterized, we have to build the string for parameter, therefore it is less secure than sp_executesp in terms of SQL Injection.

·         It never cache the execution plan but creates execution plan for each execution this can be advantage in some cases; but it can also be disadvantage in some cases like SQL Server needs to recomplie/ optimize for each execution.

We have already discussed the first point in previous example, now let’s understand the second point with a simple example.

Below I am using the same query I had used for explaining EXEC and SP_EXECUTESQL.

DBCC FREEPROCCACHE -- LET'S CLEAR THE EXECUTION PLAN FROM BUFFER

-- LET'S Execute the Below query

DECLARE @DEPARTMENT VARCHAR(255), @DYNAMICQUERY VARCHAR(MAX)

SET @DEPARTMENT='''IT'''

SET @DYNAMICQUERY = 'SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='

EXEC(@DYNAMICQUERY+@DEPARTMENT)

--To execute the above query database engine will create the execution plan, let’s check the Chached Execution plan for above query.

SELECT A.TEXT,*

FROM SYS.DM_EXEC_CACHED_PLANS B

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A

WHERE A.TEXT LIKE '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'

AND A.TEXT NOT LIKE '%SELECT A.TEXT%'

-- LET'S Execute the Below query with different parameter value

DECLARE @DEPARTMENT VARCHAR(255), @DYNAMICQUERY VARCHAR(MAX)

SET @DEPARTMENT='''HR'''

SET @DYNAMICQUERY = 'SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='

EXEC(@DYNAMICQUERY+@DEPARTMENT)

--To execute the above query database engine again will create the execution plan, let’s check the Cached Execution plan for second query.

SELECT A.TEXT,*

FROM SYS.DM_EXEC_CACHED_PLANS B

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A

WHERE A.TEXT LIKE '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'

AND A.TEXT NOT LIKE '%SELECT A.TEXT%'

As you can see database engine is creating execution plan for each execution.you can see in the Text column table has one record for HR parameter and another record for IT parameter.

SP_executesql

·         sp_executesql allows for statements to be parameterized, therefore it is more secure than EXEC in terms of SQL Injection.

·         TSQL string is build one time, when it get executed first time execution plan created by Database engine will be cached. When we execute it next time it uses that same execution plan, It never Creates second Execution Plan it. This can be advantage and disadvantage, so you have to decide what to use to execute dynamic query based on your requirement.

We have already discussed the first point in previous example, now let’s understand the second point with a simple example.

DBCC FREEPROCCACHE -- LET'S CLEAR THE EXECUTION PLAN FROM BUFFER

-- LET'S Execute the Below query

DECLARE @DEPARTMENT NVARCHAR(255), @QUERY NVARCHAR(MAX)

SET @DEPARTMENT='IT'

SET @QUERY ='SELECT * FROM EMPLOYEES WHERE DEPARTMENT =@DEPARTMENT'

EXEC SP_EXECUTESQL @QUERY ,N'@DEPARTMENT NVARCHAR(255)', @DEPARTMENT=@DEPARTMENT

--To execute the above query database engine will create the execution plan, let’s check the Cached Execution plan for above query.

SELECT A.TEXT,*

FROM SYS.DM_EXEC_CACHED_PLANS B

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A

WHERE A.text like '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'

and A.TEXT NOT LIKE '%SELECT A.TEXT%'

-- LET'S Execute the Below query with different parameter value

DECLARE @DEPARTMENT NVARCHAR(255), @QUERY NVARCHAR(MAX)

SET @DEPARTMENT='HR'

SET @QUERY ='SELECT * FROM EMPLOYEES WHERE DEPARTMENT =@DEPARTMENT'

EXEC SP_EXECUTESQL @QUERY ,N'@DEPARTMENT NVARCHAR(255)', @DEPARTMENT=@DEPARTMENT

--To execute the above query database engine will not create the execution plan. It will use the same execution plan which was used to run the query first time. let’s check and confirm the Chached Execution plan for second query.

SELECT A.TEXT,*

FROM SYS.DM_EXEC_CACHED_PLANS B

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A

WHERE A.text like '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'

and A.TEXT NOT LIKE '%SELECT A.TEXT%'.

As you can see it didn’t not create another execution plan, it used the old execution plan. You can also see object type for this plan is prepared.

Dynamic SQL result stored in a TABLE

Introduction

As we all know that the dynamic SQL is not good where performance is concern. But sometimes we don't have other choices. As I personally think that, no developer chooses the dynamic SQL with interest, but they choose it as they have no alternates to minimize the code. Well the debugging of dynamic SQL is not so easy.  

Some times when the developer works with stored procedure they want to make certain dynamic SQL and want to copy the output or the result set of the dynamic SQL into a table and next want to work with table for farther processing.

Please note that the Temporary Table is not permitted here.

The Problem

As the columns name and data type of the dynamic SQL is dynamic, it is not possible to make a table definition within the stored procedure.

To understand it properly here I am providing an example.

-- Base Table Object Creation

IF OBJECT_ID('my_EMP') IS NOT NULL

   BEGIN

     DROP TABLE my_EMP;

   END

GO  

CREATE TABLE my_EMP

       (EMPID    INT         NOT NULL IDENTITY PRIMARY KEY,

        EMPNAME  VARCHAR(50) NOT NULL,

        EMPGRADE VARCHAR(1)  NOT NULL,

        EMPDEPT  VARCHAR(50) NOT NULL,

        EMPCITY  VARCHAR(50) NOT NULL,

        EEMSTATE VARCHAR(50) NOT NULL);

GO

-- Inserting some Records

INSERT INTO my_EMP

       (EMPNAME, EMPGRADE, EMPGRADE, EMPDEPT, EMPCITY, EEMSTATE)      

VALUES ('JOYDEEP DAS', 'B', 'DEV', 'AGARTALA', 'TRIPURA'),

       ('TUHIN SHINAH', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL'),

       ('SANGRAM JIT', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL'),

       ('SUKAMAL JANA', 'B', 'DEV', 'CHUCHURA', 'WEST BENGAL'),

       ('SUDIP DAS', 'A', 'MGR', 'KOLKATA', 'WEST BENGAL'),

       ('SAIKAT SREE', 'A', 'MGR', 'KOLKATA', 'WEST BENGAL'),

       ('MANI SANKAR', 'C', 'DM', 'AGARTALA', 'TRIPURA'),

       ('A DEKA', 'C', 'DM', 'GWAHATI', 'ASSAM');

GO

-- Creting Stored Procedure with Dynamic SQL

-- Note that the parameters, one is the fields name and second is the Table objects

-- So the Dynamic Sql fields and table are dynamic and depends on user input.  

IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL

   BEGIN

     DROP PROCEDURE usp_DISPLAYEMP;

   END

GO         

CREATE PROCEDURE usp_DISPLAYEMP

       (

          @p_Attributes VARCHAR(200) = NULL,

          @p_TblName    VARCHAR(50)  = NULL

       )

AS

  DECLARE @sqlString VARCHAR(200);

  BEGIN

   IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''

     BEGIN

         SET @sqlString='SELECT '+ @p_Attributes + ' FROM '+ @p_TblName;

         EXEC (@sqlString);

     END

   ELSE

     BEGIN

        PRINT 'Input can not be Null';

     END   

  END

-- Executing the SP [ with Fields name EMPNAME, EMPGRADE, EMPGRADE ]

EXEC usp_DISPLAYEMP

     @p_Attributes = 'EMPNAME, EMPGRADE, EMPGRADE',

     @p_TblName    = 'my_EMP'

The output is

EMPNAME                                            EMPGRADE EMPGRADE

-------------------------------------------------- -------- --------

JOYDEEP DAS                                        B        B

TUHIN SHINAH                                       B        B

SANGRAM JIT                                        B        B

SUKAMAL JANA                                       B        B

SUDIP DAS                                          A        A

SAIKAT SREE                                        A        A

MANI SANKAR                                        C        C

A DEKA                                             C        C

(8 row(s) affected)

-- Executing the SP [ with Fields All Fields Name ]    

EXEC usp_DISPLAYEMP

     @p_Attributes = '*',

     @p_TblName    = 'my_EMP'

The Output is

EMPID     EMPNAME        EMPGRADE  EMPDEPT    EMPCITY       EEMSTATE

----------------------------- ---------------------------------------

1         JOYDEEP DAS    B         DEV         AGARTALA     TRIPURA

2         TUHIN SHINAH   B         DEV         KOLKATA      WEST BENGAL

3         SANGRAM JIT    B         DEV         KOLKATA      WEST BENGAL

4         SUKAMAL JANA   B         DEV         CHUCHURA     WEST BENGAL

5         SUDIP DAS      A         MGR         KOLKATA      WEST BENGAL

6         SAIKAT SREE    A         MGR         KOLKATA      WEST BENGAL

7         MANI SANKAR    C         DM          AGARTALA     TRIPURA

8         A DEKA         C         DM          GWAHATI      ASSAM

(8 row(s) affected)

So in this example the output of the stored procedure depends on user input. We can't understand how many columns it displays.

We want to use a table within this stored procedure.

Easy Solutions

Example-1

-- Using Temp Table    

IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL

   BEGIN

     DROP PROCEDURE usp_DISPLAYEMP;

   END

GO         

CREATE PROCEDURE usp_DISPLAYEMP

       (

          @p_Attributes VARCHAR(200) = NULL,

          @p_TblName    VARCHAR(50)  = NULL

       )

AS

  DECLARE @sqlString VARCHAR(200);

  BEGIN

     IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''

        BEGIN

         SET @sqlString='SELECT '+ @p_Attributes + 
                        ' INTO TMP_TBL FROM '+@p_TblName;

         EXEC (@sqlString);

        END

     ELSE

        BEGIN

            PRINT 'Input can not be Null';

        END   

  END 

Example-2

-- Using Temp Table    

IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL

   BEGIN

     DROP PROCEDURE usp_DISPLAYEMP;

   END

GO         

CREATE PROCEDURE usp_DISPLAYEMP

       (

          @p_Attributes VARCHAR(200) = NULL,

          @p_TblName    VARCHAR(50)  = NULL

       )

AS

  DECLARE @sqlString VARCHAR(200);

  BEGIN

     IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''

        BEGIN

             SET @sqlString='SELECT '+ @p_Attributes +

                            ' INTO ' + TMP_TBL +

                            ' FROM '+ @p_TblName;

             EXEC (@sqlString);

        END

     ELSE

        BEGIN

            PRINT 'Input can not be Null';

        END   

  END            

Share your Knowledge

If we can put the output in a Temporary Table (the Local Temporary Table) it well is the BEST.

But in this situation it is NOT possible. If have you an idea, please share your knowledge with us.

Related Tropics

Dynamic SQL With sp_executesql ()

http://sqlknowledgebank.blogspot.in/2012/06/dynamic-sql-with-spexecutesql.html

Hope you like it.

Dynamic SQL With sp_executesql ()

Dynamic SQL is a part of the development. It is not a good idea to use the dynamic SQL. But the real facts are that, in such kind of situation we must use the dynamic SQL.

Here in this article I am trying to illustrate the execution of dynamic SQL.

We can execute the dynamic SQL by using

1.    EXECUTE statements

2.    By using SP_EXECUTESQL () stored procedure.

Microsoft always recommended that to execute the dynamic SQL we must use the stored procedure SP_EXECUTESQL ().

Why we are going to use SP_EXECUTESQL ()

First we take an example:

CREATE PROC sp_sample

      @v_tblName sysname

AS

DECLARE @strQuery nVARCHAR(4000)

SELECT @strQuery = 'SELECT * FROM DBO.' + QUOTENAME(@v_tblName)

 EXEC SP_EXECUTESQL()  @strQuery -------- (Statement -A)

 EXEC (@strQuery)                -------- (Statement -B)

GO

In this example both Statement-A and Statement-B gives us the same output.

But for Statement-A, we must declare the @strQuery as nVARCHAR, nCHAR or nTEXT others it gives us the following error.

Msg 214, Level 16, State 2, Procedure sp_SP_EXECUTESQL() , Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

The EXEC is Un-parameterized and SP_EXECUTESQL ()  is parameterized, that means if we write a query which takes a parameter like "EmpID". When we run the query with "EmpID" as 1 and 2 it would be creating two different cache entries (one each for value 1 and 2 respectively).

It means for Un-parameterized queries the cached plan is reused only if we ask for the same id again. So the cached plan is not of any major use.

But in case of SP_EXECUTESQL () the similar situation for "Parameterised" queries the cached plan would be created only once and would be reused 'n' number of times. So this would have better performance.

I am trying to explain it by an example

CREATE TABLE my_emp

       (EmpID    INT        NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpName  VARCHAR(50)NOT NULL)

GO

       

INSERT INTO my_emp (EmpName)

VALUES('Joydeep Das'),

      ('Sukamal jana'),

      ('Sudip Das'),

      ('Tuhin Shinah') 

     

CREATE TABLE my_empGarde

       (EmpID    INT        NOT NULL PRIMARY KEY,

        EmpGrade VARCHAR(50)NOT NULL,

        FOREIGN KEY (EmpID) REFERENCES my_emp(EmpID))

GO

INSERT INTO  my_empGarde (EmpID, EmpGrade)

VALUES (1, 'A'),

       (2, 'A'),

       (3, 'A+'),

       (4, 'B')

      

GO

DBCC Freeproccache

/*

Here DBCC FREEPROCCACHE to clear the procedure cache.

Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled

instead of reused from the cache.

*/

DECLARE @v_SQLTxt nVARCHAR(1000)

SET  @v_SQLTxt='SELECT a.EmpID, a.EmpName, b.EmpGrade

                FROM   my_emp a

                INNER  JOIN  my_empGarde b ON a.EmpID = b.EmpID

                WHERE  a.EmpID = N''1'''

               

EXEC (@v_SQLTxt)

SET  @v_SQLTxt='SELECT a.EmpID, a.EmpName, b.EmpGrade

                FROM   my_emp a

                INNER  JOIN  my_empGarde b ON a.EmpID = b.EmpID

                WHERE  a.EmpID = N''2'''

               

EXEC (@v_SQLTxt)               

                

SET  @v_SQLTxt='SELECT a.EmpID, a.EmpName, b.EmpGrade

                FROM   my_emp a

                INNER  JOIN  my_empGarde b ON a.EmpID = b.EmpID

                WHERE  a.EmpID = @EmpID'

Exec sp_executesql @v_SQLTxt, N'@EmpID int', 1

Exec sp_executesql @v_SQLTxt, N'@EmpID int', 2

/*

  After this lets have a look at the cached plan by executing the below query.

  The first two (Unparameterised) has a execution_count of 1,

  the last one (Parameterised) would have an execution_count of 2.

*/

SELECT sqlTxt.text, qStats.execution_count

FROM   sys.dm_exec_query_stats qStats

       CROSS APPLY (SELECT [text]

                    FROM   sys.dm_exec_sql_text(qStats.sql_handle)) assqlTxt option (Recompile)