Feeds:
Posts
Comments

SQL SERVER – How to format values as a percentage

August 4, 2013 by Muhammad Imran

In SQL Server, we usually come across a situation where we need to format values as a percentage. Usually, developers format the values as a percentage manually. But in this article, I will share a shortcut to format values as a percentage.
Before proceeding with the solution, I would like to create a sample table and data to demonstrate the formatting.

USE tempdb
GO
--DROP TABLE [dbo].[Student]
--GO
CREATE TABLE [dbo].[Student]
(
[Student ID] int,
[Student Name] [varchar](50) NOT NULL,
[Percentage] numeric(18,2)
)
GO
Insert into dbo.[Student] values (1,'Bob', 0.10)
Insert into dbo.[Student] values (2,'Alexander',0.50)
Insert into dbo.[Student] values (3,'Hosanna',0.85)
Insert into dbo.[Student] values (4,'William',0.11)
Insert into dbo.[Student] values (5,'Hulda',1.20)
Insert into dbo.[Student] values (6,'Jacoba',1.15)
GO

Solution 1 :
In this solution, we need to manually calculate the percentage and then format it. Given below is the script.

--This script is compatible with SQL Server 2000 and above.
USE tempdb
GO
SELECT [Student ID]
,[Student Name]
,[Percentage]
,CONVERT(VARCHAR(50),[Percentage]*100)+' %' AS [%] FROM dbo.Student
GO

Formatvalues1.1

Solution 2 :
In this solution, we will use a new function shipped with SQL Server 2012 namely Format. You just need to pass the values in this function and it will format values as a percentage (without any hurdles of calculations).
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT [Student ID]
,[Student Name]
,[Percentage]
,FORMAT([Percentage],'p') AS [%] FROM dbo.Student
GO

Formatvalues1.1

Posted in Solutions, SQL SERVER | Tagged , , , , | 2 Comments »

SQL SERVER 2012 – Fix – Error -11507 – The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid.

August 3, 2013 by Muhammad Imran

The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid is one of the new error messages shipped with SQL Server 2012. This error message is related to sp_describe_undeclared_parameters, a new system stored procedure shipped with SQL Server 2012 as well.

Lets discuss this in detail:
Message Number: 11507

Severity : 16

Error Message: The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid.

Error Generation:

Given below is the script that I tried to execute but it gave me this error.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID
And Instructions =@Instruction'
GO
--OUTPUT

Msg 11507, Level 16, State 1, Line 1
The parameter type for ‘@Instruction’ cannot be deduced because no type would make the query valid.

Ooopps…… I am unable to execute it.

erromessage11507.1.1

Resolution:

To resolve this issue you need to go to details of sp_describe_undeclared_parameters because sp_describe_undeclared_parameters does not support few data types and if those data types comes as undeclared parameters, it generates the error message.
Given below are the details of unsupported data types:

  • image
  • text
  • ntext
  • xml

Lets rewrite the script and remove the xml parameter from the script.
Given below is the script:

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID '
--OUTPUT

Conclusion :
Remember, whenever you use sp_describe_undeclared_parameters, make sure you are using only supportive data type columns.

Let me know if you faced this issue and solved it in a different way.

Posted in Errors, SQL SERVER | Tagged , , , | Leave a Comment »

SQL SERVER HEKATON – How to implement default constraints in the memory optimized table

August 2, 2013 by Muhammad Imran

‘How to implement default constraints in the memory optimized table’ seems very simple to me and I tried to implement in the same way, as we usually do it in the disk based table. But the output was not as per the expectation. Let me show you the output.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime default Getdate()
) WITH (MEMORY_OPTIMIZED = ON)
GO

Msg 10770, Level 16, State 13, Line 3
The feature ‘default constraints’ is not supported with memory optimized tables.

Oooops.. I am unable to create a default constraint in memory optimized table.

So, can we create a default constraint column in memory optimized table ? The answer is No & Yes.

Why No: Because you cannot create a default constraint column in memory optimized table.
Why Yes:Because you can implement the default constraint functionality in memory optimized table using stored procedure.

Let me create the table & stored procedure to implement default constraints in the memory optimized table.

Step 1 :
First of all, you need to create a memory optimized table without any default constraint.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO

Step 2 :
Once you have created the memory optimized table, create a stored procedure to insert records in it.

USE hkNorthwind
GO
CREATE PROCEDURE usp_Insert_Product_Master
@ProductID int,
@ProductName nvarchar(100)
AS
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (@ProductID,@ProductName,getdate())

Step 3 :
Insert the records using stored procedure in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
EXEC usp_Insert_Product_Master 1, 'SQL Server 2012'
GO
EXEC usp_Insert_Product_Master 2, 'SQL Server 2014'

Step 4 :
Just browse the table and check whether the default constraint is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

implement_deafult_constraints.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Posted in Solutions, SQL SERVER | Tagged , , , | 1 Comment »

SQL SERVER – How to concatenate int and varchar

July 31, 2013 by Muhammad Imran

Concatenate int and varchar is a usual problem that we face in SQL scripting and the solution is easily available over the web. But what encourages me to write this article is, we can resolve this issue using a built in function in SQL Server 2012.
Let me show you the issue that we generally face when we concatenate int and varchar. Given below is the script.

Use AdventureWorks2012
GO
SELECT [DepartmentID] + ' ' + [Name] AS [Department ID & Name]
FROM [HumanResources].[Department]
GO
--OUTPUT

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘Document Control’ to data type smallint.

Solution 1:
In the earlier version of SQL Server, we usually use CONVERT function to convert int into varchar and then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONVERT(VARCHAR(5),[DepartmentID]) + ' ' + [Name]
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

conactenate int to varchar1.1

Solution 2:
In this solution, we will use CONCAT function (a newly shipped function in SQL Server 2012) to convert int into varchar then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONCAT([DepartmentID] , ' ' , [Name] )
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

conactenate int to varchar1.1

Conclusion :
I generally recommend solution 2 because as you can see that there is no difference in output between solution 1 and solution 2 but there is a remarkable difference between both solutions in performance. You can get the performance details from here.

Posted in Solutions, SQL SERVER | Tagged , , , , | 2 Comments »

SQL SERVER HEKATON – List of supportive and non-supportive data types in the memory optimized table

July 30, 2013 by Muhammad Imran

Recently, I was doing research on memory optimized table and I found that most of the data types are supportive and few of them are non supportive as well. But even though few data data types are non supportive there are works around to deal with those data types as well. In my earlier article, I wrote about how to deal with BLOB data types even though they are non supportive. Rest of the non supportive data types work around, I will share in my upcoming posts.

Given below is the list of Supportive data types:

  • bigint
  • int
  • tinyint
  • smallint
  • int
  • real
  • smallmoney
  • money
  • float
  • decimal
  • numeric
  • bit
  • uniqueidentifier
  • smalldatetime
  • datetime
  • binary
  • nchar
  • sysname

Given below is the list of Non-supportive data types:

  • image
  • text
  • sql_variant
  • ntext
  • varbinary(Max)  (Limitation is 8060 bytes per row)
  • varchar(Max)  (Limitation is 8060 bytes per row)
  • timestamp
  • nvarchar(Max) (Limitation is 8060 bytes per row)
  • xml

Let me know if you have work around for the non supportive data types.

Posted in SQL SERVER, Tables | Tagged , , , | 1 Comment »

SQL SERVER 2012 – How to add multiple files in a single filestream filegroup in the existing database.

July 28, 2013 by Muhammad Imran

Adding multiple files in a single filestream filegroup was a very nice & helpful feature shipped with SQL Server 2012. Now, you can easily organize your data under multiple files in the same filestream filegroup. Also, it is not necessary that you place all of them in the same location. You can place them in multiple locations to divide the load as well.

Let me create a sample database to demonstrate. Given below is the script.
Note : Before executing the below script, kindly create a Data folder in C: drive.

USE [master]
GO
CREATE DATABASE [Sample_DB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'Sample_DB',
FILENAME = N'C:\Data\Sample_DB.mdf',
SIZE = 500MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'Filestream1',
FILENAME = N'C:\Data\FileStream1',
MAXSIZE = UNLIMITED
)
LOG ON
(NAME = N'Sample_DB_log'
,FILENAME = N'C:\Data\Sample_DB_log.ldf',
SIZE = 500MB,
MAXSIZE = 1GB,
FILEGROWTH = 10MB
)
GO

