CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user2994144
user2994144

Reputation: 167

Stored Procedure not working properly

I have the following stored procedure but it's not returning the correct value. I tested taking certain things out and it seems like % is the reason why it's not working properly. Although, ID is an Int in the database, the program kept failing due to % until I changed IDOld to VARCHAR.

I tested the statement in SQL and it returns the correct value only the stored procedure is returning the wrong value. Also, I tested it by inserting '1' after like and it was still unable to find the value even though it does exists in the database. So not sure how to make this work properly.

I'm getting the old ID and removing the first digit and trying to find the ID that is like the old Id but without the first digit so example IDOld=5623826, IDOld = 623826 than it should return the value that contains IDOld. In the database there are more variations of the ID like 1623826, 2623826,... but I want the last updated value hence using Max. 

ALTER PROCEDURE dbo.UP_getLastUsedIDFromProducts
(
    @IDOld VARCHAR,
    @IDLastUpdated INT OUTPUT,
)
AS
    SELECT @IDLastUpdated = MAX(ID)  
    FROM Products 
    WHERE ID LIKE  '%' + @IDOld

 SqlCommand cmd = new SqlCommand("UP_getLastUsedIDFromProducts");
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@IDOld", SqlDbType.VarChar).Value = tbOriginalID.Text;
 cmd.Parameters.Add("@IDLastUpdated", SqlDbType.Int).Direction = ParameterDirection.Output;

Upvotes: 0

Views: 324

Answers (2)

user275683
user275683

Reputation:

This is a problem that comes from not specifying size of VARCHAR fields. When you type VARCHAR it is same as VARCHAR(1) thus SQL server truncates your input to first character.

Read this https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length

Additionally you want to avoid implicit conversion between datatypes. This can greatly affect performance of the query in case SQL decides to convert your column instead of parameter. That is why you always want to match data types on both side. You never want 1 = '1' which will work but will cause problems. That is why it is better to do this 1 = CONVERT(INT,'1')

Read this - http://blogs.msdn.com/b/turgays/archive/2013/09/16/data-mismatch-on-where-clause-might-cause-serious-performance-problems.aspx

Upvotes: 0

marc_s
marc_s

Reputation: 755217

Defining a parameter of type VARCHAR without giving it a length will give you a string of exactly one character in length ...

ALTER PROCEDURE dbo.UP_getLastUsedIDFromProducts
(
    @IDOld VARCHAR,            -- exactly ONE character long
    @IDLastUpdated INT OUTPUT,
)

This is most often not what you're looking for - so please always specify a length when you define a VARCHAR (whether it's used as a parameter, defining a SQL variable, using it in a CAST or CONVERT statement, or a table definition) !

ALTER PROCEDURE dbo.UP_getLastUsedIDFromProducts
(
    @IDOld VARCHAR(20),        -- or whatever fits your needs - just *DEFINE* the length!
    @IDLastUpdated INT OUTPUT,
)

Upvotes: 1

Related Questions