Hi All SQLStars
I am kind of new to developing complex databases in SQL Server 2005, so I hope there is someone out there with the answer to all my prayers.
I have 3 distinct tables
tblContainer
ContNum smallint (primary key)
Length smallint
Stop smallint
ContTypeID tinyint
HookUpID tinyint
tblContTypes
ContTypeID tinyint
ContType nvarchar(30)
tblHookUp
HookUpID tinyint
HookUp nvarchar(30)
I need to make an Insert query (and an update query) as stored procedures that can insert/update rows in the tblContainer, but I want the user not to enter ContTypeID or HookUpID but rather ContType and HookUp. Further there are some conditions to be validated:
WHEN ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'
WHEN ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'
WHEN ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'
WHEN ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'
WHEN ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'
WHEN ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'
WHEN ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'
WHEN ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'
WHEN ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'
WHEN ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'
WHEN ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'
WHEN ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'
WHEN ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'
ContNum can only be a number in the above used intervals, there's a constraint on tblContainer to ensure that.
I've tried this code, but obviously it doesn't work, can you tell me why?
CREATE PROCEDURE usp_InsertContainer
(
@.ContNum smallint,
@.Length smallint,
@.Stop smallint,
@.ContType nvarchar(30),
@.HookUp nvarchar(30)
)
INSERT INTO tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)
WHEN ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'
WHEN ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'
WHEN ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'
WHEN ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'
WHEN ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'
WHEN ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'
WHEN ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'
WHEN ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'
WHEN ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'
WHEN ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'
WHEN ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'
WHEN ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'
WHEN ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'
INNER JOIN tblHookUp on tblContainer.HookUpID=tblHookUp.HookUpID
VALUES(@.ContNum, @.Length, @.Stop, @.HookUp)
The result should look like this:
ContNum, Length, Stop, ContType, HookUp
1, 100, 15, Very Large, Wire
I'd really appriciate your help in this riddle - thanks a bundle.
Kind regards
Tina Nielsen
Denmark
Hi Tina,
Use a "select" statement as the source for the "insert" one.
INSERT INTO dbo.tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)
select
@.ContNum,
@.Length,
@.Stop,
(select ContTypeID from dbo.tblContTypes where ContType = @.ContType),
case
WHEN @.ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'
WHEN @.ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'
WHEN @.ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'
WHEN @.ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'
WHEN @.ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'
WHEN @.ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'
WHEN @.ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'
WHEN @.ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'
WHEN @.ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'
WHEN @.ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'
WHEN @.ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'
WHEN @.ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'
WHEN @.ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'
else null
end
What about adding columns [ContNum_From] and [ContNum_To] to table [tblHookUp], and using:
INSERT INTO dbo.tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)
select
@.ContNum,
@.Length,
@.Stop,
(select ContTypeID from dbo.tblContTypes where ContType = @.ContType),
(select HookUpID from dbo.tblHookUp where @.ContNum between [ContNum_From] and [ContNum_To])
AMB
|||Hi AMB
Thank you very, very much for your rapid reply. I'm quite sure this will solve my problem.
Just to clarify, can I use this as a template for an update and delete query as well?
Again thank very much for helping me out.
Kind regards
Tina Nielsen
Denmark
|||Hi, Tina!
Another option is to use computed column instead of storing data in Database:
Code Snippet
CREATETABLE tblContainer (
ContNum smallintPRIMARYKEY,
Length smallint,
Stop smallint,
ContTypeID tinyint,
HookUpID AS(CASE
WHEN ContNum BETWEEN 100 AND 199 THEN 13
WHEN ContNum BETWEEN 1000 AND 1999 THEN 1
WHEN ContNum BETWEEN 2000 AND 2499 THEN 2
WHEN ContNum BETWEEN 2500 AND 2999 THEN 3
WHEN ContNum BETWEEN 3000 AND 3249 THEN 4
WHEN ContNum BETWEEN 3250 AND 3499 THEN 5
WHEN ContNum BETWEEN 3500 AND 3749 THEN 6
WHEN ContNum BETWEEN 3750 AND 3999 THEN 7
WHEN ContNum BETWEEN 4000 AND 4249 THEN 8
WHEN ContNum BETWEEN 4250 AND 4449 THEN 9
WHEN ContNum BETWEEN 4500 AND 4749 THEN 10
WHEN ContNum BETWEEN 4750 AND 4999 THEN 11
WHEN ContNum BETWEEN 5000 AND 5249 THEN 12
END)
)
So, whenever you insert or update [ContNum] column, in SELECT queries value of HookUpID is calculated automatically. You don't need to store it in database and use additional tricks during update operation.
NOTE, that this option is applicable only if SELECT statement execution speed is not critical! As I know, computed columns affect SELECT statement performance. Although, in this case, when rows count in the table is not much, it's not significant.
|||Hi Tina,
For a "delete" you just need the PK to be deleted, and for "update", well it depends if you will be updating the PK also.
declare @.ContNum
set @.ContNum = 10
delete from
where ContNum = @.ContNum
go
declare @.ContNum int, @.Length int, @.Stop int
set @.ContNum = 10
set @.Length = 20
set @.Stop = 30
update
dbo.tblContainer
set
Length = @.Length,
Stop = @.Stop
where
ContNum = @.ContNum
go
AMB
