Is it possible to pass table name or function name to SQL script as SQLParameter?

Question

C# code which calls sql scripts:

var result = dbContext.QueryScript<Guid>(x => "path to script is here", args.ToArray());

1 Script code:

SELECT [UID] FROM @functionName(@userId, @isMobile)

How to pass [dbo].[fn_SQLFunctionName] function to this script, following way doesn't work:

args.Add(new SqlParameter("functionName", "[dbo].[fn_SQLFunctionName]")) ;

2. Script code:

SELECT * FROM @tableName

How to pass [dbo].[Users] table to this script, following way doesn't work:

args.Add(new SqlParameter("tableName", "[dbo].[Users]")) ;

You cannot parameterize object names; dynamic SQL is required, i.e. `DECLARE @sql NVARCHAR(MAX) = 'SELECT [UID] FROM dbo.' + QUOTENAME(@functionName) + '(@userId, @isMobile)'; EXEC sp_executesql @sql, N'@functionName SYSNAME, @userId INT, @isMobile BIT', @functionName = @functionName, @userId = @userId, @isMobile = @isMobile`. If the schema itself must be parameterized as well, you have to pass (and quote) this separately or `QUOTENAME` fails (and without `QUOTENAME` you are at risk of SQL injection). — Jeroen Mostert, Jul 08 '21 at 17:16

0 Answers0