locked
Create Complex Type from stored procedure RRS feed

  • Question

  • User-2012457684 posted

    I am trying to add a search function into my MVC APP.   Generally when stored procedures are added where rows of data is returned a complex type is created by visual studio.   I updated my model with this stored procedure that does a search of resumes stored in a database with full text indexing, then clicked on build

    CREATE PROCEDURE SearchResumes
    
    @SearchFor nvarchar(500),
    @AdminID nvarchar(15)
    
    AS
    
    SET NOCOUNT ON
    
    DECLARE @RoleID int 
    
    SELECT 
    	@RoleID = RoleID
    FROM
    	dbo.AdminUsers 
    WHERE
    	AdminID = @AdminID
    
    IF @RoleID < 4
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    IF @RoleID = 4  -- Divisional Manager
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			J.DivisionID in(SELECT DivisionID FROM dbo.DivisionManager WHERE AdminID = @AdminID)
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    IF @RoleID = 5  -- District Manager
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			App.LocationID in(SELECT LocationID FROM dbo.Zone_Locations WHERE ZoneID IN (SELECT ZoneID FROM dbo.Region_Zones WHERE RegionID IN (SELECT RegionID FROM dbo.District_Regions WHERE DistrictID IN(SELECT DistrictID FROM dbo.DistrictManager WHERE AdminID = @AdminID))))
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    IF @RoleID = 6  -- Region Manager
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			App.LocationID in(SELECT LocationID FROM dbo.Zone_Locations WHERE ZoneID IN (SELECT ZoneID FROM dbo.Region_Zones WHERE RegionID IN (SELECT RegionID FROM dbo.RegionManager WHERE AdminID = @AdminID)))
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    IF @RoleID = 7  -- Zone Manager
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			App.LocationID in(SELECT LocationID FROM dbo.Zone_Locations WHERE ZoneID IN (SELECT ZoneID FROM dbo.ZoneManager WHERE AdminID = @AdminID))
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    IF @RoleID = 8  -- Department Manager
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			J.DepartmentID in(SELECT DepartmentID FROM dbo.DepartmentManager WHERE AdminID = @AdminID)
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    IF @RoleID = 9  -- Department-Location Manager in charge of department at a single location
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			J.DepartmentID in(SELECT DepartmentID FROM dbo.DepartmentManager WHERE AdminID = @AdminID)
    			AND
    			App.LocationID in(SELECT LocationID FROM dbo.Manager_Locations WHERE AdminID = @AdminID)
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END
    
    IF @RoleID = 10  -- Location Manager
    	BEGIN
    		SELECT     
    			A.ApplicationID,
    			A.FamilyName,
    			A.GivenName,
    			A.ApplyDate,
    			J.JobTitle,
    			F.Abbreviation 
    		FROM         
    			dbo.ApplicantResume R INNER JOIN
    			dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
    			dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    		WHERE
    			App.LocationID in(SELECT LocationID FROM dbo.Manager_Locations WHERE AdminID = @AdminID)
    			AND
    			CONTAINS(R.FileContent,@SearchFor)
    	END

    This should have created a complex type, but it did not.   When I double click on the procedure in Model Browser to bring up the Edit Function Import window it says it returns a collection of 'None', then when I try to get the column information it says "The selected stored procedure or function returns no columns."  Why is this not showing that columns are being returned?  And more importantly what do I need to do to fix this?     

    Wednesday, July 24, 2019 3:23 PM

Answers

  • User-474980206 posted

    the sp is too complex to create the row set metadata. change to:

    CREATE PROCEDURE SearchResumes
        @SearchFor nvarchar(500),
        @AdminID nvarchar(15)
    AS
        SET NOCOUNT ON
        SELECT     
            A.ApplicationID,
            A.FamilyName,
            A.GivenName,
            A.ApplyDate,
            J.JobTitle,
            F.Abbreviation 
        FROM         
            dbo.ApplicantResume R INNER JOIN
            dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
            dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
            dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
            dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    

    import the proc meta data with your tool of choice. then put the SP bask to the original code.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2019 4:27 PM

All replies

  • User753101303 posted

    Hi,

    My first move would be maybe to try to add a IF @AdminID IS NULL dummy case that would return an empty resultset. It might be enough. Else I would use a SQL Server trace to find out what is used exactly (maybe https://docs.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-2017 or now one of the listed replacement ?)

    You could then test if you can make SQL Server to return something for this kind of SP.

    Wednesday, July 24, 2019 3:49 PM
  • User-474980206 posted

    the sp is too complex to create the row set metadata. change to:

    CREATE PROCEDURE SearchResumes
        @SearchFor nvarchar(500),
        @AdminID nvarchar(15)
    AS
        SET NOCOUNT ON
        SELECT     
            A.ApplicationID,
            A.FamilyName,
            A.GivenName,
            A.ApplyDate,
            J.JobTitle,
            F.Abbreviation 
        FROM         
            dbo.ApplicantResume R INNER JOIN
            dbo.Application A ON R.ApplicationID = A.ApplicationID INNER JOIN
            dbo.Applicant App on A.ApplicationID = App.ApplicationID INNER JOIN
            dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
            dbo.ElectronicFolder F ON App.FolderID = F.FolderID
    

    import the proc meta data with your tool of choice. then put the SP bask to the original code.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2019 4:27 PM
  • User-2012457684 posted

    Yep  that is what I had to do.   It did not like this part of the code 

    CONTAINS(R.FileContent,@SearchFor)

    once I removed all conditions except one and then removed the where clause from it, visual studio created a complex type.   I then recreated the stored procedure as it was previously.   I know my procedure works because I can run it in SSMS and even if it does not return any results it gives me an empty result set,  then if I change my search terms to words I know are in some of the resumes, I start seeing results

    Wednesday, July 24, 2019 5:13 PM
  • User1520731567 posted

    Hi mj1223,

    "The selected stored procedure or function returns no columns."

    If you use EF framework,I suggest you add following code in the start of your stored procedure:

    SET FMTONLY OFF

    Then save the Stored Procedure, Update the model and retry.

    The problem maybe caused by your  stored procedures may  contain:

    • Dynamic queries
    • Temporary tables

    While EF doesn't support importing stored procedures above.

    The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database

    More details,you could refer to these links:

    https://stackoverflow.com/questions/7128747/ef4-the-selected-stored-procedure-returns-no-columns

    https://forums.asp.net/t/2000731.aspx?EF+4+0+The+selected+stored+procedure+returns+no+columns+when+i+try+to+import+my+stored+procedure+inside+my+asp+net+mvc

    Best Regards.

    Yuki Tao

    Thursday, July 25, 2019 3:11 AM