Dynamic SQL Query, Cursors, and Exceptions

What is dynamic SQL query and how do you use it in your assignment? Everyone can find an answer to this question in our sample. In one of your projects using dynamic SQL you can solve the tasks of constructing dynamic reports, while in others – data migration. Also, dynamic SQL is indispensable in cases where you want to create or change, or retrieve data or objects, but the values or names come as parameters. If it seems too difficult for you, you may leave your assignment to AssignmentShark.com assignment help online service.

On our blog, we show different examples of how different problems can be solved by our experts. You can use our service for handling homework in different disciplines. With the use of our service, you can get high grades. Our experts use different methods for completing different orders. You can see this by looking through examples on our blog. As you can see from the sample below, we can deal with tasks of any difficulty. If you have a similar task, look at what result you may get!

Dynamic SQL Queries, Cursors, and Exceptions

We need to develop a stored procedure that displays the names of all user tables of the database of the individual subject area in alphabetical order, using the cursor-based system view sysobjects.

Here is an SQL code to implement it:

CREATE PROCEDURE ShowTables
AS
BEGIN
	--VARIABLE FOR WORK @name
	DECLARE @name VARCHAR(100)

	--CREATE CURSOR
	DECLARE db_cursor CURSOR FOR
	SELECT name
	FROM sysobjects
	WHERE TYPE = 'U'
	ORDER BY name 

	--OPEN CURSOR
	OPEN db_cursor

	--THE SAMPLE DATA OF THE FIRST LINE
	FETCH NEXT FROM db_cursor INTO @name

	--WHILE THERE IS DATA IN THE CURSOR - THE WHOLE SAMPLE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @name
		FETCH NEXT FROM db_cursor INTO @name
	END

	--CLOSE THE CURSOR
	CLOSE db_cursor

	--DESTROY THE CURSOR
	DEALLOCATE db_cursor
END

--OVERVIEW PROCEDURE
EXEC ShowTables

And here is the result we obtain:

Now we need to modify the last exercise in SQL code to display the names of all user tables of the database of individual subject areas as well as the number of records in each table in alphabetical order, using the cursor based on the system view sysobjects and grouping query inside the dynamic SQL.

CREATE PROCEDURE ShowTables2
AS
BEGIN
	--VARIABLE FOR WORK @name
	DECLARE @name VARCHAR(100)

	--CREATE CURSOR
	DECLARE db_cursor CURSOR FOR
	SELECT name
	FROM sysobjects
	WHERE TYPE = 'U'
	ORDER BY name 

	--OPEN CURSOR
	OPEN db_cursor

	--THE SAMPLE DATA OF THE FIRST LINE
	FETCH NEXT FROM db_cursor INTO @name

	--WHILE THERE IS DATA IN THE CURSOR - THE WHOLE SAMPLE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @command NVARCHAR(max)
		DECLARE @cnt NVARCHAR(max)
		SET @command = 'SELECT @cntOUT=COUNT(*) FROM ['+@name+']' 
		SET @cnt = 0 
		EXEC sp_executesql @command, N'@cntOUT INT OUTPUT', @cntOUT=@cnt OUTPUT 
		PRINT 'Table '+ @name +' '+@cnt
		FETCH NEXT FROM db_cursor INTO @name
	END


	--CLOSE THE CURSOR
	CLOSE db_cursor

	--DESTROY THE CURSOR
	DEALLOCATE db_cursor
END

--OVERVIEW PROCEDURE
EXEC ShowTables2

Here is the result:

And the last thing we need to do is write a procedure that would take the error code and fix it in the special table.

CREATE PROCEDURE Errors
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'tbl_Exception' AND type = 'U')
		CREATE TABLE tbl_Exception (
		Id NUMERIC(10,0) NOT NULL PRIMARY KEY IDENTITY(1,1),
		ErrorDate DATETIME NOT NULL DEFAULT GETDATE(),
		ServerName NVARCHAR(max) NOT NULL DEFAULT @@SERVERNAME,
		DataBaseName NVARCHAR(max) NOT NULL DEFAULT ORIGINAL_DB_NAME(),
		CurrentLogin NVARCHAR(max) NOT NULL DEFAULT SYSTEM_USER,
		UserName NVARCHAR(max) NOT NULL DEFAULT USER_NAME(),
		Using NVARCHAR(max) NOT NULL DEFAULT APP_NAME(),
		ErrorCode INT NOT NULL,
		ErrorDescription NVARCHAR(max) NOT NULL)

	--DECLARE VARIABLES USED IN ERROR CHECKING
	DECLARE @ErrorNum INT  
	DECLARE @ErrorMessage NVARCHAR(max)
	
	--SAVE @@ErrorNum VALUE IN FIRST LOCAL VARIABLE
	SET @ErrorNum = ERROR_NUMBER()

	--SAVE @@ErrorMessage VALUE IN SECOND LOCAL VARIABLE
	SET @ErrorMessage = ERROR_MESSAGE()

	--IF SECOND TEST VARIABLE CONTAINS NON-ZERO, OVERWRITE VALUE IN SECOND LOCAL VARIABLE
	IF (@ErrorNum <> 0)
		INSERT INTO tbl_Exception(ErrorCode, ErrorDescription) VALUES (@ErrorNum, @ErrorMessage)
END

--CHECK
BEGIN TRY
	SELECT 1/0
END TRY

BEGIN CATCH
	EXEC Errors
END CATCH

--OVERVIEW
SELECT * FROM tbl_Exception

And here is the result: