250+ TOP MCQs on Dynamic SQL and Answers

SQL Server Multiple Choice Questions 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

Answer: d
Clarification: 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

Answer: c
Clarification: 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

Answer: b
Clarification: 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.

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>, …]

Answer: c
Clarification: 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

Answer: b
Clarification: The output type @retType is passed as the second parameter to sp_executesql.

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

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

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

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

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

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

Answer: a
Clarification: 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

Answer: d
Clarification: 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.