开发者

How using sp_executesql with parameters

开发者 https://www.devze.com 2023-04-03 08:45 出处:网络
I wrote a query: DECLARE @Col_Select NVARCHAR(10); SET @Col_Select = N\'[od.orderID]\'; DECLARE @Query NVARCHAR(4000);

I wrote a query:

DECLARE @Col_Select NVARCHAR(10);
SET @Col_Select = N'[od.orderID]';

DECLARE @Query NVARCHAR(4000);
SET @Query = N'SELECT quantity, COUNT(o.orderID) FROM orders o LEFT OUTER JOIN [order details] od ON o.orderid = @Col_Select group by od.quantity';

EXEC sp_executesql @Query,@Col_Select

What is wrong with my query?

I get th开发者_如何学编程is error:

Incorrect syntax near 'od.orderI)SELECT quantity,COUNT(o.orderID) FROM orders o LEFT OUTER JOIN [order details'.


Because you are building a dynamic query where the fields change, this can't be done by normal parameters. You'll have to use the parameter to build up the string of the query - be very careful if you can supply @Col_Select from user input to avoid SQL Injection attacks!

SET @Query=N'SELECT quantity,COUNT(o.orderID) FROM orders o LEFT OUTER JOIN [order details] od ON o.orderid = ' + @Col_Select + ' group by od.quantity';
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号