none
Store procedure error: Conversion failed when converting the varchar value 'A' to data type smallint.

    Question

  • I am new to SQL Server. I wrote a store procedure (as listed at the bottom) to provide data to my asp.net page.

    When I run the store procedure by providing null value to @METHOD, I get the result as expected but when I specified the the parameter (@METHOD = N'AB01',) I receive the following error:

    Msg 245, Level 16, State 1, Procedure SP_GET_PROVINCE_SUMMARY, Line 25

    Conversion failed when converting the varchar value 'AB' to data type smallint.

    I would appreciate if the forum can point out the answer to this problem.


    USE [INTEGRATED]

    GO

    DECLARE @return_value int

    EXEC @return_value = [REF].[SP_GET_PROVINCE_SUMMARY]
    @START_DATE = N'1/1/2013',
    @END_DATE = N'12/12/2013',
    @METHOD = N'AB01',
    @ORGANISATION = NULL,
    @PROJECT = NULL,
    @PROVINCE = NULL,
    @TARGET_GROUP = NULL,
    @EVENT = NULL,
    @SERVICE_LOCATION = NULL

    SELECT 'Return Value' = @return_value

    GO


    My SP is listed below:

     

    ALTER PROCEDURE [REF].[SP_GET_PROVINCE_SUMMARY] 
    @START_DATE DATETIME,
    @END_DATE DATETIME,
    @METHOD VARCHAR(1000) = NULL,
    @ORGANISATION NVARCHAR(1000) = NULL,
    @PROJECT NVARCHAR(100)= NULL,
    @PROVINCE NVARCHAR(1000) = NULL,
    @TARGET_GROUP  NVARCHAR(1000) = NULL,
    @EVENT NVARCHAR(2000) = NULL,
    @SERVICE_LOCATION VARCHAR(1000) = NULL
    AS
    DECLARE @STATEMENT NVARCHAR(4000)
    DECLARE @PARAMETERS NVARCHAR(1000)
    DECLARE @SUM_METHOD VARCHAR(2000)
    DECLARE @PVT_METHOD  VARCHAR(2000)
    DECLARE @METHOD_ID VARCHAR(2000)

    BEGIN
    IF NOT @METHOD IS NULL
    DECLARE CUR_METHODS CURSOR  FOR SELECT METHOD_CODE FROM REF.FN_SPLIT_METHOD (@METHOD)
    ELSE
    DECLARE CUR_METHODS CURSOR  FOR SELECT METHOD_CODE FROM METHODS
    OPEN CUR_METHODS

    SET @SUM_METHOD = ''
    SET @PVT_METHOD = ''

    FETCH NEXT FROM CUR_METHODS INTO @METHOD_ID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --create sql string
    IF @SUM_METHOD = '' 
    SET @SUM_METHOD = 'SUM([' + CAST(@METHOD_ID AS VARCHAR) + ']) AS [' + CAST(@METHOD_ID AS VARCHAR) + ']'
    ELSE
    SET @SUM_METHOD = @SUM_METHOD + ', SUM([' + CAST(@METHOD_ID AS VARCHAR) + ']) AS [' + CAST(@METHOD_ID AS VARCHAR) + ']'

    IF @PVT_METHOD = '' 
    SET @PVT_METHOD = ' [' + CAST(@METHOD_ID AS VARCHAR) + ']'
    ELSE
    SET @PVT_METHOD = @PVT_METHOD + ', [' + CAST(@METHOD_ID AS VARCHAR) + ']'

    FETCH NEXT FROM CUR_METHODS INTO @METHOD_ID
    END
    PRINT @SUM_METHOD
    CLOSE CUR_METHODS
    DEALLOCATE CUR_METHODS

    SET @STATEMENT = '  SELECT        PROVINCE_CODE, PROVINCE_NAME, PROVINCE_ABBR, PROVINCE_NAME_KH, '
    SET @STATEMENT = @STATEMENT + @SUM_METHOD
    SET @STATEMENT = @STATEMENT + ' FROM   ( SELECT PROVINCE_CODE, PROVINCE_NAME, PROVINCE_ABBR, PROVINCE_NAME_KH, METHOD_CODE, SUM(QUANTITY) AS REFERRAL_TOTAL '
    SET @STATEMENT = @STATEMENT + ' FROM REF.SQL_ORGANISATION_REFERRALS '
    SET @STATEMENT = @STATEMENT + ' WHERE   (REF.SQL_ORGANISATION_REFERRALS.REPORTING_DATE BETWEEN @START_DATE AND @END_DATE)'

    IF NOT @METHOD IS NULL BEGIN
    SELECT @METHOD = GBL.FN_ENCLOSE_QUOTE(@METHOD)
    SET @STATEMENT = @STATEMENT + ' AND (REF.SQL_ORGANISATION_REFERRALS.METHOD_CODE IN (' + @METHOD + '))'
    END
    IF NOT @ORGANISATION IS NULL BEGIN
    SELECT @ORGANISATION = GBL.FN_ENCLOSE_QUOTE(@ORGANISATION)
    SET @STATEMENT = @STATEMENT + ' AND (REF.SQL_ORGANISATION_REFERRALS.ORGANISATION_CODE IN (' + @ORGANISATION + '))'
    END
    IF NOT @PROJECT IS NULL BEGIN
    SELECT @PROJECT = GBL.FN_ENCLOSE_QUOTE(@PROJECT)
    SET @STATEMENT = @STATEMENT + ' AND (REF.SQL_ORGANISATION_REFERRALS.PROJECT_CODE IN (' + @PROJECT + '))'
    END
    IF NOT @PROVINCE IS NULL BEGIN
    SELECT @PROVINCE = GBL.FN_ENCLOSE_QUOTE(@PROVINCE)
    SET @STATEMENT = @STATEMENT + ' AND (REF.SQL_ORGANISATION_REFERRALS.PROVINCE_CODE IN (' + @PROVINCE + '))'
    END

    IF NOT @TARGET_GROUP IS NULL BEGIN
    SELECT @TARGET_GROUP = GBL.FN_ENCLOSE_QUOTE(@TARGET_GROUP)
    SET @STATEMENT = @STATEMENT + ' AND (REF.SQL_ORGANISATION_REFERRALS.TARGET_GROUP_CODE IN (' + @TARGET_GROUP + '))'
    END

    IF NOT @SERVICE_LOCATION IS NULL BEGIN
    SET @STATEMENT = @STATEMENT + ' AND (REF.SQL_ORGANISATION_REFERRALS.LOCATION_CODE IN (' + @SERVICE_LOCATION + '))'
    END
    SET @STATEMENT = @STATEMENT + ' GROUP BY PROVINCE_CODE, PROVINCE_NAME, PROVINCE_ABBR, PROVINCE_NAME_KH, METHOD_CODE) GRP_DATA '
    SET @STATEMENT = @STATEMENT + ' PIVOT (SUM(REFERRAL_TOTAL) FOR METHOD_CODE IN (' + @PVT_METHOD + ')) PVT_DATA '
    SET @STATEMENT = @STATEMENT + ' GROUP BY PROVINCE_CODE, PROVINCE_NAME, PROVINCE_ABBR, PROVINCE_NAME_KH '
    SET @STATEMENT = @STATEMENT + ' ORDER BY PROVINCE_NAME '

    SET @PARAMETERS = '@START_DATE DATETIME, @END_DATE DATETIME'
    EXECUTE SP_EXECUTESQL @STATEMENT, @PARAMETERS, @START_DATE = @START_DATE, @END_DATE = @END_DATE

    END

    Tuesday, November 19, 2013 1:37 AM

