CODEWITHSUNDEEP
sqlstored-procedures
eugene.it
eugene.it

Reputation: 447

Stored procedure does not return anything

It is should be no problem to find but after long hours at my job I cannot notice what I'm doing wrong here.

There is very simple stored procedure:

ALTER PROCEDURE MyProc  
@input char(10)

AS

    BEGIN
        SET NOCOUNT ON;
        SELECT isonum 
        FROM iso where isonum LIKE '%' + @input + '%'
        ORDER BY isonum
    END

when executing a query: select isonum from iso where isonum like '%2333%' - I get the data,but when executing the stored procedure:

exec MyProc '2333' - I get nothing???

What's wrong here?

Upvotes: 0

Views: 114

Answers (2)

M.Ali
M.Ali

Reputation: 69494

ALTER PROCEDURE MyProc  
@input varchar(10)   --<-- Use varchar here 

AS

    BEGIN
        SET NOCOUNT ON;
        SELECT isonum 
        FROM iso where isonum LIKE '%' + @input + '%'
        ORDER BY isonum
    END

'CHAR' or 'NCHAR' fixed data types and they add white spaces to the passed strings if it is less then the maximum length of the data.

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19184

Change to @input char(10) to @input varchar(10)

your sp is currently running

isonum from iso where isonum like '%2333 %'

Upvotes: 2

Related Questions