How to Use Variables in OPENROWSET Queries?

Using a Variable in OPENROWSET Query

Attempts to use an expression in an OPENROWSET query often result in an error such as "Incorrect syntax near ' '" This is because OPENROWSET does not support the use of expressions for specifying parameters.

To resolve this issue, use dynamic SQL to create the OPENROWSET query string with the parameter value embedded. For example, the following code demonstrates how to use dynamic SQL to pass an integer parameter named @ID to the stored procedure sProc1 in the OPENROWSET query:

DECLARE @ID int
DECLARE @sql nvarchar(max)

SET @ID = 1
SET @sql = 'SELECT *
FROM OPENROWSET(
    ''SQLNCLI'',
    ''DRIVER={SQL Server};'',
    ''EXEC dbo.sProc1 @ID = ' + CAST(@ID AS VARCHAR(10)) + ''')'

-- Print @sql
PRINT @sql

-- Execute dynamic SQL
EXEC(@sql)

In this example, the value of the @ID variable is dynamically inserted into the OPENROWSET query string using the CAST function to convert the integer value to a VARCHAR(10). The resulting dynamic SQL statement is then printed to the console and executed.

The above is the detailed content of How to Use Variables in OPENROWSET Queries?. For more information, please follow other related articles on the PHP Chinese website!