SQL SERVER TUTORIAL IN DEPTH

January 04, 2014

Get double byte / Single byte data from the table

In NVARchar DataType column we can store both Single byte and Double byte data. Many a times we want to know how many records have Single byte or Double byte data. Let us understand this with an example.

--CREATE TABLE dbo.library
-- (libraryId int, username NVarchar(50))
--GO
--Populate Customer table with single byte and
--double byte CustomerName records
INSERT INTO dbo.library (libraryId, username)
VALUES (1, 'Anurag')

INSERT INTO dbo.library (libraryId, username)
VALUES (2, N'尊敬卿')

INSERT INTO dbo.library (libraryId, username)
VALUES (3, 'Siba')
GO

SELECT *
FROM dbo.library
WHERE username = CAST(username AS VARCHAR(50))

SELECT *
FROM dbo.library
WHERE username != CAST(username AS VARCHAR(50))

------------------------------------------------------------------------
Can we store double byte data type in varchar ?

Lets see
CREATE TABLE dbo.library1
(library1Id int, username Varchar(50))
GO
--Populate Customer table with single byte and
--double byte CustomerName records
INSERT INTO dbo.library1 (library1Id, username)
VALUES (1, 'Anurag')

INSERT INTO dbo.library1 (library1Id, username)
VALUES (2, N'尊敬卿')

INSERT INTO dbo.library1 (library1Id, username)
VALUES (3, 'Siba')
GO

SELECT *
FROM dbo.library1

test 0

January 04, 2014

To Search a text from Stored procedure

alter proc usp_find_word
as
begin

select 'anuragggggggggggg'
declare @varrrrrrr varchar(50)

end

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

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%@varrrrrrr%'

Try this too

SELECT *, OBJECT_DEFINITION(object_id) ObjectDefinition
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

test 0

January 04, 2014

Joining Two Tables without any Common Column between them

create table #temp1
(
id int,
username varchar(50)
)

create table #temp2
(

age int,
sex varchar(1)

)

insert into #temp1
select 1,'anurag'
union all select 2,'abhi'
union all select 3,'binda'
union all select 4,'asu'

insert into #temp2
select 24,'m'
union all select 22,'m'
union all select 25,'m'

select * from #temp1
select * from #temp2

----------------Now we have to combine the two table ..lets try..-----------------

;with cte(id,username,row1)
as
(
select id,username,row_number() over (order by id)
from #temp1
),
cte1(age,sex,row2)
as
(
select age,sex,row_number() over (order by age)
from #temp2
),
cte2
as
(
select c1.id,c1.username,c2.age,c2.sex
from cte c1
left join cte1 c2 on c1.row1=c2.row2
)
select * from cte2
-----------------------------------------------------------------------------------

test 0

January 04, 2014

How to find the text and get to know the stored procedure if you have lot of stored procedures

I have create a stored Procedure. As you know there are number of stored procedure and its tough to search a text if you want to find by text to know which sp has that text. The small technique is mentioned below.

alter proc usp_find_word
as
begin
select 'anuragggggggggggg'
declare @varrrrrrr varchar(50)
end

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

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%@varrrrrrr%'

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

Try this too

SELECT *, OBJECT_DEFINITION(object_id) ObjectDefinition
FROM sys.objects

WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

test 0

January 04, 2014

Error Line,Error Severity, Error State, Error Number,Throw , Raise Error in Sql server

ERROR_LINE

The Error_Line function returns the error Line number from code. This function does not accept any parameters. The Error_Line() function is used to determine the error line which occurred in a try block. The following query returns the line number where the error occurred:

BEGIN TRY

Select 14/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line ' + Cast(ERROR_line() as Varchar )

END CATCH

ERROR NUMBER

Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

This function may be called anywhere within the scope of a CATCH block.

ERROR_NUMBER returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block.

BEGIN TRY

Select 11/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_Number() as Varchar )

END CATCH

ERROR STATE

Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

When called in a CATCH block, returns the state number of the error message that caused the CATCH block to be run.

Returns NULL if called outside the scope of a CATCH block.

ERROR_STATE may be called anywhere within the scope of a CATCH block.

ERROR_STATE returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block

BEGIN TRY

Select 11/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_State() as Varchar )

END CATCH

ERROR SEVERITY

0 to 10	Messages with a severity level of 0 to 10 are informational messages and not actual errors.
11 to 16	Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16.
17	Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.
18	Severity level 18 messages indicate nonfatal internal software problems.
19	Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.
20	Severity level 20 indicates a problem with a statement issued by the current process.
21	Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database.
22	Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.
23	Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands.
24	Severity level 24 indicates a hardware problem.
25	Severity level 25 indicates some type of system error.

BEGIN TRY

Select 11/ 0

END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_Severity() as Varchar )

END CATCH

Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user.

--In SQL Server 2005/2008, if you want to re-throw an error in a catch block of a TRY CATCH statement, you have to use RAISERROR with ERROR_MESSAGE() and ERROR_SEVERITY(). But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.

--We divide a number by zero. A THROW statement is used to raise exceptions; see:

BEGIN TRY DECLARE @VALUE INT SET @VALUE = 12/ 0

END TRY

BEGIN CATCH

throw

END CATCH

--Now press F5 to execute it; the results will be:

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

Raise Error Vs Throw

Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 2005, where as the journey of THROWstatement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.

RAISE ERROR

THROW

Introduced in SQL SERVER 2005

Introduced in SQL SERVER 2012.

It always generates new exception and results in the loss of the original exception details. Below example demonstrates this:

BEGIN TRY

DECLARE @result INT

--Generate divide-by-zero error

SET @result = 55/0

END TRY

BEGIN CATCH

--Get the details of the error

--that invoked the CATCH block

DECLARE

@ErMessage NVARCHAR(2048),

@ErSeverity INT,

@ErState INT

SELECT

@ErMessage = ERROR_MESSAGE(),

@ErSeverity = ERROR_SEVERITY(),

@ErState = ERROR_STATE()

RAISERROR (@ErMessage,

@ErSeverity,

@ErState )

END CATCH

RESULT:
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

NOTE:The actual line number of the code which generated Divided By Zero error here is 4, but the exception message returned by RAISERROR is showiung it as 19. Also the error number corresponding to divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.

To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this:

BEGIN TRY

  DECLARE @result INT

--Generate divide-by-zero error

  SET @result = 55/0

END TRY

BEGIN CATCH

    THROW

END CATCH

RESULT:

RESULT:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.

In the below Batch of statements the PRINT statement after RAISERROR statement will be executed.

BEGIN

 PRINT 'BEFORE RAISERROR'

 RAISERROR('RAISERROR TEST',16,1)

 PRINT 'AFTER RAISERROR'

END

RESULT:

BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 3
RAISERROR TEST

AFTER RAISERROR

In the below Batch of statements the PRINT statement after THROW statement will not executed.

BEGIN

    PRINT 'BEFORE THROW';

    THROW 50000,'THROW TEST',1

    PRINT 'AFTER THROW'

END

RESULT:
BEFORE THROW
Msg 50000, Level 16, State 1, Line 3
THROW TEST

BEGIN TRY

  DECLARE @RESULT INT = 55/0    

END TRY

BEGIN CATCH

  PRINT 'BEFORE THROW';

  THROW;

  PRINT 'AFTER THROW'

END CATCH

  PRINT 'AFTER CATCH'

RESULT:

BEFORE THROW
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

Can set the severity of error

There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)

The statement before the THROW statement is not followed by the semicolon (;) statement terminator.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

CREATE PROCEDURE dbo.TryCatchTest

AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

test 0