stored proc not really returning anything

Question

using sql server, in azure.

a requested a stored procedure to return a next number from a developer.

This is what they returned.

BEGIN

exec ('
SELECT 
  (
    SELECT FORMAT(getdate(),agencyFormat) FROM [dbo].[tblCardNextNum]     WHERE agencyID = '+@agencyID+' and type = '''+@type+''') + 
    FORMAT(NEXT VALUE FOR [dbo].['+@agencyShort+'], 
      (
        SELECT REPLICATE(''0'',
        (
          SELECT [agencyMaxLength] FROM [dbo].[tblCardNextNum] WHERE   agencyID = '+@agencyID+' and type = '''+@type+'''
        )
      )
    )
  )
')

END

Now when i execute the stored proc in ssms, it will display the next number, but it will not return anything, just will display it.

So my question is....

what do i add to this stored procedure to actually return the newly created number to the calling function?

To those that are asking about the calling of this stored procedure...

EXEC [dbo].[GetCrudAgencyNextNum]
    @agencyShort = N'PLFD',
    @agencyID = 2,
    @type = N'C'

small screen shot

and no, this is NOT a duplicate of the MVC solution - i looked.

my question is.. what do i add to this stored procedure to return the value.

Usually a good starting point in returning a value is to actually include the word return in your query. Also, Google "SQL Server Return". — dfundako, Apr 25 '18 at 13:23
In SSMS, what do you think is the difference between displaying a result and returning a result? It's probably possible to get the result in your calling function as is, if you do it correctly. But you haven't shown us the calling function. — Tab Alleman, Apr 25 '18 at 13:38
Possible duplicate of [How to return values from a dynamic SQL Stored Procedure to the Entity Framework?](https://stackoverflow.com/questions/3166952/how-to-return-values-from-a-dynamic-sql-stored-procedure-to-the-entity-framework) — Peter B, Apr 25 '18 at 13:38
The code is calling a sequence ID, using dynamic code. Please start by posting all of the code, not just partial bits. I think the op is referring to the display from the `Messages` tab vs the `Results` tab. — Random_User, Apr 25 '18 at 13:49
I'd recommend parametrising your SQL; it's wide open to Injection right now. — Thom A, Apr 25 '18 at 14:01

score 1 · Answer 1 · answered Apr 25 '18 at 14:32

There is a a lot of guesswork here, as images of code aren't helpful.

Firstly, like I said in the comments, please please please parametrise your SQL; concatenating strings like that is a injection issue waiting to happen. It's 2018 and poor pratcies like that should be a thing of the past.

Next, the return value of a SP is either 0 or 1, that's it. If you want to output to a variable, use an OUTPUT parameter.

The following should get you on the right path, however, it is untested:

CREATE PROC YourProc @AgencyShort sysname, @AgencyID int, @Type nchar(1), @Return bigint OUTPUT AS --I have GUESSED your datatypes
BEGIN


    DECLARE @SQL nvarchar(MAX);
    DECLARE @Params nvarchar(MAX);

    SET @SQL = N'SELECT @dReturn = (SELECT FORMAT(getdate(),agencyFormat)' + NCHAR(10) +
               N'                   FROM [dbo].[tblCardNextNum]' + NCHAR(10) +
               N'                   WHERE agencyID = @dAgencyID' + NCHAR(10) +
               N'                     AND type = @dtype) + ' + NCHAR(10) +
               N'                   FORMAT(NEXT VALUE FOR [dbo].' + QUOTENAME(@agencyShort) + N' (SELECT REPLICATE(''0'',(SELECT [agencyMaxLength]' + NCHAR(10) +
               N'                                                                                                         FROM [dbo].[tblCardNextNum]' + NCHAR(10) +
               N'                                                                                                         WHERE agencyID =  @dAgencyID' + NCHAR(10) +
               N'                                                                                                           AND type = @dType))));';
    PRINT @SQL; --Your Best Friend
    SET @Params = N'@dAgencyID int, @dType nchar(1), @dReturn bigint OUTPUT'; -- I have GUESSED your datatypes
    EXEC sp_executesql @SQL, @Params, @dAgencyID = @AgencyID, @dType = @Type, @dReturn = @Return OUTPUT;
END
GO

DECLARE @Return bigint;
EXEC YourProc N'PLFD',2, N'C', @Return OUTPUT;
SELECT @Return AS [Return];

If this doesn't work, your best friend is there to help you.

score 0 · Accepted Answer · answered Apr 26 '18 at 01:43

0

After kicking the stored proc back to the DBA... they rewrote it, and now we have a working stored proc YEA!

answered Apr 26 '18 at 01:43

pithhelmet

2,222
6
35
60

stored proc not really returning anything

2 Answers2