SQL Server : Dynamic Query in Stored Procedure in

Question

I tried to write dynamic query in SQL Server. However I get warning like below,

The Selected stored procedure or function returns no columns

I have been trying to achieve this however it still does not run.

Where did I miss ?

ALTER PROCEDURE [dbo].[S_ProjeGetir1]
@ID          int=0,
@AktifMi     int,
@ProjeFirma  int,
@Icinde      int,
@AramaText   Varchar(500),
@Arasinda1   Varchar(50),
@Arasinda2   Varchar(50)
AS
BEGIN 
 Create Table #TempTable 
(Sonuc int   
,ID int           
,FirmaID  int
,PrismProjeID  int
,ProjeAdi  nvarchar(150)
,RgID  uniqueidentifier
,HaritaEnlem  nvarchar(25)
,HaritaBoylam  nvarchar(25)
,ProjeTeslimTarihi  datetime
,HemenTeslimMi  bit
,Adres  nvarchar(250)
,AdresUlkeID  int
,AdresILID  int
,AdresILceID  int
,AdresSemtID  int
,KonutSayisi  int
,LansmanTarihi  datetime
,OfisSayisi  int
,MagazaSayisi  int
,BlokSayisi  int
,YesilAlan  int
,KrediyeUygunMu  bit
,AktifMi  bit
,UcBoyutVarMi  bit
,UlkeAdi  nvarchar(150)
,Sehir  nvarchar(50)
,Ilce  nvarchar(50)
,Semt  nvarchar(50)
,FirmaAdi  nvarchar(100)
,StokSonGuncellemeTarihi  datetime)

   Declare @SqlText varchar(8000),
           @AktifPasif bit
   Set @AktifPasif=Case When @AktifMi=0 Then 1  When @AktifMi=1 Then 0 End
   SET NOCOUNT ON
   BEGIN TRY

        Set @SqlText='  SELECT 1 Sonuc ,[ID],[FirmaID] ,[PrismProjeID],[ProjeAdi]    ,[RgID]      ,[HaritaEnlem] ,[HaritaBoylam]  ,[ProjeTeslimTarihi]
                                       ,[HemenTeslimMi],[Adres]       ,[AdresUlkeID] ,[AdresILID] ,[AdresILceID] ,[AdresSemtID]   ,[KonutSayisi] 
                                       ,[LansmanTarihi],[OfisSayisi]  ,[MagazaSayisi],[BlokSayisi],[YesilAlan]   ,[KrediyeUygunMu],[AktifMi]  
                                       ,[UcBoyutVarMi] ,[UlkeAdi]     ,[Sehir]       ,[Ilce]      ,[Semt]
                                       ,[FirmaAdi]     ,[StokSonGuncellemeTarihi] 
                        FROM [dbo].[V_Proje] AS P WITH (NOLOCK) 
                        WHERE 1=1 '
        If @ID>0 Set --ID ye Göre İlgili Kayıdı almak için
           @SqlText=@SqlText +' and ID=' +cast(@ID as Varchar(50))
        Else
        Begin
            --Aktif ise veya Pasif ise            
            if @AktifMi=0 or @AktifMi=1
            Begin
              Set @SqlText=@SqlText +' and AktifMi='+cast(@AktifPasif as varchar(50))
            End
            --Bütün Alanları sorgulamak için
              Set @SqlText=@SqlText +' and CASE WHEN '+cast(@ProjeFirma as varchar(50))+'=1 THEN Upper(ProjeAdi)
                                                WHEN '+cast(@ProjeFirma as varchar(50))+'=0 THEN Upper(FirmaAdi)
                                                WHEN '+cast(@ProjeFirma as varchar(50))+'=2 THEN Upper(HaritaEnlem)
                                                WHEN '+cast(@ProjeFirma as varchar(50))+'=3 THEN Upper(HaritaBoylam)
                                                WHEN '+cast(@ProjeFirma as varchar(50))+'=4 THEN Upper(Adres)'  

            If @Icinde=0--İçinde                                              
              Set @SqlText=@SqlText +' END Like ''%'+@AramaText+'%''  '
            If @Icinde=1--İle Başlayan
              Set @SqlText=@SqlText +' END Like '''+@AramaText+'%''  '
            If @Icinde=2--Arasında
              Set @SqlText=@SqlText +' END between '''+ @Arasinda1+''' and '''+@Arasinda2+''''
            --select @SQLTEXT
        End
        exec('insert into #TempTable ' + @SqlText)



        Select     Sonuc, [ID],[FirmaID],[PrismProjeID],[ProjeAdi] ,[RgID]          ,[HaritaEnlem],[HaritaBoylam],[ProjeTeslimTarihi],[HemenTeslimMi]
                        ,[Adres]       ,[AdresUlkeID] ,[AdresILID],[AdresILceID]   ,[AdresSemtID],[KonutSayisi] ,[LansmanTarihi]    ,[OfisSayisi]
                        ,[MagazaSayisi],[BlokSayisi]  ,[YesilAlan],[KrediyeUygunMu],[AktifMi]    ,[UcBoyutVarMi],[UlkeAdi],[Sehir]  ,[Ilce],[Semt]
                        ,[FirmaAdi]    ,[StokSonGuncellemeTarihi] 
        From #TempTable AS T  WITH (NOLOCK) 

   END TRY
   BEGIN CATCH
       SELECT -1 AS Sonuc -- EXCEPTION
   END CATCH
   SET NOCOUNT OFF
END

Any help will be appreciated.

Thanks

possible duplicate of [EF4 - The selected stored procedure returns no columns](http://stackoverflow.com/questions/7128747/ef4-the-selected-stored-procedure-returns-no-columns) — Ed Harper, Nov 09 '13 at 11:09

score 0 · Accepted Answer · answered Nov 11 '13 at 08:25

I am not sure if what i am going to tell is going to work for EF, but i had a similar case in LINQ to SQL classes.

Backup the code of your Stored Procedure

Create a single SELECT statement with appropriate values, in your Stored Procedure like the following:

 ALTER PROCEDURE [dbo].[S_ProjeGetir1]
     @ID          int=0,
     @AktifMi     int,
     @ProjeFirma  int,
     @Icinde      int,
     @AramaText   Varchar(500),
     @Arasinda1   Varchar(50),
     @Arasinda2   Varchar(50)
 AS
 BEGIN

     Select CONVERT(VARCHAR(30),'') AS Sonuc, -- Make sure to convert to what your 
            0 AS [ID],
            0 AS [FirmaID],
            0 AS [PrismProjeID],
            ...
 END

Import this Stored Procedure in EF
If all goes well, restore the backed up code in the Stored Procedure.

SQL Server : Dynamic Query in Stored Procedure in

1 Answers1