Tuesday, 27 August 2013

Update schema.table using dynamic sql but encounter [schema] not permitted error

Update schema.table using dynamic sql but encounter [schema] not permitted
error

Here's part of the sql query.
UPDATE '+@Schm+'.Location
SET IsActive ='+CONVERT(VARCHAR(10),1)+'
WHERE LocId ='+CONVERT(VARCHAR(100),@LocID)+'
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
RETURN
END
@schm is the schema name passed as parameter, while the Location is the
name of the table. When I tried to execute this..
The name "AB" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
The "AB" is the schema name.
Apparently @schm.Location was being read as table.column instead of
schema.table.
Any idea what I should do?

No comments:

Post a Comment