truncation of value passed into the stored procedure

I want my stored procedure to throw an error if the value passed into the parameter is > the length of how the variable is declared rather then doing what it is doing now and truncating the passed value. How?
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Well, you will need to declare the parameter as large as the biggest number of characters.

Then inside the procedure have your procedural declaratives to load and check

CREATE PROCEDURE usp_my_test_procedure (@Parameter1 varchar(500) ) 
AS
DECLARE @Name       varchar(50)

IF LEN(@Parameter1) between 1 and 50
   SET @NAME = @PARAMETER1
ELSE
BEGIN
   SELECT ERROR = '@Parameter1 is wrong size '+cast(LEN(@Parameter1) as varchar(10))
   RETURN
END

SELECT GOOD = 'Good parameter ' + @name

GO

-- now test

exec usp_my_test_procedure @Parameter1 = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
exec usp_my_test_procedure @Parameter1 = 'bbb'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
Is there something built into SQL server that would make if error rather then truncate?
Mark WillsTopic AdvisorCommented:
Not that I know of.

It can also happen with variables. These are about the only two places where  ANSI_WARNINGS  are not honoured.

There used to be a connect ticket (now collaborate) in Microsoft, but was always 'not changing'


edit : "Not that I know of." really means "I know there isnt"
vbnetcoderAuthor Commented:
ty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.