A slightly different approach from the previous two, triggered by a gruelling telephone survey I was foolish enough to agree to participate in just a couple of weeks ago. There must have been two hundred questions and it took 35 minutes.

Since all four statements are updates of the same table and must occur as a transaction, I'd change the code to update the table only once, like so - given the current calling syntax:

ALTER PROCEDURE [dbo].[UpdateSurvey]

-- Add the parameters for the stored procedure here

@ID int, -- explanation

@response nvarchar(50), -- explanation

@buildingpoint nvarchar(50), -- explanation

@ID1 int -- explanation

AS

SET NOCOUNT ON;

UPDATE c SET

ques_1= CASE WHEN @ID1 = 1 THEN x.response ELSE ques_1 END,

q1buildpoint = CASE WHEN @ID1 = 1 THEN y.buildingpoint ELSE q1buildpoint END,

ques_2a = CASE WHEN @ID1 = 2 THEN x.response ELSE ques_2a END,

q2abuildpoint = CASE WHEN @ID1 = 2 THEN y.buildingpoint ELSE q2abuildpoint END,

ques_2b = CASE WHEN @ID1 = 3 THEN x.response ELSE ques_2b END,

q2bbuildpoint = CASE WHEN @ID1 = 3 THEN y.buildingpoint ELSE q2bbuildpoint END,

ques_2c = CASE WHEN @ID1 = 4 THEN x.response ELSE ques_2c END,

q2cbuildpoint = CASE WHEN @ID1 = 4 THEN y.buildingpoint ELSE q2cbuildpoint END

FROM survey c

OUTER APPLY (SELECT response FROM surveyanswers t2 WHERE id = @response) x

OUTER APPLY (SELECT buildingpoint FROM surveyanswers t2 WHERE id = @buildingpoint) y

WHERE c.cid = @ID

RETURN 0

Christian makes a good point about the "magic number". Can you post up some code to show how this stored procedure is run? We might figure out a way to eliminate it.