Stored Procedure only utilises first character of passed parameter

Question

Post reply

Stored Procedure only utilises first character of passed parameter

tony-1020600

Old Hand

Points: 360
More actions
March 4, 2009 at 5:01 am

#200472

Hi,
Below is my Stored Procedure
CREATE PROCEDURE UPDATE_TABLE_COUNT @NAME VARCHAR, @COUNT INT
AS
UPDATE TABLE
SET COUNT = @COUNT
WHERE NAME = @NAME
It's very simple.
I want to update the Count value for rows containing an entry with a name of 1000 (unoriginal, but does the job). When I pass 1000 as my name, only a row with a name value of 1 is updated. I verified this with a name of 2000, and only a row with a name of 2 was updated.
In the table NAME is specified as VARCHAR(50).
What do I need to do to ensure that all characters of the NAME parameter are used, as opposed to just the first?
Tony

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

twillcomp SSC Eights! Points: 989 More actions · Answer 1

Tony,

You need to give the name parameter a size for the varchar declaration.

CREATE PROCEDURE UPDATE_TABLE_COUNT @NAME VARCHAR(4), @COUNT INT

AS

UPDATE TABLE

SET COUNT = @COUNT

WHERE NAME = @NAME

Tony.

vinurajr SSCrazy Points: 2404 More actions · Answer 2

vinurajr

SSCrazy

Points: 2404

March 4, 2009 at 5:15 am

#953018

varchar length u need to define;)

tony-1020600 Old Hand Points: 360 More actions · Answer 3

Thanks for that guys.

I had tried that solution before in the SQL Management Tool and it twice failed to compile nad execute.

Now you have made the suggestion it works a treat.

Power of the mind it must be!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

tony (3/4/2009)
CREATE PROCEDURE UPDATE_TABLE_COUNT @NAME VARCHAR, @COUNT INT

Just to explain why. If you define a varchar (or char, nchar, nvarchar, binary, varbinary) without giving a length parameter, the default length is 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JacekO SSCertifiable Points: 6610 More actions · Answer 5

GilaMonster (3/4/2009)
tony (3/4/2009)
CREATE PROCEDURE UPDATE_TABLE_COUNT @NAME VARCHAR, @COUNT INT
Just to explain why. If you define a varchar (or char, nchar, nvarchar, binary, varbinary) without giving a length parameter, the default length is 1

Except when you use VARCHAR (without the lenght) in CAST and CONVERT. Then the default lenght is 30.

DECLARE @test-2 VARCHAR(100)

SET @test-2 = '123456789012345678901234567890123456789012345678901234567890'

SELECT LEN(@test)

SELECT LEN(CONVERT(VARCHAR, @test-2))

SELECT LEN(CAST(@test AS VARCHAR))

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]