none
Stored Procedure has no return type, when it should RRS feed

  • Question

  • Hi,

     

    I have a problem where I'm trying to add a stored procedure to my DBML file, and it insists that the procedure has no return type, even though the last statement in the procedure is a SELECT.

     

    The fully query is as below:

     

    Code Snippet

    ALTER PROCEDURE [dbo].[FindComputers]

      @SearchString varchar(16),

      @UserName varchar(16)

    AS

    BEGIN

      DECLARE @ApplicationId uniqueidentifier

      SELECT @ApplicationId = NULL

      SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER('LabManager') = LoweredApplicationName

     

      DECLARE @UserId uniqueidentifier

      SELECT @UserId = NULL

      SELECT @UserId = UserId

      FROM dbo.aspnet_Users

      WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

     

      SELECT DISTINCT c.* FROM Computer c

      INNER JOIN SecurityAccess s ON c.computerid = s.computerid or c.labid = s.labid or c.siteid = s.siteid

      INNER JOIN aspnet_roles AS r ON s.roleid = r.roleid

      WHERE c.name like ('%' + @SearchString + '%')

      AND r.RoleName IN (

        SELECT r.RoleName

        FROM dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur

        WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId

      )

      GROUP BY c.name, c.siteid, c.labid, c.computerid, c.macaddress, c.expressid, c.altirisid, c.inmaintenancemode, c.lastchange, r.roleid, c.islectern, c.fcmis, c.inproduction, c.targetcomputerid, c.computertypeid

      HAVING

        min (

          CASE

            WHEN s.computerid IS NOT NULL THEN s.allowaccess + 1

            WHEN s.labid IS NOT NULL THEN s.allowaccess + 3

            WHEN s.siteid IS NOT NULL THEN s.allowaccess + 5

            ELSE 7

          END

        ) % 2 = 0

      ORDER BY c.name

    END

     

    I'd really love to know why it won't give it a return type. Testing the procedure with Step Into Procedure, it does everything as it should from what I can tell.

     

    Many Thanks,

     

    Jessica

    Monday, June 16, 2008 11:52 PM

Answers

All replies

  • I first posted something about this here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3184132&SiteID=1

    I also posted a bug report here: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=339154

     

    I believe it's because of the 'declares' or other stuff BEFORE the select.

     

    workaorunds for include making the VERY FIRST THING (after the BEGIN statement) being a false / fake select statemement which returns some dummy results, but in the correct format.

     

    so for you, maybe try this?

     

    SELECT DISTINCT c.* FROM Computer c

     

    that's it. get rid of all the rest (copy/paste it somewhere else). save sproc. drag onto designer and then check what is designed.

     

    if the code is right, replace the content of the sproc with your original sql and save .. do NOT drag back to designer.

     

    run linq code and see if it's working.

    Tuesday, June 17, 2008 1:22 AM
  • Thanks for the link to your thread. One of the replies provided the 'fix' to make the designer work.

     

    Moving the last END up to just before the SELECT to be returned made the designer generate the correct return type.

     

    Jessica

     

    Tuesday, June 17, 2008 1:43 AM