Subquery returns more than 1 value

Question

Subquery returns more than 1 value

dhaval_dsa

SSC Veteran

Points: 219
More actions
May 14, 2010 at 12:51 am

#74062

CREATE PROCEDURE USP_UPDATESAL
@DPID INT
AS
BEGIN
DECLARE
@DPTID INT,
@SAL1 MONEY
SET @DPTID=(SELECT deptid FROM Humans WHERE deptid=@DPID)
SET @SAL1=
CASE @DPTID
WHEN 10 THEN 40000
WHEN 20 THEN 38000
WHEN 30 THEN 10000
WHEN 40 THEN 0
WHEN 50 THEN -1
END
UPDATE Humans SET SAL=@SAL1
WHERE deptid=@DPTID
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

dhaval_dsa (5/14/2010)
CREATE PROCEDURE USP_UPDATESAL
@DPID INT
AS
BEGIN
DECLARE
@DPTID INT,
@SAL1 MONEY
SET @DPTID=(SELECT deptid FROM Humans WHERE deptid=@DPID)
SET @SAL1=
CASE @DPTID
WHEN 10 THEN 40000
WHEN 20 THEN 38000
WHEN 30 THEN 10000
WHEN 40 THEN 0
WHEN 50 THEN -1
END
UPDATE Humans SET SAL=@SAL1
WHERE deptid=@DPTID
END

then what to do

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

This query

SELECT deptid FROM Humans WHERE deptid=@DPID

Is returning more than one row, so you need to define which one. Use TOP 1 and an Order by clause.

Although Its superfluous anyway, as an equivalent is

SET @DPTID=@DPID

Clear Sky SQL
My Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

CREATE PROCEDURE USP_UPDATESAL

@DPID INT

AS

UPDATE Humans

SET SAL = CASE deptid

WHEN 10 THEN 40000

WHEN 20 THEN 38000

WHEN 30 THEN 10000

WHEN 40 THEN 0

WHEN 50 THEN -1

END

WHERE deptid = @DPID

The salaries are hard-coded, so why update them one department at a time?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden