CODEWITHSUNDEEP
sql-serverentity-frameworkstored-procedurescomplextype
Amit Kumar
Amit Kumar

Reputation: 645

Create complex type of SP in EF which uses exec sp_executesql

I have a stored procedure something like this

CREATE PROCEDURE [dbo].[GetUserData]
    (@departmentId varchar(max))
AS
BEGIN
    Set FMTONLY off

    declare @sql nvarchar(Max)
    Set @sql = 'select * from Users where DepartmentId in ('+@departmentId+'))'

    exec sp_executesql @sql

When I try to add this stored procedure to EF and create a complex type out of it, I get an error:

Selected procedure or function returns no column

SET FMTONLYoff also doesn't helps

This table has 25 columns and creating complex type manually will be a burden.

Upvotes: 0

Views: 428

Answers (1)

Tony Rogerson
Tony Rogerson

Reputation: 589

Whatever you do - do not write code like that, a UK company got a SQL injection attack that has cost it around £35 million.

In your code I can write any injection I want into @departmentid parameter and you'll just execute it for me!

To answer your question - it's probably because there isn't actually a result from that stored procedure until it is actually executed.

Upvotes: 1

Related Questions