Dynamic request SQL with sp_executesql to ask by Entity Framework

Question

I have a table FicheArticle with a XML column ART_Fiche.

I created a stored procedure to let me search in that XML column:

DECLARE @statement nvarchar(max) = N'SELECT ART_UniqueID FROM FicheArticle 
    CROSS APPLY ART_Fiche.nodes('''+@XPath+''') as TT(c)'

execute sp_executesql @statement

When I execute this on SQL Server I get 2 responses. First the list I want and the second a "return Value" (I suppose it's to say execute is good or not)

I call this one from my web application with Linq + Entity Framework like that

Dim a = LesDatas.GetArticleFiltred("//Product/ProductFeature[@CategoryFeature_ID=""6137"" and @Value=""DDR2""]")

I get an integer in return... I want the list, not the integer.

With all I read, I think it's on sp_executesql I don't know how specify the output. All sample are on 1 integer (count(*), MAX ...) but don't find for return rows and I don't arrive to adapt others samples.

Thanks for your help

---

UPDATE 1

I try too

exec ('SELECT ART_UniqueID FROM SHOP_FicheArticle INNER JOIN SHOP_Article ON SHOP_FicheArticle.ART_IDIcecat = SHOP_Article.ART_IDIcecat CROSS APPLY ART_FicheIcecat.nodes('''+@XPath+''') as TT(c)')

And on vb side go on models explorer -> button Obtain informations of column but always in response : no column in return

Update 2

I try solution here to force Entity to have good return

IF 1 = 2 BEGIN
SELECT
    cast(null as nvarchar(500))  as SHOP_FicheArticle
WHERE
    1 = 2  
END

Now Entity have the good return, but When I execute, it fail : not same type of return... Why I'm not so surprise... I see problem with EF and dynamic request :here I try to force in sql side : SET FMTONLY OFF But same problem

The data reader is incompatible with the specified 'ExtranetModel.GetArticleFiltred_Result'. A member of the type, 'SHOP_FicheArticle', does not have a corresponding column in the data reader with the same name.

Answer 1

I don't know if this helps you, but you might pass dedicated values into your XPath. But you cannot pass in the XPath itself.

If you deal with differing XPath expressions you will have to go via dynamic SQL, but if you just want to pass in search values you could go with this:

Set the variable @Test to x or y and you'll get the right row in return.

DECLARE @SomeTable TABLE(ID INT, SomeXML XML);
INSERT INTO @SomeTable VALUES
 (1,'<root><a test="x">x</a></root>')
,(2,'<root><a test="y">y</a></root>');

DECLARE @Test VARCHAR(10)='y';

SELECT st.*
FROM @SomeTable AS st
CROSS APPLY st.SomeXML.nodes('/root/a[@test=sql:variable("@Test")]') AS A(B)

It is absolutely easy to create an inline TVF from this:

CREATE TABLE SomeTable (ID INT, SomeXML XML);
INSERT INTO SomeTable VALUES
 (1,'<root><a test="x">x</a></root>')
,(2,'<root><a test="y">y</a></root>');
GO

CREATE FUNCTION dbo.TestFunction(@Test VARCHAR(10))
RETURNS TABLE
AS
RETURN
SELECT st.*
FROM SomeTable AS st
CROSS APPLY st.SomeXML.nodes('/root/a[@test=sql:variable("@Test")]') AS A(B)
;
GO

SELECT * FROM dbo.TestFunction('y');
GO

DROP FUNCTION dbo.TestFunction;
DROP TABLE SomeTable; 

Answer 2

Like said Shnugo ... Update 2 was the good answer correcting

IF 1 = 2 BEGIN
SELECT
    cast(null as nvarchar(112))  as ART_UniqueID
WHERE
    1 = 2  
END

... I try too with success:

option 2

Create new stored procedure Who call first and with a beautifull declare @tbl... insert into @tbl exec(FirstProc @Param) select ART_UniqueID from @tbl

option 3

In one stored procedure

DECLARE @tblAmount  TABLE(ART_UniqueID NVARCHAR(112))
insert into @tblAmount
exec ('SELECT ART_UniqueID FROM SHOP_FicheArticle INNER JOIN SHOP_Article ON SHOP_FicheArticle.ART_IDIcecat = SHOP_Article.ART_IDIcecat CROSS APPLY ART_FicheIcecat.nodes('''+@XPath+''') as TT(c)')
SELECT * FROM @tblAmount

The problem was it's complete string request then EF can't detect properly the structure.

Option 4

Fot this case where I return only one field

exec ('SELECT ART_UniqueID FROM SHOP_FicheArticle INNER JOIN SHOP_Article ON SHOP_FicheArticle.ART_IDIcecat = SHOP_Article.ART_IDIcecat CROSS APPLY ART_FicheIcecat.nodes('''+@XPath+''') as TT(c)')

And on EF : Force result type like scalar of string.