Executing dynamic queries in SQL Server through non-dynamic SQL statements

Problem:

I tried to pass a series of values delimited by commas in a stored procedure to limit the result set. But whenever I use a variable in the IN clause, I get an error message. Is there a way to complete a query without executing a dynamic SQL statement?

Expert Answer:

There is a way to complete a query without executing a dynamic SQL statement, but first let's explore the problem. I will use the AdventureWorks database in the following examples.

When you have only one value, execution will not be a problem.

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3'
Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)

But once you add commas, the result will be roughly as follows:

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '3,6' to data type int.

This is because the SQL sever that the ManagerID column is an integer, and therefore automatically converts the @managerids into a variable.

To solve this problem, you can use dynamic SQL to execute this statement. In this way, you can dynamically build the entire query before executing it.

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Declare @SQL Varchar(1000)
Set @SQL =
'Select * from HumanResources.Employee
Where ManagerID IN (' + @ManagerIDs + ')'
EXEC (@SQL)

This allows you to execute the query, but dynamic SQL is a dangerous molecule that is not even allowed to be used in certain organizations.

So how do you execute a query without using dynamic SQL? can be implemented through XML.