CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Niraj
Niraj

Reputation: 1842

SQL Server Stored Procedure with varchar parameter returns wrong results

I have created a stored procedure which takes a parameter of type varchar(5). My stored procedure was working fine, and returning the correct result, until the time I passed it a string of 6 or more characters.

What happened is that it ignored 6th onward character, and returned result only based on first 5 characters, which was a wrong result. I expect it to throw an error when I am passing a longer string.

Is this a bug or there is way to change this behavior of SQL Server?

create procedure usp_testproc 
    @param1 varchar(5)
as
begin
    if @param1 = '12345'
    begin
       select 'you got this right'
    end 
    else
    begin
       select 'String Mismatch'
    end
end

No matter whether we call 

exec usp_testproc '12345'

or 

exec usp_testproc '123456'

we get the same result

Upvotes: 4

Views: 12139

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

The stored procedure is working perfectly according what is done inside it. The bug is in your implementation. Your SP accepts max 5 chars long string, but you are expecting it to do work with the strings longer than 5 chars, this is contradiction. So you have to modify your SP in order to get your desired result, you can easily increase the length of the varchar parameter to an acceptable range so that the input does not exceed it. So you can do something like this :

create procedure usp_testproc @param1 varchar(50)
as
begin

if len(@param1)<6
begin
    if @param1='12345'
        begin
           select 'you got this right'
        end 
        else
        begin
           select 'String Mismatch'
        end
    end
else
    begin
       select 'Parameter length is high!'
    end 
end

Upvotes: 0

bjnr
bjnr

Reputation: 3437

You need to specify the correct type of the input parameter:

create procedure usp_testproc @param1 varchar(<max number of chars here>)
as...

Upvotes: 1

FilipYordanov
FilipYordanov

Reputation: 117

varchar(5)

It means you will get only the first 5 chars so it will ignore the rest, the number inside of the "()" shows how many symbols you will store in this param. You can ensure yourself with the longest possible string that can be returned from your procedure and it will be fine Here's a little extra that you can read : char and varchar (docs.microsoft)

Upvotes: 4

Related Questions