Conversion Error from varchar to smallint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Conversion Error from varchar to smallint

Author

Topic

Quan69
Starting Member

5 Posts

Posted - 2011-08-08 : 19:53:58

I have a stored procedure that results in an error:
Converting adult_member_no from varchar to smallint.

It is odd b/c I have adult_member_no declared as smallint...

Here is my stored proc:

-- Create new proc for adding adult member
ALTER PROCEDURE [dbo].[AddJuvenile]
(
@member_no smallint OUTPUT,
@firstname VARCHAR(15) = NULL,
@middleinitial CHAR(1) = NULL,
@lastname VARCHAR(15) = NULL,
@adult_member_no smallint,
@birth_date datetime = NULL
)
AS

-- DECLARE/SET datetime variables
-- Assumption: That Juvenile is from 5 years to day before 18th birthday
-- @min_age and @max_age are used to verify juvenile's age
DECLARE @expr_date datetime = DATEADD(yy, 1, GetDate());
DECLARE @min_age datetime = DATEADD(dd, -1825, GetDate()); -- 5 years
DECLARE @max_age datetime = DATEADD(dd, -6569, GetDate()); -- 17 years, 11 months, 29 days

-- Test for NULLS and unwanted values
IF @firstname IS NULL
BEGIN
RAISERROR('First Name Can NOT be empty', 10, 1);
RETURN -10
END
IF @lastname IS NULL
BEGIN
RAISERROR('Last Name Can NOT be empty', 10, 1);
RETURN -10
END
IF @adult_member_no IS NULL OR @adult_member_no < 0 OR @adult_member_no > 32767
BEGIN
RAISERROR('Adult Member ID must be positive of type short', 10, 1);
RETURN -10
END
IF @birth_date IS NULL OR @birth_date > @min_age OR @birth_date < @max_age
BEGIN
RAISERROR('City can NOT be empty', 10, 1);
RETURN -10;
END

BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON

-- TODO: Check if Adult Member ID exists in adult/member tables
IF EXISTS(SELECT member_no FROM [library].[dbo].[adult] WHERE member_no='adult_member_no')
-- First, insert into member table to get memberID
INSERT INTO member (firstname, middleinitial, lastname)
VALUES (@firstname, @middleinitial, @lastname);

-- Commit Rollback if an error
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -@@error;
END

SET @member_no = scope_identity();
IF @member_no IS NULL OR @member_no < 0 OR @member_no > 32767
BEGIN
RAISERROR('Member ID must be positive of type short', 10, 1);
RETURN -10
END

INSERT INTO juvenile (member_no, adult_member_no, birth_date)
VALUES (@member_no, @adult_member_no, @birth_date);

-- Commit Rollback if an error
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -@@error;
END
ELSE
BEGIN
RAISERROR('Adult Member ID does NOT exist in library DB!', 10, 1);
RETURN -10
END
COMMIT TRANSACTION
RETURN
END

Thanks for help

Me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-08 : 20:07:23

I think it's failing here: WHERE member_no='adult_member_no'

Don't you want: WHERE member_no=@adult_member_no

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Quan69
Starting Member

5 Posts

Posted - 2011-08-08 : 23:18:50

Ok, stupid mistake!

But I still get that error...

Me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-08 : 23:59:33

You'll need to narrow it down for us then with a line number. Please color code the section that is erroring. And if you can't, please start removing code until you narrow it down.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-09 : 00:44:29

What value is getting passed in? If you're passing a varchar that can't explicitly be converted, then there's your error.

Also, the block of code starting with

quote:
IF @member_no IS NULL OR @member_no < 0 OR @member_no > 32767

is totally unnecessary. It isn't possible for any of these conditions to be true in this SP.

AND...why in the world would you allow optional parameters only to raise a custom error if they aren't supplied? should remove the @param = null, and all of that custom error handling for missing params.

Wow...remove those transactions too. I'm starting to sound like Celko, but this thing could be just a few lines, yet you've managed to make it many and hurt performance at the same time.

But, try this out

ALTER PROCEDURE [dbo].[AddJuvenile]
	@member_no smallint OUTPUT,
	@firstname VARCHAR(15),
	@middleinitial CHAR(1),
	@lastname VARCHAR(15),
	@adult_member_no smallint,
	@birth_date datetime
AS

SET NOCOUNT ON
-- DECLARE/SET datetime variables
-- Assumption: That Juvenile is from 5 years to day before 18th birthday
-- @min_age and @max_age are used to verify juvenile's age
DECLARE @expr_date datetime = DATEADD(yy, 1, GetDate());
DECLARE @min_age datetime = DATEADD(dd, -1825, GetDate()); -- 5 years
DECLARE @max_age datetime = DATEADD(dd, -6569, GetDate()); -- 17 years, 11 months, 29 days


IF @birth_date > @min_age OR @birth_date < @max_age
BEGIN
RAISERROR('I dont think this message should be city related', 10, 1);
RETURN -10;
END


-- TODO: Check if Adult Member ID exists in adult/member tables 
IF EXISTS(SELECT member_no FROM [library].[dbo].[adult] WHERE member_no = adult_member_no)
-- First, insert into member table to get memberID
INSERT INTO member (firstname, middleinitial, lastname)
VALUES (@firstname, @middleinitial, @lastname);

SET @member_no = scope_identity(); 

INSERT INTO juvenile (member_no, adult_member_no, birth_date)
VALUES (@member_no, @adult_member_no, @birth_date);
GO

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-08-09 : 19:08:06

If I am reading the error correctly it is complaining about 'adult_member_no'; NOT '@adult_member_no'. Is there a column in your table with that name? Is it a varchar? Would any of the values fail to convert to smallint?

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

Quan69
Starting Member

5 Posts

Posted - 2011-08-11 : 00:32:58

RESOLVED -- Thanks everyone

If statements with more than a single line of code must be contained in BEGIN-END block

Me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources