SQL Server Questions and Answers – Dynamic SQL

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Dynamic SQL”.

1. Which of the following is a way to build dynamic sql statements?
a) Writing a query with parameters
b) Using sp_executesql
c) Using EXEC
d) All of the mentioned
View Answer

Answer: d
Explanation: SQL Server offer three ways of running a dynamically built SQL statement.

2. Point out the correct statement.
a) ODBC has the call ExecDirect
b) Few database systems provide the facilities for running SQL code directly against the database engine
c) The SQL code is not stored in the source program, but rather it is generated based on user input
d) None of the mentioned
View Answer

Answer: c
Explanation: The SQL code can include determining not only what objects are involved, but also the filter criteria and other qualifiers that define the set of data being acted on.

3. Dynamic SQL Statements in SQL Server can be easily built using ________________
a) Cursor
b) Stored procedure
c) Function
d) All of the mentioned
View Answer

Answer: b
Explanation: Dynamic SQL is a term used to mean SQL code that is generated programmatically (in part or fully) by your program before it is executed.
advertisement
advertisement

4. Which of the following is a calling syntax for sp_executesql?
a) sp_execute <@stmt> [<@param1 data_type>,<@param2 data_type>, …]
b) sp_sql <@stmt> [<@param1 data_type>,<@param2 data_type>, …]
c) sp_executesql <@stmt> [<@param1 data_type>,<@param2 data_type>, …]
d) sp_executesql [<@param1 data_type>,<@param2 data_type>, …]
View Answer

Answer: c
Explanation: Using sp_executesql to run dynamic statements gives us a couple advantages over EXEC that are worth noting.

5. Point out the wrong statement.
a) The @stmt parameter in sp_executesql is a Unicode string containing valid SQL commands
b) The input type @Type is passed as the first parameter to sp_executesql
c) We can specify the parameters for both input and output in sp_executesql
d) None of the mentioned
View Answer

Answer: b
Explanation: The output type @retType is passed as the second parameter to sp_executesql.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

6. Below Code is procedure for dynamic SQL using ___________ parameter.

CREATE PROCEDURE GetArticle 
               @ArticleID INT 
        AS 
        SELECT ArticleTitle, ArticleBody FROM 
        Articles
        WHERE ArticleID = @ArticleID GO -

a) input and output
b) input
c) output
d) all of the mentioned
View Answer

Answer: b
Explanation: The output of an SP could also be returned in an output parameter.
advertisement

7. The Dynamic SQL Queries in a variable are __________ until they are executed.
a) Compiled
b) Parsed
c) Checked for errors
d) All of the mentioned
View Answer

Answer: d
Explanation: A Dynamic SQL is needed when we need to retrieve a set of records based on different search parameters.
advertisement

8. The basic syntax for using EXECUTE command is ___________
a) SP_EXECUTE(@SQLStatement)
b) EXEC_SQL(@SQLStatement)
c) EXECUTE(@SQLStatement)
d) All of the mentioned
View Answer

Answer: c
Explanation: EXECUTE command is demonstrated using :

SET @SQLQuery = 'SELECT * FROM tblEmployees
WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10))
EXECUTE(@SQLQuery)

.

9. Which of the following is a disadvantage of dynamic SQL?
a) Stored procedure can not cache the execution plan for this dynamic query
b) Stored procedure can cache the execution plan for this dynamic query
c) Flexibility in your code that you can not get with standard SQL
d) All of the mentioned
View Answer

Answer: a
Explanation: Stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose the performance boost that you usually gain with stored procedures.

10. Which of the stored procedure used for dynamic SQL is prone to attacks?
a) xp_executesql
b) executesql
c) sp_execute
d) sp_executesql
View Answer

Answer: d
Explanation: sp_executesql executes a Transact-SQL statement or batch that can be reused many times or one that has been built dynamically. Run time-compiled Transact-SQL statements can expose applications to malicious attacks.

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.