1

I have a master table "Repairs" and a detail table "RepairDetails" I am trying to write a procedure to update both tables when I send the appropriate parameters from my application. Here is my SQL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateRepair]
    @RepairID bigint,
    @TypeID bigint = NULL,
    @Directions nvarchar(3000) = NULL,
    @NewDetails NewDetails READONLY
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Repairs
    SET
        TypeID = ISNULL(@TypeID, TypeID),
        Directions = ISNULL(@Directions, Directions),
        LastUpdate = SYSDATETIME()
    WHERE RepairID = @RepairID;
    IF @NewDetails IS NOT NULL UpdateRepairDetails;
END

where "NewDetails" is a User-defined table type and "UpdateRepairDetails" is a different stored procedure that takes @RepairID and @NewDetails as parameters.

I have an error and a question. The error message is:

Must declare the scalar variable "@NewDetails"

which I don't understand because it is defined.

And my question is: will the parameters "@RepairID" and "@NewDetails" get automatically passed to the "UpdateRepairDetails" procedure. If not, what is the proper approach to accomplish this?

SezMe
  • 527
  • 8
  • 24

1 Answers1

0

You cannot assign NULL to a table variable. Hence you can't check whether a table variable is NULL.

Second: no. You should call as follows:

EXEC UpdateRepairDetails @RepairID, @NewDetails;
TT.
  • 15,774
  • 6
  • 47
  • 88
  • 1
    To clarify for OP who is having problems. A table is not null; you need to check for the existence of rows (e.g., if exists (select * from xxx) ,,, ). – SMor Aug 13 '17 at 21:59
  • I marked this as the answer but @SMor added a useful comment that completely answers my questions. Thanks to both. – SezMe Aug 13 '17 at 22:49
  • @TT. I still can't get this to work. I've removed the null checking and implemented your code but I still get that same error message. – SezMe Aug 21 '17 at 02:44
  • @SezMe That is weird. I am pretty sure that I pointed out the two problems in your procedure and I can't see any more problems. Can you make a new question with the updated code please? – TT. Aug 21 '17 at 07:09
  • @TT Well, isn't this embarrassing. I shut down the server and my PC and went to bed frustrated. Fired up everything this morning and no longer get the error. I blame Bill Gates. – SezMe Aug 21 '17 at 19:28
  • @TT Well, isn't this even more embarrassing. The error is back. I'll start a new quesiton. – SezMe Aug 21 '17 at 19:35
  • @SezMe Drop a link to the question here, I'll have a look at the new question as well. – TT. Aug 21 '17 at 19:43
  • @TT see here; https://stackoverflow.com/questions/45805242/dont-understand-sql-procedure-error-the-sequel – SezMe Aug 21 '17 at 20:46