locked
stored procedure return value RRS feed

  • Question

  • User154448021 posted

    I have a stored procedure in SQL server 2000.  It is a simple select query and returns a list of account names.  However, when I try to execute the stored procedure, it automatically declare @return_value as int and return this int as shown below:

    DECLARE    @return_value int

    EXEC    @return_value = [dbo].[p_getAccountNameListByPrefix]
            @prefixText = N'br',
            @count = 40

    SELECT    'Return Value' = @return_value


    How can I make the stored procedure return the list of account names as expected?

    Thanks.

    Wednesday, June 9, 2010 10:14 AM

Answers

  • User154448021 posted

    just 

    EXEC [dbo].[p_getAccountNameListByPrefix]
            @prefixText = N'br',
            @count = 40

    I certainly knew without the select @return_value at the end, I would get the list of names. 

    I hoped I could get some quick answer.  But I figured it out myself:

    That line is added automatically when the stored procedure is called in SQL Server 2000.  DataSet would return the list of names and work for both SQL server 2000 and 2005.   But LINQ to SQL only works with SQL Server 2005.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 9, 2010 11:27 AM

All replies

  • User-36838843 posted

    DECLARE    @return_value int

    EXEC    @return_value = [dbo].[p_getAccountNameListByPrefix]
            @prefixText = N'br',
            @count = 40

    SELECT    'Return Value' = @return_value

    Just simply run this 

    DECLARE    @return_value int

    EXEC [dbo].[p_getAccountNameListByPrefix]
            @prefixText = N'br',
            @count = 40

    You will get list of account names.

    The reason why you are not getting expected result is you are returning the result to variable @return_value and which is int and I think stored proc is returning the number of records(means number of account names).  So you can simply execute that stored proc to return list.

    Wednesday, June 9, 2010 10:23 AM
  • User1508394307 posted

    just 

    EXEC [dbo].[p_getAccountNameListByPrefix]
            @prefixText = N'br',
            @count = 40

    Wednesday, June 9, 2010 10:24 AM
  • User-1199946673 posted

    I have a stored procedure in SQL server 2000
     

    So why are you posting this in the Access Databases and AccessDataSource Control forum?

    Wednesday, June 9, 2010 10:37 AM
  • User154448021 posted

    just 

    EXEC [dbo].[p_getAccountNameListByPrefix]
            @prefixText = N'br',
            @count = 40

    I certainly knew without the select @return_value at the end, I would get the list of names. 

    I hoped I could get some quick answer.  But I figured it out myself:

    That line is added automatically when the stored procedure is called in SQL Server 2000.  DataSet would return the list of names and work for both SQL server 2000 and 2005.   But LINQ to SQL only works with SQL Server 2005.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 9, 2010 11:27 AM