CODEWITHSUNDEEP
sqlsql-server-2008
Haminteu
Haminteu

Reputation: 1334

Error converting data type varchar to float SQL Server stored procedure

I've searching and searching, but still... Gave me an error.

I have this stored procedure:

CREATE procedure _spItemList
(
    @branch nchar(12)
    , @ItemNo float
    , @ItemDescription char(60)
)
as
begin
        SET NOCOUNT ON;

        select          
            Branch
            , CONVERT(varchar(100), ItemNo) ItemNo
            , Description1
            , Description2
            , FullDescription
        from
            _ItemList
        where           
            (Branch LIKE '%' + @branch + '%' or ISNULL(@branch, '') = '')
            and (ItemNo LIKE '%' + @ItemNo + '%' or ISNULL(@ItemNo, '') = '')
            and (FullDescription LIKE '%' + @ItemDescription + '%' or ISNULL(@ItemDescription, '') = '')
    end

I have the following table structure:

CREATE TABLE _ItemList
(
    [ItemNo] [float] NULL,
    [Description1] [char](30) NULL,
    [Description2] [char](30) NULL,
    [FullDescription] [char](60) NULL
)

When I execute the stored procedure, I get an error:

Error converting data type varchar to float

What's wrong? SQL Server parameter in stored procedure already has the same data type as the column in the table. Does anyone know?

Upvotes: 0

Views: 398

Answers (1)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

Try to convert this sentence too, because your are concatenating '%' (string) with @ItemNo (float value), check it out:

(ItemNo LIKE '%' + CONVERT(VARCHAR(100),@ItemNo) + '%' or ISNULL(@ItemNo, '') = '')

Upvotes: 1

Related Questions