3

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

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
Niraj
  • 1,782
  • 1
  • 22
  • 32
  • 9
    `'123456'` is longer than 5 characters, which means it is truncated to `'12345'` by your parameter size of `varchar(5)` (per your code, not your question stating `varchar(10)`). – SqlZim Nov 07 '17 at 18:30
  • Try '12345' and '1234'. Pretty sure you get different result. – Eric Nov 07 '17 at 18:31
  • As @SqlZim explain, it will return the same result with any string start with `'12345'` you pass. even you pas `'123456789'`. – Ilyes Nov 07 '17 at 18:32
  • 1
    More discussion about this here: https://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures – SqlZim Nov 07 '17 at 18:35
  • This is an issue with SQL Server. There is an open Microsoft Connect item regarding this issue.https://connect.microsoft.com/SQLServer/feedback/details/622699/stored-procedures-should-throw-error-message-when-called-with-string-values-which-exceed-lenght-of-stored-procedure-parameter – Niraj Nov 08 '17 at 13:07

3 Answers3

4
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)

Ilyes
  • 14,640
  • 4
  • 29
  • 55
FilipYordanov
  • 117
  • 4
  • Try to explain some for the OP please, I feel like your answer like a comment. – Ilyes Nov 07 '17 at 18:33
  • 2
    It is a comment, you need to have a reputation score of at least 50 to post a comment, so some post comments as answers. :-) – SS_DBA Nov 07 '17 at 18:46
1

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

create procedure usp_testproc @param1 varchar(<max number of chars here>)
as...
bjnr
  • 3,353
  • 1
  • 18
  • 32
0

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
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43