none
Designer: SP returns int when it shouldn't RRS feed

  • Question

  • Hi.
    I wrote a SP. That's it:

    ALTER PROCEDURE [dbo].[get_work_by_id]
    	@RoleID bigint,
    	@pWorksPlanId bigint
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	DECLARE @SQL nvarchar(MAX)
    	SELECT @SQL=QueryText FROM StoredQueries WHERE QueryName='get_works_Role'+cast(@RoleID as nvarchar(10));
    	SET @SQL=@SQL+' AND WP.Id = ' + cast(@pWorksPlanId as nvarchar(10));
    
    	CREATE TABLE #tmp([Id] [bigint] NOT NULL, ... , [CustomerKS3State] [bigint] NULL);
    
    	INSERT #tmp(Id, ..., CustomerKS3State)
    	EXEC(@SQL);
    
    	SELECT * FROM #tmp
    END
    When I add it into a O\R Designer and watch its properties there is no return value [greyed field with "No" or "None" or smth]. Restarting VS & recreating connection made no changs so i don't know how to get SELECT results of SP into my app. And, this SP works perfectly into the SQL Server Mgmt Studio and returns records as i expect. Please, help me.
    Tuesday, September 29, 2009 8:03 AM

Answers

  • Hi Niello,

    For inserting, updating and deleting SP, you can set any return type as result.

    You can get more information about it in this blog,
    http://blogs.msdn.com/adonet/archive/2008/03/26/stored-procedure-mapping.aspx

    For other type of SP, you can just use entity as return type in EF 3.5. It is possible to use other type in EF 4.

    Another option is using normal way to call the SP,

                    EntityConnection entityConnection = (EntityConnection)context.Connection;
                    DbConnection storeConnection = entityConnection.StoreConnection;

                    DbCommand command = storeConnection.CreateCommand();

                    command.CommandText = "getUser";

                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter("@userid", 5));

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Tuesday, October 6, 2009 1:02 AM
    Thursday, October 1, 2009 7:38 AM

All replies

  • Think I know what's a matter. Designer can not parse a structure of non-existing table [as well as table created into a SP]. So, i change my question:
    Can anyone say if i can manually write a returning value class & rewrite generated SP interface into my app? I ask before trying because a lot of code is to be written. So if you know the result please share with me.

    Thanx.
    Wednesday, September 30, 2009 2:52 AM
  • Hi Niello,

    For inserting, updating and deleting SP, you can set any return type as result.

    You can get more information about it in this blog,
    http://blogs.msdn.com/adonet/archive/2008/03/26/stored-procedure-mapping.aspx

    For other type of SP, you can just use entity as return type in EF 3.5. It is possible to use other type in EF 4.

    Another option is using normal way to call the SP,

                    EntityConnection entityConnection = (EntityConnection)context.Connection;
                    DbConnection storeConnection = entityConnection.StoreConnection;

                    DbCommand command = storeConnection.CreateCommand();

                    command.CommandText = "getUser";

                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter("@userid", 5));

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Tuesday, October 6, 2009 1:02 AM
    Thursday, October 1, 2009 7:38 AM