A better way to write some SQL?
-
Would anyone have any suggestions how to better write the below SQL code. I do 3 Selects one after another at the start of the code, I think this is a bit shabby!!!
Anyone any Ideas??
CREATE PROCEDURE sproc_Number
(
@deptID int,
@serOff varchar(4) OUTPUT,
@returnNum int OUTPUT
)
AS
DECLARE @Service char(4)
DECLARE @office char(4)
DECLARE @Num int
DECLARE @tempNum int
DECLARE @tempNumTwo int
DECLARE @temp varchar(4)
SELECT @Service = Service, @office = Office FROM Service WHERE DeptID = @deptID
SET @serOff = RTRIM(@Service) + RTRIM(@Office)
SELECT @Num = Num FROM GenNum WHERE @serOff = SO
SET @tempNum = @Num + 1
SELECT @tempNumTwo = Num FROM GenNum WHERE @serOff = SO
IF @tempNumTwo = @TempNum
BEGIN
SET @tempNumTwo = @tempNum + 1
UPDATE GenNum SET Num = @tempNumTwo WHERE SO = @serOff
SET @returnNum = @tempNumTwo
END
ELSE
BEGIN
UPDATE GenNum SET Num = @tempNum WHERE SO = @serOff
SET @returnNum = @tempNum
END
SELECT @returnNum
GO
-
John,
Just ignore the previous post because it is only criticizing and not helping you ( as usual
)Can you post what are the definitions (DDL) of your tables?
* Noel -
CREATE PROCEDURE sproc_Number
(
@deptID int,
@serOff varchar(4) OUTPUT,
@returnNum int OUTPUT
)
AS
DECLARE @Service char(4)
DECLARE @office char(4)
DECLARE @Num int
DECLARE @tempNum int
DECLARE @tempNumTwo int
DECLARE @temp varchar(4)
-- Don't need this, used in subselect below.
-- SELECT Service, Office
-- FROM Service
-- WHERE DeptID = @deptID
-- Don't need this handled in select below. SET @serOff = RTRIM(@Service) + RTRIM(@Office)
-- Modify this to below
-- SELECT @Num = Num FROM GenNum WHERE @serOff = SO
SELECT @tempNum = Num + 1, @tempNumTwo = Num
FROM GenNum
WHERE SO = (select RTRIM(Service) + RTRIM(Office)
FROM Service
WHERE DeptID = @deptID)
-- Done need this now, in select above SET @tempNum = @Num + 1
-- Don't need this used in query above
-- SELECT @tempNumTwo = Num FROM GenNum WHERE @serOff = SO
-- If TempNum = GenNum.Num + 1 where SO = @serOff
-- and @TempNum = GenNum.Num where SO = @serOff
-- How can this Statement ever evaluate to true?
-- The way I see it, They can never be equal
IF @tempNumTwo = @TempNum
BEGIN
SET @tempNumTwo = @tempNum + 1
UPDATE GenNum SET Num = @tempNumTwo WHERE SO = @serOff
SET @returnNum = @tempNumTwo
END
ELSE
BEGIN
UPDATE GenNum SET Num = @tempNum WHERE SO = @serOff
SET @returnNum = @tempNum
END
Return
-- Don't need to select if this is an output parameter
-- SELECT @returnNum
GO
declare @serOff varchar(4),
@ReturnNum int
sproc_Number @deptID = 'foo', @serOff = @serOff OUTPUT, @returnNum = @ReturnNum OUTPUT
select @serOff, @returnNum
-- Are you trying to build a table that holds increments a number for a row, and returns the incremented number?
-- If so how bout this
-- For testing purposes
Create table GenNum (pk int identity, Num int, SO varchar(10))
Create table Service (pk int identity, Service char(4), Office char(2), DeptID int)
insert into GenNum (NUM, SO)
values(1,'AABB')
insert into GenNum (NUM, SO)
values(1,'XXYY')
Insert into Service (Service, Office, DeptID)
Values ('AA', 'BB', 1)
Insert into Service (Service, Office, DeptID)
Values ('XX', 'YY', 2)
GO
CREATE PROCEDURE sproc_Number
(
@deptID int,
@serOff varchar(4) OUTPUT,
@returnNum int OUTPUT
)
AS
SELECT @serOff = RTRIM(Service) + RTRIM(Office)
FROM Service
WHERE DeptID = @deptID
Update GenNum
set @ReturnNum = Num
, Num = Num + 1
where SO = @serOff
GO
Execute this part over and over, see the number increment.Change Depid to 2, it will start incrementing that one.
declare @serOff varchar(4),
@ReturnNum int
exec sproc_Number @deptID = '1', @serOff = @serOff OUTPUT, @returnNum = @ReturnNum OUTPUT
select @serOff, @returnNum
-- Drop Procedure sproc_Number
-- drop table GenNum
-- Drop table Service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply