1

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

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?

How can I resolve that?

enter image description here

Tom
  • 1,343
  • 1
  • 18
  • 37
  • Change `exec (@sql)` to `exec @sql` – MusicLovingIndianGirl Nov 09 '15 at 09:21
  • Do your tables have enough data? – MusicLovingIndianGirl Nov 09 '15 at 09:26
  • Yes. There is data. When running SP in DB level data returning. – Tom Nov 09 '15 at 09:26
  • 1
    Compared to views, stored procs doesn't have a fixed column list. A SELECT-Statement does define columns. But you cannot SELECT from a Stored Proc. You can only specify the Parameters for the stored proc and see what it returns. – CPMunich Nov 09 '15 at 09:38
  • I'd take away the `print`... Silly question: Are you sure, your Wizard works against the right db? And one more question: Are you sure, that dynamic SQL in this case is the needed option? If you just need a solution for the `IN(... List of InvoiceNumbers ...)` there are better approaches... (and be aware of injection!) – Shnugo Nov 09 '15 at 09:39
  • With out dynamic sql data didn't retrieved – Tom Nov 09 '15 at 09:42
  • I'm quite sure you tried to fill something like `"3,7,12"` into the `IN-clause`. This won't work. Dynamic SQL is a possible, but surely a bad approach. You could read this. http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause – Shnugo Nov 09 '15 at 09:48

1 Answers1

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:

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.

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...

Shnugo
  • 66,100
  • 9
  • 53
  • 114