Let me add an additional file to the same filegroup. Given below is the script.

ALTER DATABASE [Sample_DB]
ADD FILE
(
NAME = N'Filestream2',
FILENAME = N'C:\Data\Filestream2',
MAXSIZE = 100MB
)TO FILEGROUP [FS]
GO

Lets check if the file has been created or not. Given below is the script.

Select * from sys.master_files where database_id =db_id('Sample_DB')
GO

multiplefilestream1.1

Let me know if you have done this in real world and what was the solution.

Posted in Features, Solutions, SQL SERVER | Tagged , , , , | Leave a Comment »

SQL SERVER – How to insert space before Capital letters – User Defined Function

July 27, 2013 by Muhammad Imran

Today, I  received an email from one of my friends. He was working on a legacy data and he executed a wrong update statement on a table. Basically, he removed the space between the names and then updated the Student name column. For example, if the student name was “Steve Masters”, it became “SteveMasters”.

Now, we need to insert a space before Capital letters to convert the data into its original state. As usual, I started the easy way… the web search but almost all solutions use loop to convert it. I normally avoid loop to perform any operation, unless it is the last option, due to performance issue.

So, I designed the solution via Xquery and inserted space before the capital letters without loop.

Let me create sample to explain it.

USE tempdb
GO
CREATE TABLE Student
(
[Student ID] INT Identity(1,1),
[Student Name] VARCHAR(50)
)
GO
INSERT INTO Student VALUES ('SteveMasters')
INSERT INTO Student VALUES ('DavidOrtiz')
INSERT INTO Student VALUES ('MichaelSeanRay')
INSERT INTO Student VALUES ('StevenSelikoff')
INSERT INTO Student VALUES ('CarolePoland')
INSERT INTO Student VALUES ('BjornRettig')
INSERT INTO Student VALUES ('MichikoOsada')
INSERT INTO Student VALUES ('CarolPhilips')
INSERT INTO Student VALUES ('MeravNetz')
GO
SELECT * FROM Student
GO
--OUTPUT

insertspacebeforecapitalletter1.1

Given below is the user defined function that can insert the space before each capital letter.

USE tempdb
GO
CREATE FUNCTION dbo.[UDF_Space_Before_Capital_Letters]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')+
(CASE WHEN SUBSTRING(@String,Nums.n,1)=
UPPER(SUBSTRING(@String,Nums.n,1)) collate Latin1_General_CS_As
AND Nums.n >1
THEN SPACE(1) + SUBSTRING(@String,Nums.n,1)
ELSE SUBSTRING(@String,Nums.n,1) END)

FROM N4 Nums
WHERE Nums.n<=LEN(@String)

RETURN @RETURN_STRING
END
GO
-- For Example :
USE tempdb
GO
SELECT [Student ID]
,dbo.[UDF_Space_Before_Capital_Letters]([Student Name]) As [Student Name]
from Student
GO

insertspacebeforecapitalletter1.2

Let me know if you come across this issue and its solution.

Posted in Solutions, SQL SERVER | Tagged , , , | 3 Comments »

SQL SERVER – Fix – Error – 11538 – EXECUTE statement failed because its WITH RESULT SETS clause

July 26, 2013 by Muhammad Imran

EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid data type usage in the new feature EXEC WITH RESULT SETS.

Let’s discuss this in detail:
Message Number: 11538

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types.

Error Generation:
Let me create a sample stored procedure to generate this error.

USE tempdb
GO
Create Procedure Usp_Resultset
As
Select 1 as [Number]
GO
EXEC Usp_Resultset
GO
--OUTPUT

errormessage11538.1.1

You can see that the above stored procedure is executed successfully and it has ONE result sets with int data type column.
Lets try to execute it using WITH RESULT SETS clause.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] xml
));
--OUTPUT
Msg 11538, Level 16, State 1, Procedure Usp_Resultset, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘xml’ for column #1 in result set #1, and the corresponding type sent at run time was ‘int’; there is no conversion between the two types.

Ooops…… I am unable to execute it. It returned the error message.

errormessage11538.1.2

