OPENROWSET不接受变量？

Question

Server 2008 R2

这不管用：

IF @exists = 1 
    SELECT
        @pic = pImage.BulkColumn
    FROM
        OPENROWSET(BULK @fn, SINGLE_BLOB) AS pImage
ELSE
    SET @pic = NULL

除非我将@fn ( NVARCHAR)替换为像N'C:\path\to\some.jpg'.这样的硬编码字符串但一定有办法在那里使用变量，对吧？

Answer 1

我建议使用动态sql，如下所示：Using a Variable in OPENROWSET Query

就你的情况而言，会是这样的：

DECLARE @sql VARCHAR(MAX)
IF @exists = 1 
SET @sql = 'SELECT pImage.BulkColumn FROM OPENROWSET(BULK ''' + @fn + ''', SINGLE_BLOB) AS pImage'

sp_execute @sql
--Here you would need to add something to retreive @pic (I didn't add it since I don't know what @pic is. You can guide you with this link https://msdn.microsoft.com/en-us/library/ms188001.aspx
ELSE 
SET @pic = NULL