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
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
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,这需要在应用程序方面采取更多行动...