Resolution:
Why this error ? Because, the above stored procedure returns one result sets with int data type column but we defined  XML Data type column in WITH RESULT SETS clause. This is true that you can change the data type & column name in result sets, but the issue is, we must look at the data type compatibility as well. And as we know, int & xml data types are not compatible with each other. Lets rewrite the script and define int data type column (or any data type that is compatible with int) in the result sets to avoid this error.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] int
));
--OUTPUT

errormessage11538.1.3

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, make sure you use data type in the result sets that is compatible with the source column data type to avoid such errors discussed above.

Posted in Errors, SQL SERVER | Tagged , , , , , | Leave a Comment »

SQL SERVER – How to pass parameters to an SP using select command (Without variables)

July 25, 2013 by Muhammad Imran

In SQL scripting, it is a common scenario when you need to pass parameters to a stored procedure using select command and we have multiple solutions for this. But before proceeding with the solutions, I need to create a table and a stored procedure to demonstrate the solution.

USE AdventureWorks2012
GO
--Create table
CREATE TABLE dbo.[Department_Audit]
(
DepartmentID smallint,
Name nvarchar(50),
)

GO
--Create Stored Procedure
CREATE PROCEDURE dbo.Usp_Department_Audit
@DepartmentID int,
@Name varchar(50)
AS
BEGIN
INSERT INTO [Department_Audit] VALUES(@DepartmentID,@Name)
END

Solution 1 :
In this solution you need to use extra variable to take the values from SELECT command and insert into a STORED PROCEDURE.
Given below is the solution.

USE AdventureWorks2012
GO
--Declare variables
DECLARE @DepartmentID int
DECLARE @Name varchar(50)

--Set value to variables
SELECT @DepartmentID=[DepartmentID],@Name=[Name]
FROM HumanResources.Department
Where [DepartmentID]=1

--pass variables to stored procedure and execute it.
EXEC dbo.Usp_Department_Audit @DepartmentID,@Name

GO
USE AdventureWorks2012
GO
--Browse the table
SELECT * FROM [Department_Audit]
GO
--OUTPUT

SP_parameter_using_Select.1.1

Solution 2 :
In this solution you do not need to use extra variable to take the values from SELECT command and insert into a STORED PROCEDURE. You can make a dynamic SQL string and insert the records directly into the STORED PROCEDURE using SELECT command. Given below is the solution.

USE AdventureWorks2012
GO
DECLARE @SQL nvarchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC dbo.Usp_Department_Audit '
+ Convert(nvarchar(2),[DepartmentID]) + ', '
+ '''' + [Name] + '''' + '; '
FROM HumanResources.[Department] Where [DepartmentID] =2
EXEC (@SQL)

USE AdventureWorks2012
GO
SELECT * FROM [Department_Audit]
--OUTPUT

SP_parameter_using_Select.1.2

Posted in Solutions, SQL SERVER | Tagged , , , , | Leave a Comment »

SQL SERVER – String comparison as a Boolean in Select statement

July 24, 2013 by Muhammad Imran

Most of the times, we usually maintain the status column of any table in varchar data type. But sometimes due to the requirements, we need to convert this Varchar data type column to Boolean data type to further use it for stats or other purposes as well. But how ?
Before proceeding with the solution, let me first create a sample to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] int,
[Name] varchar(50),
[Status] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'SQL SERVER 2000','Inactive')
INSERT INTO tbl_sample VALUES (2,'SQL SERVER 2005','Active')
INSERT INTO tbl_sample VALUES (3,'SQL SERVER 2008','Active')
INSERT INTO tbl_sample VALUES (4,'SQL SERVER 2012','Active')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

stringcomparision

Given below are the solutions.

Solution 1:
In this solution, we will use CASE statement to convert varchar status to boolean.

--This solution is compatibile with SQL Server 2005 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,(CASE WHEN [STATUS]='Active' THEN 1 ELSE 0 END) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

stringcomparision1.2

Solution 2:
In this solution, we will use IIF statement to convert varchar status to Boolean.

--This solution is compatibile with SQL Server 2012 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,IIF([STATUS]='Active', 1,0) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

stringcomparision1.3

Posted in Solutions, SQL SERVER | Tagged , , , , | Leave a Comment »

« Newer Posts - Older Posts »