T-SQL:字符串concat问题 [英] T-SQL: issue with string concat

问题描述

我有一组名为GreenLine1.mp3，GreenLine2.mp3 e.t.c的音频文件.我将它们写为BLOB表(我使用MS SQL Server'08)，这是我的sql请求:

I have a set of audio files with names GreenLine1.mp3, GreenLine2.mp3 e.t.c. I'm going to write them into a table as BLOB (I use MS SQL Server'08), here's my sql request:

DECLARE @aud AS VARBINARY(MAX)
DECLARE @num AS INT    
-- Load the audio data
SET @num=1
WHILE (@num<38)
BEGIN;

SELECT @aud = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            'C:\Users\Ilya\folder\GreenLine' + CAST(@num AS VARCHAR) + '.mp3',
            SINGLE_BLOB ) AS x

-- Insert the data to the table          
INSERT INTO Mb2.dbo.Audios (Id, [Content])
SELECT NEWID(), @aud
SET @num = @num + 1
END;

我有一个错误:'+'附近的语法不正确，期望为'，'或')'.

I have an error: Incorrect syntax near '+', expecting ',' or ')'.

如果我尝试写

'C:\ Users \ Ilya \ folder \ GreenLine'+ CAST(@num AS VARCHAR)+'.mp3'

'C:\Users\Ilya\folder\GreenLine' + CAST(@num AS VARCHAR) + '.mp3'

放入变量并将其放在BULK之后，我在@variable，预期的STRING或TEXT_LEX附近得到错误的语法

into a variable and put it after BULK, I get Incorrect syntax near @variable, expected STRING, or TEXT_LEX

推荐答案

您不能参数化或连接

You can't parametrise or concatenate the parameters of OPENROWSET. It is constant values only.

您必须使用动态SQL和临时表，或考虑使用SSIS作为示例

You'll have to use dynamic SQL and a temp table, or consider using SSIS for example

这篇关于T-SQL:字符串concat问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！