列中没有显示动态SQL的DataSet

I have the following SP which works correctly when ran on its own:

我有以下SP,当它自己运行时可以正常工作:

ALTER PROCEDURE [dbo].[sgetInvoiceHeaderDetails]

@InvoiceNo varchar(max)
AS
BEGIN
SET FMTONLY ON;
    declare @sql varchar(max)

set @sql = 'SELECT IH.InvoiceNo, IH.InvoiceDate, IH.InvoiceTime, C.Name, R.Name AS Customer, IH.NetAmount, 
IM.Name AS Item, ID.UnitPrice, ID.Qty, ID.Total, ID.BatchNo
FROM InvoiceHeader AS IH INNER JOIN
InvoiceDetail AS ID ON IH.InvoiceNo = ID.InvoiceNo INNER JOIN
Customer AS C ON IH.CustomerId = C.Id INNER JOIN
Route AS R ON IH.RouteId = R.Id INNER JOIN
ItemMaster AS IM ON ID.ItemMasterId = IM.Id
WHERE IH.InvoiceNo IN ('+@InvoiceNo+')'
print @sql
exec (@sql)
END

The problem I'm having is that when I add a DataSet for a report, it pulls no fields/columns in the Fields section. I'm guessing it's due to the dynamic SQL?

我遇到的问题是,当我为报表添加DataSet时,它会在Fields部分中不提取任何字段/列。我猜它是由于动态SQL?

How can I resolve that?

我怎么解决这个问题?

1 个解决方案

#1

As statet in my comment you should avoid the dynamic approach.

作为我在评论中的状态,你应该避免使用动态方法。

Just to offer you a pure inline solution in SQL have a look at this:

只是为了在SQL中为您提供纯粹的内联解决方案,请看一下:

DECLARE @tbl TABLE(ID INT, Caption VARCHAR(100));
INSERT INTO @tbl VALUES(1,'Test 1'),(2,'Test 2'),(3,'Test 3'),(4,'Test 4'),(5,'Test 5');

DECLARE @WantToGet VARCHAR(100)='1,3,4';

WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@WantToGet,',','</x><x>') + '</x>' AS XML) AS AsXml
)
,SplittedAsList AS
(
    SELECT The.Node.value('.','int') As ID
    FROM Splitted
    CROSS APPLY AsXml.nodes('/x') AS The(Node)
)
SELECT Caption
FROM @tbl AS tbl
INNER JOIN SplittedAsList sal ON sal.ID = tbl.ID;

The string 1,3,4 is splitted as a list. The INNER JOIN at the end is exactly the same as you wanted to achieve with the IN-clause.

字符串1,3,4被拆分为列表。最后的INNER JOIN与您希望使用IN子句完全相同。

This approach you can plcae within a table valued function (make sure to keep this as inline function!). This function is much better reusable everywhere.

这种方法可以在表值函数内进行(确保将其保留为内联函数!)。这个功能在任何地方都可以更好地重复使用。

The second recommandable approach would be the CREATE TYPE, bute this needs more action on application side...

第二个可推荐的方法是CREATE TYPE,这需要在应用程序方面采取更多行动...

#1