如何将参数值传递给T-SQL查询

I am using the following T-SQL query in SQL server 2005 (Management Studio IDE):

我在SQL Server 2005(Management Studio IDE)中使用以下T-SQL查询:

DECLARE @id int;
DECLARE @countVal int;
DECLARE @sql nvarchar(max);
SET @id = 1000;
SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = @id';
EXEC (@sql) AT oracleServer -- oracleServer is a lined server to Oracle

I am not sure how to pass the input parameter @id to the EXEC query, and pass the count result out to @countVal. I saw some examples for Microsoft SQL server like:

我不知道如何将输入参数@id传递给EXEC查询,并将计数结果传递给@countVal。我看到了一些Microsoft SQL服务器的例子:

EXEC (@sql, @id = @id)

I tried this for Oracle but I got a statement error:

我为Oracle尝试了这个,但是我收到了一条语句错误:

OLE DB provider "OraOLEDB.Oracle" for linked server "oracleServer" 
returned message "ORA-00936: missing expression"

3 个解决方案

#1

Try this:

EXEC sp_executesql @sql, N'@id int', @id

More info at this great article: http://www.sommarskog.se/dynamic_sql.html

更多信息在这篇伟大的文章:http://www.sommarskog.se/dynamic_sql.html

As for the output, your SELECT needs to look something like this:

至于输出,你的SELECT需要看起来像这样:

SELECT @countVal = COUNT(id) FROM owner.myTable WHERE id = @id

I'm selecting 'id' instead of '*' to avoid pulling unnecessary data...

我选择'id'而不是'*'来避免不必要的数据...

Then your dynamic sql should be something like this:

那你的动态sql应该是这样的:

EXEC sp_executesql @sql, 
                   N'@id int, @countVal int OUTPUT', 
                   @id, 
                   @countVal OUTPUT

This example is adapted from the same article linked above, in the section sp_executesql.

此示例改编自上面链接的相同文章,在sp_executesql部分中。

As for your Oracle error, you will need to find out the exact SQL that sp_executesql is sending to Oracle. If there is a profiler or query log in Oracle, that may help. I have limited experience with Oracle, but that would be the next logical step for troubleshooting your problem.

至于您的Oracle错误,您需要找出sp_executesql发送给Oracle的确切SQL。如果Oracle中有分析器或查询日志,那可能会有所帮助。我对Oracle的经验有限,但这将是解决问题的下一个合乎逻辑的步骤。

#2

I don't know why you are pass id separately.

我不知道你为什么分别通过id。

You could do the following
SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = ' + @id

您可以执行以下SET @sql ='SELECT COUNT(*)FROM owner.myTable WHERE id ='+ @id

#3

The quick and dirty way is to just build the string before using the EXEC statement, however this is not the recommended practice as you may open yourself up to SQL Injection.

快速而肮脏的方法是在使用EXEC语句之前构建字符串,但是这不是推荐的做法,因为您可以自己打开SQL注入。

DECLARE @id int;
DECLARE @countVal int;
DECLARE @sql nvarchar(max);
SET @id = 1000;
SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = ' + @id 
EXEC (@sql) AT oracleServer -- oracleServer is a lined server to Oracle

The correct way to do this is to use the system stored procedure sp_executesql as detailed by magnifico, and recommended by Microsoft in Books Online is:

正确的方法是使用由magnifico详细说明的系统存储过程sp_executesql,Microsoft在联机丛书中推荐的是:

EXEC sp_executesql @sql, N'@id int', @id

#1