This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Dynamic SQL”.
1. Which of the following is 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
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
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
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.
4. The calling syntax for sp_executesql is as follows:
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
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
Explanation: 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
View Answer
Explanation: 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
View Answer
Explanation: 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:
a) SP_EXECUTE(@SQLStatement)
b) EXEC_SQL(@SQLStatement)
c) EXECUTE(@SQLStatement)
d) All of the mentioned
View Answer
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
Explanation: Stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a 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
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.