locked
StoredProc result into a variable RRS feed

  • Question

  • User496086301 posted

    ERROR : Syntax error converting the varchar value 'Q4 2016' to a column of data type int.
    CREATE PROCEDURE [dbo].[GetQuarter]
    ( @id int )
    AS
    BEGIN
    DECLARE @Date datetime;
    DECLARE @Quarter varchar(20);
    SET @Date = (select TOP 1 Date from tableA where id = @id order by Date desc);

    SELECT @Quarter = 'Q' + DATENAME(QUARTER, @Date) +' ' + DATENAME(YEAR, @Date);
    RETURN @Quarter;
    --PRINT @Date;
    END
    GO

    Date from tableA is a datetime field.

    When I exec GetQuarter 'id', I would like to see Q4 2016.

    Tuesday, September 13, 2016 7:01 PM

Answers

  • User77042963 posted

    You cannot use RETURN to get non  integer value from a stored procedure. You need to use an OUTPUT parameter to get the value you need.

    Here is the modified version of your stored procedure.

    CREATE PROCEDURE [dbo].[GetQuarter]
     @id int 
    ,@Quarter VARCHAR(20) OUTPUT  
    AS 
    BEGIN
    DECLARE @Date datetime; 
    
    SET @Date = (select TOP 1 [Date] from tableA where id = @id order by Date desc);
    
    SELECT @Quarter = 'Q' + DATENAME(QUARTER, @Date) +' ' + DATENAME(YEAR, @Date);
    RETURN;
     
    END
    GO
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2016 2:39 PM

All replies

  • User2117486576 posted

    How are you executing the stored procedure in your .Net code?

    Tuesday, September 13, 2016 11:39 PM
  • User347430248 posted

    Hi Sushsundh,

    try to fetch the raw value in c# code and then you can try to concatenate them and store in the variable instead of doing this operation in stored procedure.

    may be possible that some type casting issue.

    if you get same error in c# code then caste the int value into string then try it.

    also try to debug your code and check what value is passing at run time and on which step error occurs.

    you can also try to convert int to string in stored procedure like below.

    Convert(varchar(10), @Branch )

    Regards

    Deepak

    Wednesday, September 14, 2016 5:51 AM
  • User77042963 posted

    You cannot use RETURN to get non  integer value from a stored procedure. You need to use an OUTPUT parameter to get the value you need.

    Here is the modified version of your stored procedure.

    CREATE PROCEDURE [dbo].[GetQuarter]
     @id int 
    ,@Quarter VARCHAR(20) OUTPUT  
    AS 
    BEGIN
    DECLARE @Date datetime; 
    
    SET @Date = (select TOP 1 [Date] from tableA where id = @id order by Date desc);
    
    SELECT @Quarter = 'Q' + DATENAME(QUARTER, @Date) +' ' + DATENAME(YEAR, @Date);
    RETURN;
     
    END
    GO
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2016 2:39 PM