Return the result of dynamic sql in stored procedure using entity framework

Question

I want to return the result of sql dynamic in stored procedure. This Stored Procedure is used in entity framework, when I try to create a complex type the procedure returns no columns. Is there any way I can force it to return my values and get the entity framework to receive them? Here is my stored procedure:

CREATE PROCEDURE [dbo].[SP_Check_Alarme]
    (
    @IdUser varchar (50)
    )
AS
BEGIN
DECLARE @CMD as varchar(1000)
SET @cmd='SELECT MAG_Import_' + @IdUser +'.IdElement
        ,MAG_Import_' + @IdUser +'.Code
        ,MAG_Import_' + @IdUser +'.Surface
        ,MAG_Import_' + @IdUser +'.Lieu
        ,MAG_Import_' + @IdUser +'.Adresse
        ,MAG_Import_' + @IdUser +'.CodePostal
        ,MAG_Import_' + @IdUser +'.Ville
        ,MAG_Import_' + @IdUser +'.IdEnseigne
        ,MAG_Import_' + @IdUser +'.LibMagasin
        ,MAG_Import_' + @IdUser +'.TelStandard
        ,MAG_Import_' + @IdUser +'.IdClient
        ,MAG_Import_' + @IdUser +'.Warning
        ,MAG_Import_' + @IdUser +'.Alarme
        ,MAG_Import_' + @IdUser +'.DateCreation
        ,MAG_Import_' + @IdUser +'.AcrCreateur
        ,MAG_Magasin.IdMagasin
        FROM MAG_Import_' + @IdUser +'
        LEFT JOIN MAG_Magasin       
        ON replace(replace(MAG_Import_' + @IdUser +'.Adresse,char(10),'' ''),char(13),'' '') = replace(replace(MAG_Magasin.Adresse,char(10),'' ''),char(13),'' '') 
        AND replace(replace(MAG_Import_' + @IdUser +'.Lieu,char(10),'' ''),char(13),'' '') = replace(replace(MAG_Magasin.Lieu,char(10),'' ''),char(13),'' '')
        AND MAG_Import_' + @IdUser +'.CodePostal = MAG_Magasin.CodePostal
        AND (MAG_Magasin.Suppression IS NULL 
        OR MAG_Magasin.Suppression = 0)'

EXEC(@cmd)

END
GO

When it comes to executing dynamically generated sql string, use sp_executesql instead of EXECUTE. Check this link : msdn.microsoft.com/en-us/library/ms188001.aspx — Paresh J, Commented Dec 7, 2014 at 14:01
When i execute the stored procedure in sql server, i get the result. but when i try to get the result in entity framework using code like (var list = database.SP_Check_Alarme (param);) it does'nt work?. list is equal to -1 — fatys, Commented Dec 7, 2014 at 14:17

fatys · Accepted Answer · 2015-01-05 16:10:55Z

I resolved the problem by using a temporary table. I replace Exec (@cmd) by this code:

DECLARE @Temp_Magasins_Alarme TABLE
    (
        IdElement int,
        Code varchar(15),
        Surface int,
        Place varchar(80),
        Adresse varchar(150),
        PostalCode varchar(10),
        City varchar(80),
        IdEnseigne int,
        LibStore varchar(50),
        Phone varchar(30),
        IdClient int,
        Warning bit,
        Alarme bit,
        DateCreation datetime,
        AcrCreateur char(6),
        IdMagasin int
    )

    INSERT INTO @Temp_Magasins_Alarme (IdElement, Code, Surface, Place, Adresse, PostalCode,
     City , IdEnseigne, LibStore ,Phone, IdClient, Warning , Alarme , DateCreation ,
     AcrCreateur, IdMagasin )

    EXEC sp_executesql @CMD

Collectives™ on Stack Overflow

Return the result of dynamic sql in stored procedure using entity framework

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related