USE DBA
GO
CREATE or ALTER PROC usp_UpdateHCROutputTables
(
  @OutputTable OutputTablenamesType READONLY,
  @SessionId INT,
  @Reviewcomments VARCHAR(100),
  @ReviewedBy VARCHAR(100) ,
  @TableIds VARCHAR(100),
  @ReviewedDate DATETIME
)
AS
BEGIN
    UPDATE OutputTablenames
    SET SessionId = @Sessionid,
        Reviewcomments = @Reviewcomments ,
        ReviewedBy = @ReviewedBy ,
        TableIds = @TableIds,
        ReviewedDate = GETDATE()
    FROM @OutputTable T2
    JOIN OutputTablenames T1 ON T2.TableIds = T1.TableIds
END
GO

I'm getting the error likethis:

Msg 206, Level 16, State 2, Procedure usp_UpdateHCROutputTables, Line 0 [Batch Start Line 36]
Operand type clash: int is incompatible with OutputTablenamesType

Please help me to find the Right query to update table passing as parameters in stored procedure

CodePudding user response：

And from your previous question, you have the example of

EXECUTE usp_UpdateHCROutputTables 100, 'No action reqired for this',
       'Prudhvi', 1,'30/06/2022' 

That just will NOT work since "100" is interpreted as an int and that is definitely not a table variable. Using Tsql, you must create a variable of the appropriate type, populate it, and then pass the variable. There is no way to pass some sort of structured literal as the UDTT. A simple example is:

declare @parm dbo.OutputTableNamesType;
insert @parm (...) values (1, ... );

EXECUTE dbo.usp_UpdateHCROutputTables @parm, 'No action reqired for this', 
       'Prudhvi', 1, '20220630';

That is a huge guess. Notice the slight improvements. Use a date format that is not subject to interpretation problems. Schema-qualify your object names. Use statement terminators.

Your previous question had the definition of your UDDT and that suggests that it contains all the columns you need to provide for your update. It that is correct, then why do you have additional parameters? It seems you haven't quite figured out which approach to take with parameter passing?

CodePudding user response：

As mentioned by others, judging from your previous question you have a table type defined like this

CREATE TYPE OutputTableNamesType AS TABLE (
  SessionId INT,
  Reviewcomments VARCHAR(100),
  ReviewedBy VARCHAR(100) ,
  TableIds VARCHAR(100),
  ReviewedDate DATETIME
)

So your stored procedure is wrong. It should be the following:

CREATE or ALTER PROC usp_UpdateHCROutputTables
  @OutputTable OutputTablenamesType READONLY
AS

UPDATE T1
SET SessionId = T2.Sessionid,
    Reviewcomments = T2.Reviewcomments ,
    ReviewedBy = T2.ReviewedBy ,
    ReviewedDate = T2.ReviewedDate
FROM @OutputTable T2
JOIN OutputTablenames T1 ON T2.TableIds = T1.TableIds;

go

Note that:

• The other parameters should really be part of the table type.
• You need to do UPDATE T1 with the alias in order to do an update in a join correctly.
• Do not update the primary key TableIds, that is part of the join anyway.
• Is TableIds a sensible idea to be varchar(100)? It's quite big for a primary key.

You then call the stored procedure as others have mentioned

DECLARE @parm dbo.OutputTableNamesType;
INSERT @parm
  (SessionId, Reviewcomments, ReviewedBy, TableIds VARCHAR(100), ReviewedDate) VALUES (1, 'comment', 'user', '123', GETDATE());

EXECUTE dbo.usp_UpdateHCROutputTables @@OutputTable = @parm;