Storeproc - Error converting data type

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

General SQL Server Forums

New to SQL Server Programming

Storeproc - Error converting data type

Author

Topic

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 04:22:48

[code]
--Creation of Procedure
create procedure sp_WIP2Stock

@Stock_ID int,
@Item_ID int,
@LookupID int,
@Stock_Class_ID int,
@Test_Tech_ID int,
@Job_No varchar(10),
@Serial_No varchar(50),
@Asset_Tag varchar(30),
@Func_Status varchar(20),
@Grade varchar(10),
@Test_Comments varchar(100),
@Test_Date date,
@Stock_Loc varchar(15),
@BrokerDate date

As

select * from Test_WIP_tbl TW
where TW.Job_No = @Job_No and TW.Stock_Loc = @Stock_Loc

Begin

insert into Stock_tbl values(@Stock_ID,@Item_ID,@LookupID,@Stock_Class_ID,@Test_Tech_ID,
@Job_No,@Serial_No,@Asset_Tag,@Func_Status,@Grade,@Test_Comments,@Test_Date,@Stock_Loc,NULL);

End

--To Run Procedure
exec sp_WIP2Stock 'EK2000', 'PLT04'

[/code]

Msg 8114, Level 16, State 5, Procedure sp_WIP2Stock, Line 0
Error converting data type varchar to int.

The proc is supposed to copy data from the Test_WIP to the Stock table
The only extra field that is in the stock table is BrokerDate and it's set to allow NULLs

I've checked the field types and the field ordering for both tables and they are correct. The script below works but the storeproc above doesn't.

[code]
Insert into Stock_tbl
SELECT [Stock_ID]
,[Item_ID]
,[LookupID]
,[Stock_Class_ID]
,[Test_Tech_ID]
,[Job_No]
,[Serial_No]
,[Asset_Tag]
,[Func_Status]
,[Grade]
,[Test_Comments]
,[Test_Date]
,[Stock_Loc]
,NULL
from Test_WIP_tbl
where Func_Status = 'Pass' and Stock_Loc = 'PLT01'
[/code]

any advice gratefully received :)

(total SQL noob here)

Paul

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-21 : 04:29:19

exec sp_WIP2Stock 'EK2000', 'PLT04'

Your sp has more than two parameters.
The sp is trying to take the given parameters in the given order so
@Stock_ID int, --> 'EK2000' --> not possible
@Item_ID int --> 'PLT04' --> not possible

No, you're never too old to Yak'n'Roll if you're too young to die.

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 04:43:27

Ah so I need to reduce the number of parameters at the start of the sp?

Paul

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-21 : 04:45:59

Or you can give null,null,... in the right order

No, you're never too old to Yak'n'Roll if you're too young to die.

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 04:51:06

Maybe I've misunderstood how to do the sp.

My intention was to copy the data from all the fields in the test_wip table to the Stock table then later to delete the data from test_wip

am I going about it the wrong way?

Paul

Complete SQL newbie!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 05:10:10

One row at a time? How many rows do you need to move? Once off or repeated?

--
Gail Shaw
SQL Server MVP

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 05:16:02

Usually there will be between 15 and 100 rows to move at a time and this will happen a couple of times per day

Paul

Complete SQL newbie!

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 05:35:20

I think I'll either want the rows moved by job_No and stock_loc or just by stock_loc as long as I can ensure that the technicians (doing the testing) use unique idents for the stock_loc field

Basically once a batch of items is tested it'll be moved from test_wip into stock where it will be visible to the sales staff

Thanks for everyones input :)

--
Paul

Complete SQL newbie!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 05:57:47

How do you identify that a batch of stock has been tested?

Doing the inserts row by row is slow and far from the best approach

--
Gail Shaw
SQL Server MVP

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 06:05:26

Well once a batch of items (usually a pallet) is tested it is passed over to stock control who'll do the move of the data to the stock table and update the warehouse stock location for the items

--
Paul

Complete SQL newbie!

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 07:27:01

Aha! got it working

and have added in a line to delete the entries from the test_wip table :)


--Creation of Procedure
create procedure sp_WIP2Stock

@Stock_Loc varchar(15)

As

Begin

insert into Stock_tbl select Stock_ID,Item_ID,LookupID,Stock_Class_ID,Test_Tech_ID, Job_No,Serial_No,
Asset_Tag,Func_Status,Grade,Test_Comments,Test_Date,Stock_Loc,NULL from Test_WIP_tbl TW
where TW.Stock_Loc = @Stock_Loc

delete from Test_WIP_tbl
where Stock_Loc=@Stock_Loc

End 

--To Run Procedure
exec sp_WIP2Stock 'PLT02'

--
Paul

Complete SQL newbie!

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 07:37:54

Couple of thoughts:

"sp_WIP2Stock"

Don't name your Sprocs starting with the prefix "sp_" - that is used by the system and your Sprocs will incur extra-processing as a result.


create procedure sp_WIP2Stock

@Stock_Loc varchar(15)

As

Begin

insert ...
SELECT ...
where TW.Stock_Loc = @Stock_Loc

delete from ...
where Stock_Loc=@Stock_Loc

End

you need a transaction block around the INSERT and DELETE so they function as an ATOMic unit (otherwise someone else COULD change the Stock_Loc on one of the rows between the INSERT and DELETE which would create an inconsistency in your data

P.S. Personally (Belt & Braces) I would check the @@ROWCOUNT after the INSERT is the same as after the DELETE and ROLLBACK if they are different

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 08:28:43

Thanks for the hint on the sp_ thing Kristen

tbh I don't know what a transaction block is nor how to implement one. I do see your point though.

The rowcount is going to be different as these will be additional rows in the stock table.

Thanks

--
Paul

Complete SQL newbie!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-21 : 09:20:22

quote:
Originally posted by Kristen

you need a transaction block around the INSERT and DELETE so they function as an ATOMic unit

And some error handling so that if the insert fails the delete doesn't run.

Things to look up:
Begin Transaction
Commit Transaction
Rollback Transaction
try ... catch

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 15:49:51

"The rowcount is going to be different as these will be additional rows in the stock table."

Sorry, didn't mean quite that, but rather this:


DECLARE @MyInsertCount int,
         @MyDeleteCount int

insert ...
SELECT ...
where TW.Stock_Loc = @Stock_Loc
SELECT @MyInsertCount = @@ROWCOUNT
delete from ...
where Stock_Loc=@Stock_Loc
SELECT @MyDeletedCount = @@ROWCOUNT

IF MyDeletedCount <> @MyInsertCount
BEGIN
    ... Something unexpected happened, bail out ...
END

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 16:27:50

Ah thanks Kristen,
I'll try this tomorrow :)

Paul

--
Paul

Complete SQL newbie!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources