Stored procedure returns no columns

Question

I have the following stored procedure :

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 ALTER PROCEDURE [dbo].[SearchMediaTitles] 

   @query varchar(50),
   @limit int = 6,
   @userId int
 AS
 SET FMTONLY OFF
 BEGIN

 declare @searchString varchar(52)

 set @searchString = '"' + @query +'*"'
 IF @userId!=NULL
 SELECT TOP (@limit) ID, Title from Media where CONTAINS([Title], @searchString)
 AND ID IN
  (
  SELECT FavoriteMedia_ID
  FROM dbo.UserMedia
  WHERE UserMedia_Media_ID=@userId
  )
 ELSE
 SELECT TOP (@limit) ID, Title from Media where CONTAINS([Title], @searchString)

 END

In Entity Framework when I try to map it at function import for a complex type it says

the selected stored procedure returns no columns

I've read about this on the internet and I found out I need to set SET FMTONLY OFF, but as you can see it didn't work.

Any ideas?

EDIT:

I've changed SELECT to * and it return an empty result. I think it's related to problem described above

Bit of a bodge but what if you just temporarily change the definition to SELECT ID, Title from Media so it is a simple straight forward proc with no conditional logic then do the entity framework mapping then put your original code back as both paths look like they will return a resultset of that shape. — Martin Smith
– Martin Smith, Commented Dec 19, 2012 at 22:23
Did you get rid of the SET FMTONLY statement? Also is this something specific to that stored proc or do you get this for all stored procedures? — Martin Smith
– Martin Smith, Commented Dec 19, 2012 at 22:42
Yes. I tried to use that because of this problem. I've read on the internet about it, but I couldn't resolve it — Cosmin
– Cosmin, Commented Dec 19, 2012 at 23:03
And you definitely changed the definition to exactly SELECT ID, Title from Media you didn't leave TOP(@limit) in there? According to this thread that might mess things up as EF will pass NULL for all parameters. And that will cause an error for TOP — Martin Smith
– Martin Smith, Commented Dec 19, 2012 at 23:11

Cosmin · Accepted Answer · 2012-12-20 09:06:56Z

3

For everyone who has the same problem, this is what I've done.

First I modified the stored procedure to a simple one like this :

  SELECT  [column1],[column2] FROM [table]

I imported it in Entity Framework, created the complex type and mapping properties

Then I changed it back and updated the model and now it works. I think Entity Framework has some problems with complex stored procedures. From what I read it can't get results for dynamic stored procedures or for a stored procedure which use temp tables.

PS: When I call it with Ajax, I call it using data.column1 and data.column2. If I use data.value' it returnsundefined` .

It's kinda weird.

EDIT: It's like Martin said but I don't know why it didn't worked in the first place ! Thank you btw !

answered Dec 20, 2012 at 9:06

Cosmin

2,2141 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francis P · Accepted Answer · 2012-12-19 23:59:39Z

1

I guess you are missing the single quotes on your prefix_term. So this:

set @searchString = '"' + @query +'*"'

Should be:

set @searchString = CHAR(39) + '"' + @query +'*"' + CHAR(39)

answered Dec 19, 2012 at 23:59

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

Collectives™ on Stack Overflow

Stored procedure returns no columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related