Filtering Results of a Stored Procedure

Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.

1. Using OpenRowSet Command

SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=ServerName;TRUSTED_CONNECTION=YES;', 'EXEC sp_Who2')

Remember in this method this feature should be enabled in the server.

2. Using Temporary Tables.

-- Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))

-- Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2

-- Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = 'Domain\UserName'

-- Removing the temporary table.
DROP TABLE #tblSPWho2

SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).

Finding Number of Records in SQL Tables

By using the following query you will be able to get all the tables with at least one record in the selected database. It will be handy when you needed to get an idea of the number of records in a database. This uses two SQL Server system views, namely SysObjects and SysIndexes.

1. SELECT sysobjects.name AS [Table Name],
2.         MAX(sysindexes.rows) AS [Number of Records],
3.         sysobjects.crdate AS [Created Date],
4.         sysobjects.refdate AS [Referenced Date]
5. FROM sysobjects
6. INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
7. WHERE sysobjects.xtype = 'U' -- Filtering all the User Tables.
8.     AND sysindexes.rows > 0 -- Getting all the tables having at least one reoord.
9. GROUP BY sysobjects.name,
10.     sysobjects.crdate,
11.     sysobjects.refdate
12. ORDER BY 2 DESC -- Ordering by the number of records in table.

 

Above query will bring the following results on the Northwind database.

SQL Truncate vs. Delete

By now as you know, in this post I am going to discuss about two SQL commands we use regularly to clear data in our tables.

Usage

1. DELETE FROM TABLENAME
3. TRUNCATE TABLE TABLENAME

Even though they do a similar thing, there are some differences which makes them unique commands.

	DELETE	TRUNCATE
1.	Considered as Data Mining Language (DML) statement.	Considered as Data Definition Language (DDL) statement.
2.	Can be used to delete all or part of data in a table.	Will delete all the data in a table.
3.	Will log the actions.	There will be no logging on record deletion.
4.	Will use more locks.	Will use fewer locks.
5.	Will use more resources.	Will use less resources.
6.	Slow.	Fast.
7.	Will not reset the identity seed.	Will reset the identity seed to 0.
8.	Can be used to delete data in tables having relationships.	Cannot use in tables having relationships.
9.	Can be used in tables involved in log shipping or replication.	Cannot be used in tables involved in log shipping or replication.
10.	Transaction can be rolled back.	No rollback.
11.	Table may keep the empty pages. Can be released by running, SHRINKDATABASE (Database Name).	Data pages related to the table will be de allocated and returned to the system.
12.	Related Triggers are fired.	Triggers are not fired.

 

If you used DELETE to fully remove all the records, you can reset the identity value by running the following command.

1. DBCC CHECKIDENT (TABLENAME, RESEED, 0)

If you do have some rows left in table, simply replace 0 with the last identity column value. For example if you put 5 instead of 0 then the next record inserted into the table will have an identity column value of 6.

If you jus need to check the current identity value just use the following command.

1. DBCC CHECKIDENT (TABLENAME, NORESEED)

Importing Data into SQL

If you need to import data from a file, this can be achieved by using SQL Bulk Insert command. Recently I did use this method to import some 500 000 data from few comma separated value (CSV) files and thought to share it with you. What you need to remember is, if you are importing data from more than one file the data should have the same format through out the files.

This is a sample set of data I imported into my table from the file named File1.csv.

Login,Name,Date,Result,Pass

U0001,Roman Silva,1/10/2010 17:23,100,TRUE

U0002,Anthony Don,28/09/2010 10:01,70,TRUE

U0003,Saman Perera,16/09/2010 11:31,90,TRUE

U0004,Silvia Raz,26/09/2010 22:11,40,FALSE

U0005,Rebecca Maine,18/09/2010 11:30,100,TRUE

I used the following script to create a temporary table for my imported data.

