Home » Microsoft » MB6-886 » What should you do?
You are a database developer on an instance of SQL Server 2008. Your Sales database contains sales-related data for your company.
You need to create a stored procedure that will be used by several in-house applications. Your development team will implement the stored procedure using DataReader objects. The stored procedure must query multiple tables in the Sales database using a complex join, and return the query results to the caller.
What should you do?
A. Create a stored procedure that accepts scalar input values and returns a result set.
B. Create a CLR stored procedure that accepts multiple scalar input parameters.
C. Create a stored procedure that accepts multiple scalar input parameters and returns data using an OUTPUT parameter.
D. Create a stored procedure that accepts a table-valued input parameter and returns a table-valued output parameter.
Correct Answer: A
Explanation/Reference:
The result set that is returned can then be accessed by the in-house applications. A stored procedure can return an entire result set by including a SELECT statement within the stored procedure.
You should not create a CLR stored procedure that accepts multiple scalar input parameters. There is no need to use a CLR stored procedure in this scenario. A CLR stored procedure is a stored procedure created using a .NET Framework language and then registered with the database so it can be called from Transact-SQL. CLR stored procedures should be used to implement complex logic or functionality that is not inherently supported in the database, such as logic that requires the use of language constructs available in a .NET Framework language. Although a CLR stored procedure could work in this scenario, the option of creating a CLR stored procedure that accepts multiple scalar input parameters is incorrect because this option does not return a result set.
You should not create a stored procedure that accepts multiple scalar input parameters and returns data using an OUTPUT parameter. You would use an OUTPUT parameter if you wanted to return only a few values rather than the entire query result.
