Use non-dynamic SQL Server SQL statements to execute dynamic queries.

Use non-dynamic SQL Server SQL statements to execute dynamic queries.

This article mainly tells you about non-dynamic SQL ServerSQL statement execution of dynamic queries. In actual operations, I try to pass a series of values that define the boundary with commas in a stored procedure, to limit the result set. However, whenever I use a variable IN the IN clause, an error message is returned.

Is there a way to complete queries without executing dynamic SQL statements?

I try to pass a series of values that define boundaries with commas in a stored procedure to limit the result set. However, whenever I use a variable IN the IN clause, an error message is returned. Is there a way to complete queries without executing dynamic SQL ServerSQL statements?

Expert answers:

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

When you only have one value, the execution will be fine.

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

However, once you add a comma, the result is 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 identifies that the ManagerID column is an integer, so it automatically converts @ ManagerIDs to a variable.

To solve this problem, you can use dynamic SQL to execute this statement. In this way, you can dynamically create 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 this query, but dynamic SQL is a dangerous element and cannot be used in certain organizations.

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

In the first step, you need to generate an XML field from a string to draw a line.

Declare @ManagerIDs Varchar(100) Set @ManagerIDs = '3,6' DECLARE @XmlStr XML SET @XmlStr = --Start Tag '' + --Replace all commas with an ending tag and start a new tag REPLACE( @ManagerIDs, ',', '') + --End Tag '' 

Then, select the XML value and the result is as follows:

Select @XmlStr 

Now that you have an XML field, we can query it. The result is displayed as follows by line:

SELECT x.ManagerID.value('.', 'INT') AS A FROM @XmlStr.nodes('//ManagerID') x(ManagerID) 

Now, you can use the previous query to limit the results:

SELECT * FROM HumanResources.Employee WHERE ManagerID IN( SELECT x.ManagerID.value('.', 'INT') AS A FROM @XmlStr.nodes('//ManagerID') x(ManagerID) )

Alternatively, you can use Inner Join to limit the results:

SELECT * FROM HumanResources.Employee AS A INNER JOIN (SELECT x.ManagerID.value('.', 'INT') AS ManagerID FROM @XmlStr.nodes('//ManagerID') x(ManagerID)) B ON A.ManagerID = B.ManagerID 

The above content describes the execution of dynamic queries for non-dynamic SQL ServerSQL statements. I hope it will help you in this regard.