Using Variables Passing Into MSSQL Stored Proc as part of queries

I want to use the value from variables that is passed into the stored proc. Is that allow?

For example, I want to pass CID=5,SID=4 Into an Update Stored Proc

and it looks like this:

CREATE PROCEDURE Update @CID nvarchar(4),@SID nvarchar(4)
AS

DELETE FROM [User"+@CID+@SID+"] 

GO;

In which is like “DELETE FROM [User54]”

But I want to dynamically done given the parameter

Can it be done and how is it done?

Thanks

Answer

You must use dynamic SQL. To do it safely, ensure the created object name is properly delimited using the quotename function.

Like this:

CREATE OR ALTER PROCEDURE UpdateSomeTable @CID nvarchar(4), @SID nvarchar(4)
AS
begin
    declare @tableName nvarchar(500) = quotename(concat('User',@CID,@SID));
    declare @sql nvarchar(max) = concat('DELETE FROM ',@tableName);

    --print @sql
    exec sp_executesql @sql 
end