Alert me get list of records from source table and update column

Transact-SQL
1

Hi

In my RequestLog table, i will get multiple request ids, with different executionid.

i want to update SAPSOURCE Table 'Requestid" column where requestid in ("Reqestlog-requestid") and ExecutionID <> ("Reqestlog-executionid")

DECLARE @varRequestId NVARCHAR(255);
DECLARE @varExecutionId NVARCHAR(255);

select @varRequestId = RequestID , @varExecutionId = _AuditCreatedExecutionId from STA.RequestLog where Source = 'SAPSOURCE' and cast(_AuditCreatedDate as date) = cast(getdate() as date)

UPDATE STA.SAPSOURCE set RequestID = 0 where RequestID in (@varRequestId) and _AuditCreatedExecutionID <> @varExecutionId
the reason is there are records duplicated in the SAPSOURCE Table, where the _AuditCreatedExecutionId is not found in RequestLog Table.

2

Reformatting for readability:

DECLARE @varRequestId NVARCHAR(255);
DECLARE @varExecutionId NVARCHAR(255);

SELECT @varRequestId = RequestID
      ,@varExecutionId = AuditCreatedExecutionId
FROM STA.RequestLog
WHERE Source = 'SAPSOURCE'
      AND cast(AuditCreatedDate AS DATE) = cast(getdate() AS DATE)

UPDATE STA.SAPSOURCE
SET RequestID = 0
WHERE RequestID IN (@varRequestId)
      AND _AuditCreatedExecutionID <> @varExecutionId

You can't do this:

 WHERE RequestID IN (@varRequestId)

unless you make the udpate query dynamic SQL:

DECLARE @sql NVARCHAR(4000);

SET @sql = '
UPDATE STA.SAPSOURCE
SET RequestID = 0
WHERE RequestID IN (' + @varRequestId + ')
      AND _AuditCreatedExecutionID <> ' + @varExecutionId;

EXEC sp_executesql @sql
3

Better, IMHO, to use a Splitter Function to convert the @varRequestId values into a @TempTable and JOIN that to the main table to constrain the values.

Dynamic SQL brings with it issues of Security (User has to have SELECT access to the underlying table, rather than just EXECUTE permission on the Sproc - or you have to do the whole nine-yards with Certificates and the like), it also has risks of SQL Injection - or just plain run-time errors because unexpected characters crept into the @varRequestId variable

4

I don't think there's anything to split. The variable comes from the first query. Wish we knew a bit more here. e.g. why can't the whole thing be done with a simple join?

5

My bad. I didn't look at the code carefully.

If @varRequestId isn't a list?? then can't the O/P just change

WHERE RequestID IN (@varRequestId)

to

WHERE RequestID = @varRequestId

?

6

Exactly, but then he can do it in one query with join. Hope we get more info