Stored procedure does not return anything

Question

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?

Does it work with an @input of varchar(10)? Is it padding your variable so that it's searching for `LIKE '%2333 %'`? — Danny, Apr 08 '14 at 22:53

score 2 · Answer 1 · answered Apr 08 '14 at 22:54

2

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

your sp is currently running

isonum from iso where isonum like '%2333 %'

answered Apr 08 '14 at 22:54

Nick.Mc

18,304
6
61
91

you can observe by adding this to your sp: `select '[' + @input + ']'` – Nick.Mc Apr 08 '14 at 22:55

score 0 · Answer 2 · answered Apr 08 '14 at 22:57

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.

Stored procedure does not return anything

2 Answers2

Linked