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:
[code language=”sql”]
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
[/code]
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.
[code language=”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
[/code]
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.
[code language=”sql”]
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
[/code]
And here is the result:
