SQL Server: Scalar Input Values

For stored procedure to query multiple tables in the database using a complex join, and return the query results to the caller DBA/Developer should create a stored procedure that accepts scalar input values and returns a result set.
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. For example, DBA/Developer might use the following CREATE PROCEDURE statement to return a single result set to the caller:

CREATE PROCEDURE usp_GetDepositList (@DepositID int)

AS SELECT DepositID, FirstName, LastName, Amount
FROM Deposit
WHERE DepositID = @DepositID
ORDER BY Amount
DESC;

DBA/Developer 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. DBA/Developer should not create a stored procedure that accepts multiple scalar input parameters and returns data using an OUTPUT parameter. DBA/Developer would use an OUTPUT parameter if DBA/Developer wanted to return only a few values rather than the entire query result.