Detailed explanation of using non-dynamic SQL Server SQL statements to execute dynamic queries

This article mainly tells you about the non-dynamic SQL ServerSQL statement to execute dynamic queries. In actual operation, I tried to pass a series of values ​​demarcated by commas in a stored procedure. to limit the result set. But whenever I use the variable in the IN clause, I get the error message

This article mainly tells you about the non-dynamic SQL ServerSQL statement execution dynamics Query, in actual operation, 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 an IN clause, I get an error message.

Is there a way to complete the query without executing a dynamic SQL statement?

I tried passing a series of comma-delimited values ​​in a stored procedure to limit the results set. But whenever I use a variable in an IN clause, I get an error message. Is there a way to complete the query without executing dynamic SQL ServerSQL statements?

Expert answer:

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

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

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

But once you add a comma, 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 SQL Sever recognizes that the ManagerID column is an integer, so it will automatically convert @ManagerIDs into variables.

In order to solve this problem, you can use dynamic SQL to execute this statement. 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 element and is not even allowed to be used in some specific organizations.

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

In the first step, you need to generate an XML field from a string delimited by commas.

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 
''

Next, select this XML value, the results are displayed as follows:

Select @XmlStr

Now that you have an XML field, we can query it, and the results are displayed row by row as follows:

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 related content is to execute non-dynamic SQL ServerSQL statements The description of dynamic query, I hope it will bring you some help in this regard.

The above is the detailed content of Detailed explanation of using non-dynamic SQL Server SQL statements to execute dynamic queries. For more information, please follow other related articles on the PHP Chinese website!