1. SET ANSI_NULLS ON
2. GO
3.  
4. SET QUOTED_IDENTIFIER ON
5. GO
6.  
7. CREATE TABLE [dbo].[TABLENAME_ImportedData](
8.     [LoginId] [NVARCHAR](50) NOT NULL,
9.     [Name] [NVARCHAR](200) NULL,
10.     [Date] [DATETIME] NULL,
11.     [Result] [INT] NULL,
12.     [Pass] [NVARCHAR](8) NULL
13. ) ON [PRIMARY]
14.  
15. GO

To fetch data from the file I used the following script.

1. BULK INSERT [TABLENAME_ImportedData]
2. FROM 'D:\DataFiles\File1.csv'
3. WITH (
4.          FIELDTERMINATOR =',', -- Since my columns are seperated using commas (,).
5.          ROWTERMINATOR ='\n',  -- Since each data row is in its own line.
6.          FIRSTROW = 2          -- Since my first row is having column names. Please note FIRSTROW is not recommended to skip the column names.
7.       )

While running the script I faced an issue with the date since my server was set to US English as the default language. In US English the dates should be in MDY format. So I had 3 choices, either to change the date formats on my data files, change the server default language to another language which has its date format as DMY or change the date format of the server. I used the easy way to change the date format of the server by running  the following command.

1. SET DATEFORMAT dmy

If you like to change SQL server default language and need help please read my article on that.

Changing SQL Server Default Language

If you needed to change the default language of a SQL Server login you can do so by using sp_defaultlanguage command.

First determine the current default language by using the @@language variable as follows.

1. SELECT @@language

Then choose the language you want to change to from the available languages. You can find the available languages by the following command.

1. EXEC sys.sp_helplanguage

After selecting the language wanted to change, run the following command to change the default language, I am changing it to British using the following command.

1. DECLARE @Login NVARCHAR(30)
2. SET @Login = SYSTEM_USER
3. EXEC sp_defaultlanguage @Login, 'British'

Run the @@language to see whether the default language is changed as you wanted. Do not forget to use a new query window to see the changes.

Creating Database Table and Moving Data

When the need comes to port tables from one server / database to another server / database there is an easy way than creating the tables manually and importing data into table.

 

By using the following query you can import the table structure and the data of the required table.

1. SELECT *
2. INTO [DestinationServerName\SQLServerInstanceName].[DestinationDatabaseName].[OwnerName].[DestinationTableName]
3. FROM [SourceServerName\SQLServerInstanceName].[SourceDatabaseName].[OwnerName].[SourceTableName]

 

Using the above method you can transfer tables between different databases, Server instances or different database servers.

Consider the following example in which I am transferring the Customers table from Northwind database to my Test database.

 

1. SELECT *
2. INTO [Test].[dbo].[Customers]
3. FROM [Northwind].[dbo].[Customers]

 

Here SELECT statement will behave the same way as in a normal SELECT * FROM TableName statement meaning you can use all the techniques used in SELECT statements to filter the data needed to be ported. For example following query will only bring customers who are from UK.

1. SELECT *
2. INTO [Test].[dbo].[Customers]
3. FROM [Northwind].[dbo].[Customers]
4. WHERE Country = 'UK'

 

One thing to remember is, this method will not import the keys, meaning if you had a primary key set to CustomerId it will not exist in your new table. So you need to add the keys (primary and secondary) to the new table to match it with the source table. If you had identity specification turned on in your source table this will set it for you, so you do not need to set it again.

Dynamically Creating Computed Columns in SQL

If you are into programming you should have definitely worked with calculated fields in SQL. But for others I will briefly explain what they are.

Calculated columns are columns which depend on other columns. It gets its value by calculating which can involve values of other columns. The calculation formula is the only thing stored in the column.

So each time the column is referenced the calculation is done. But if you use the keyword “persisted” while creating the column then the values will be kept in the table. Whenever a referenced value is updated the computed column is also automatically updated to highlight the change. Also by using persisted you can index a computed column.

You can create a table with persisted computed column as follows.