Answers

  • Hi,

    The error could be inside the function call

    Check this funciton. REF.FN_SPLIT_METHOD (@METHOD)

    Also you have used sum function as 'SUM([' + CAST(@METHOD_ID AS VARCHAR).is this right?

    If that function is not causing the issue,Put some print statements along the code to isolate the statement that is causing the error.

    Once you isolate the statement,it will be easy to figure the cause.


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Tuesday, November 19, 2013 2:37 AM
    • Proposed as answer by Naomi NModerator Tuesday, November 19, 2013 3:20 AM
    • Marked as answer by SSamnang Tuesday, November 19, 2013 3:43 AM
    Tuesday, November 19, 2013 2:34 AM

All replies

  • Hi,

    The error could be inside the function call

    Check this funciton. REF.FN_SPLIT_METHOD (@METHOD)

    Also you have used sum function as 'SUM([' + CAST(@METHOD_ID AS VARCHAR).is this right?

    If that function is not causing the issue,Put some print statements along the code to isolate the statement that is causing the error.

    Once you isolate the statement,it will be easy to figure the cause.


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Tuesday, November 19, 2013 2:37 AM
    • Proposed as answer by Naomi NModerator Tuesday, November 19, 2013 3:20 AM
    • Marked as answer by SSamnang Tuesday, November 19, 2013 3:43 AM
    Tuesday, November 19, 2013 2:34 AM
  • Can you explain in English what this procedure is supposed to do? It uses 2 cursors which is not a good idea for SQL Server.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 19, 2013 3:20 AM
    Moderator