Post reply

Stored Procedures - Input Parameters

  • March 24, 2007 at 12:08 pm

    #92381

    SQL Server 2005 is giving me a headache when I try to create a stored procedure with an input parameter for text values. I am trying to create the following stored procedure which needs to be passed a string of ID numbers:

    CREATE PROCEDURE [dbo].[sp_FundingsCount]

    (@strApplicationIDs nvarchar(4000) = '',

    @intCount

    INT OUTPUT)

    AS

    SET @intCount = (SELECT COUNT(*) AS RecordCount

    FROM Fundings

    WHERE ApplicationID IN (@strApplicationIDs));

    RETURN

    @intCount

    SQL Server 2005 insists on trying to convert my input parameter to an INT data type:

    Syntax error converting the nvarchar value '2038,57' to a column of data type int.

  • March 24, 2007 at 11:35 pm

    #696773

    WHERE ','+@strApplicationIDs+',' LIKE '%,'+CAST(ApplicationID AS VARCHAR(10)+',%'

    Index usage is certainly not possible using this method...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 25, 2007 at 8:12 am

    #696775

    Thanks! I solved the problem by including the subquery in my stored procedures rather than trying to pass them a string of ID numbers.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply