{"id":8648,"date":"2018-04-24T05:55:15","date_gmt":"2018-04-24T05:55:15","guid":{"rendered":"https:\/\/assignment.essayshark.com\/blog\/?p=8648"},"modified":"2022-01-05T11:05:05","modified_gmt":"2022-01-05T11:05:05","slug":"dynamic-sql-query-cursors-and-exceptions","status":"publish","type":"post","link":"https:\/\/assignmentshark.com\/blog\/dynamic-sql-query-cursors-and-exceptions\/","title":{"rendered":"Dynamic SQL Query, Cursors, and Exceptions"},"content":{"rendered":"

What is <\/span><\/i>dynamic SQL query<\/i> 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 \u2013 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<\/a>.<\/span><\/i><\/p>\n

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!<\/span><\/i><\/p><\/blockquote>\n

<\/p>\n

Dynamic SQL Queries, Cursors, and Exceptions<\/b>
\n<\/b><\/h2>\n

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.<\/span><\/p>\n

Here is an SQL code to implement it:<\/span><\/p>\n

[code language=”sql”]
\nCREATE PROCEDURE ShowTables
\nAS
\nBEGIN
\n\t–VARIABLE FOR WORK @name
\n\tDECLARE @name VARCHAR(100)<\/p>\n

\t–CREATE CURSOR
\n\tDECLARE db_cursor CURSOR FOR
\n\tSELECT name
\n\tFROM sysobjects
\n\tWHERE TYPE = ‘U’
\n\tORDER BY name <\/p>\n

\t–OPEN CURSOR
\n\tOPEN db_cursor<\/p>\n

\t–THE SAMPLE DATA OF THE FIRST LINE
\n\tFETCH NEXT FROM db_cursor INTO @name<\/p>\n

\t–WHILE THERE IS DATA IN THE CURSOR – THE WHOLE SAMPLE
\n\tWHILE @@FETCH_STATUS = 0
\n\tBEGIN
\n\t\tPRINT @name
\n\t\tFETCH NEXT FROM db_cursor INTO @name
\n\tEND<\/p>\n

\t–CLOSE THE CURSOR
\n\tCLOSE db_cursor<\/p>\n

\t–DESTROY THE CURSOR
\n\tDEALLOCATE db_cursor
\nEND<\/p>\n

–OVERVIEW PROCEDURE
\nEXEC ShowTables<\/p>\n

[\/code]<\/p>\n

And here is the result we obtain:<\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

[code language=”sql”]
\nCREATE PROCEDURE ShowTables2
\nAS
\nBEGIN
\n\t–VARIABLE FOR WORK @name
\n\tDECLARE @name VARCHAR(100)<\/p>\n

\t–CREATE CURSOR
\n\tDECLARE db_cursor CURSOR FOR
\n\tSELECT name
\n\tFROM sysobjects
\n\tWHERE TYPE = ‘U’
\n\tORDER BY name <\/p>\n

\t–OPEN CURSOR
\n\tOPEN db_cursor<\/p>\n

\t–THE SAMPLE DATA OF THE FIRST LINE
\n\tFETCH NEXT FROM db_cursor INTO @name<\/p>\n

\t–WHILE THERE IS DATA IN THE CURSOR – THE WHOLE SAMPLE
\n\tWHILE @@FETCH_STATUS = 0
\n\tBEGIN
\n\t\tDECLARE @command NVARCHAR(max)
\n\t\tDECLARE @cnt NVARCHAR(max)
\n\t\tSET @command = ‘SELECT @cntOUT=COUNT(*) FROM [‘+@name+’]’
\n\t\tSET @cnt = 0
\n\t\tEXEC sp_executesql @command, N’@cntOUT INT OUTPUT’, @cntOUT=@cnt OUTPUT
\n\t\tPRINT ‘Table ‘+ @name +’ ‘+@cnt
\n\t\tFETCH NEXT FROM db_cursor INTO @name
\n\tEND<\/p>\n

\t–CLOSE THE CURSOR
\n\tCLOSE db_cursor<\/p>\n

\t–DESTROY THE CURSOR
\n\tDEALLOCATE db_cursor
\nEND<\/p>\n

–OVERVIEW PROCEDURE
\nEXEC ShowTables2<\/p>\n

[\/code]<\/p>\n

Here is the result:<\/span><\/p>\n

\"\"<\/a><\/p>\n

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.<\/span><\/p>\n

[code language=”sql”]
\nCREATE PROCEDURE Errors
\nAS
\nBEGIN
\n\tIF NOT EXISTS (SELECT * FROM sysobjects WHERE name = ‘tbl_Exception’ AND type = ‘U’)
\n\t\tCREATE TABLE tbl_Exception (
\n\t\tId NUMERIC(10,0) NOT NULL PRIMARY KEY IDENTITY(1,1),
\n\t\tErrorDate DATETIME NOT NULL DEFAULT GETDATE(),
\n\t\tServerName NVARCHAR(max) NOT NULL DEFAULT @@SERVERNAME,
\n\t\tDataBaseName NVARCHAR(max) NOT NULL DEFAULT ORIGINAL_DB_NAME(),
\n\t\tCurrentLogin NVARCHAR(max) NOT NULL DEFAULT SYSTEM_USER,
\n\t\tUserName NVARCHAR(max) NOT NULL DEFAULT USER_NAME(),
\n\t\tUsing NVARCHAR(max) NOT NULL DEFAULT APP_NAME(),
\n\t\tErrorCode INT NOT NULL,
\n\t\tErrorDescription NVARCHAR(max) NOT NULL)<\/p>\n

\t–DECLARE VARIABLES USED IN ERROR CHECKING
\n\tDECLARE @ErrorNum INT
\n\tDECLARE @ErrorMessage NVARCHAR(max)<\/p>\n

\t–SAVE @@ErrorNum VALUE IN FIRST LOCAL VARIABLE
\n\tSET @ErrorNum = ERROR_NUMBER()<\/p>\n

\t–SAVE @@ErrorMessage VALUE IN SECOND LOCAL VARIABLE
\n\tSET @ErrorMessage = ERROR_MESSAGE()<\/p>\n

\t–IF SECOND TEST VARIABLE CONTAINS NON-ZERO, OVERWRITE VALUE IN SECOND LOCAL VARIABLE
\n\tIF (@ErrorNum <> 0)
\n\t\tINSERT INTO tbl_Exception(ErrorCode, ErrorDescription) VALUES (@ErrorNum, @ErrorMessage)
\nEND<\/p>\n

–CHECK
\nBEGIN TRY
\n\tSELECT 1\/0
\nEND TRY<\/p>\n

BEGIN CATCH
\n\tEXEC Errors
\nEND CATCH<\/p>\n

–OVERVIEW
\nSELECT * FROM tbl_Exception<\/p>\n

[\/code]<\/p>\n

And here is the result:<\/span><\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u2013 data migration. Also, dynamic SQL is indispensable in cases […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53,35],"tags":[],"class_list":["post-8648","post","type-post","status-publish","format-standard","hentry","category-it","category-samples"],"_links":{"self":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/8648","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/comments?post=8648"}],"version-history":[{"count":11,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/8648\/revisions"}],"predecessor-version":[{"id":12863,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/8648\/revisions\/12863"}],"wp:attachment":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/media?parent=8648"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/categories?post=8648"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/tags?post=8648"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}