1. CREATE TABLE Customer
2. (
3. CustomerId INT IDENTITY(1, 1) NOT NULL,
4. CustomerFirstName NVARCHAR(100) NOT NULL,
5. CustomerLastName NVARCHAR(100) NOT NULL,
6. CustomerFullName AS CustomerFirstName + ' ' + CustomerLastName PERSISTED
7. )

One thing to remember is that you cannot directly insert values to a computed column.

I think you got a basic idea of computed columns. Now I would like to show how to create a computed column dynamically. For example think that you need to add a computed column to a table using a stored procedure. It is not a big deal.

I need to insert a TotalOrder column to the table named FoodOrder.

1. CREATE TABLE FoodOrder
2. (
3. OrderId INT IDENTITY(1, 1) NOT NULL,
4. OrderDate SMALLDATETIME NULL,
5. CustomerName NVARCHAR(100) NOT NULL,
6. TotalStarter INT NULL,
7. TotalMainCourse INT NULL,
8. TotalSoftBevarage INT NULL,
9. TotalLiquer INT NULL,
10. TotalDessert INT NULL
11. )

This can be done using the following query.

1. DECLARE @sExecuteCommand VARCHAR(250), --Keepa the command to be executed.
2. @sColumns VARCHAR(150) --Keeps the columns to be included in the formula.
3.  
4. SET @sColumns = 'TotalStarter+TotalMainCourse+TotalSoftBevarage+TotalLiquer+TotalDessert+'
5. SET @sExecuteCommand = 'ALTER TABLE FoodOrder ADD TotalOrder AS ' + SUBSTRING(@sColumns, 1, LEN(@sColumns)-1) -- Creating the computed column.
6. EXEC (@sExecuteCommand)

Note that a cursor or a loop can be easily used to populate the variable “sColumns” with the columns required for the formula.

Hope this helps.

UPDATE with JOIN

If you need to update data of a table (DestinationTable) with data of another table (SourceTable) there are 3 ways you can get this done in SQL.

Method 1

This is the most common and simple way with using a sub query.

1. UPDATE DestinationTable SET DestinationColumn =
2.     (SELECT ValueColumn FROM SourceTable WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn)

 

Method 2

This users the most common FROM clause to join the two tables as shown below.

1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
2. FROM SourceTable
3. WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn

 

Method 3

Last method mentioned below uses the join clause to join both the tables to make the update happen properly.

1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
2. FROM DestinationTable
3. INNER JOIN SourceTable ON DestinationTable.MappingColumn = SourceTable.MappingColumn

 

Hope these helps.

Database Not Accessible

Recently after restoring a database backup which I got from another server I repeatedly encountered an error when logged in using a user who is given permission. But when ‘sa’ account is used I could work with the database without any problem.

The error message was “The database DATABASE NAME is not accessible. (ObjectExplorer)” which was not helpful since it didn’t give any clue to figure out the issue.

Later I found that the issue is because the user in the restored database is not properly mapped to the user in the new server and the resolution for this is to run the stored procedure sp_change_users_login to correct the orphaned user.

• sp_change_users_login 'update_one', 'USER', 'LOGIN' – Links the given user in the current database to the specified login.
• sp_change_users_login 'auto_fix', 'USER' – Links the given user in the current database to the login having the same name in the current server.

After running this you will be able to access the restored database without any issue using the mentioned database user.

Using Single Quotes in SQL Queries

Did you ever searched how to use a single quote inside SQL query? In this article I explain how to do.

( 1.) To display a quote in between of two field values.
SELECT FirstName + ''' ' + LastName From tblStudent

The above will put a single quote and a space in between FirstName and LastName fields.

( 2.) To insert a record with a quote to a table.
INSERT INTO [tbl] (ColumnName) VALUES ('I'+'''m ok.')

The above will insert a record into tbl and will put the string of {I'm ok.} to the column ColumnName.

( 3.) To update a record will be possible as follows.
UPDATE [tbl] SET ColumnName='I'+'''m ok.'

The above will update ColumnName values of all the records of tbl in to {I'm ok.}.