How to Pass a Table Name into a Stored Proc

How should I pass a table name into a stored proc?

First of all, you should NEVER do SQL command compositions on a client app like this, that's what SQL Injection is. (Its OK for an admin tool that has no privs of its own, but not for a shared use application).

Secondly, yes, a parametrized call to a Stored procedure is both cleaner and safer.

However, as you will need to use Dynamic SQL to do this, you still do not want to include the passed string in the text of the executed query. Instead, you want to used the passed string to look up the names of the actual tables that the user should be allowed to query in the way.

Here's a simple naive example:

CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS
-- Counts the number of rows from any non-system Table, *SAFELY*
BEGIN
    DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

    EXEC(@SQL)
END

Some have fairly asked why this is safer. Hopefully, little Bobby Tables can make this clearer:
0
alt text

Answers to more questions:

QUOTENAME alone is not guaranteed to be safe. MS encourages us to use it, but they have not given a guarantee that it cannot be out-foxed by hackers. FYI, real Security is all about the guarantees. The table lookup with QUOTENAME, is another story, it's unbreakable.
QUOTENAME is not strictly necessary for this example, the Lookup translation on INFORMATION_SCHEMA alone is normally sufficient. QUOTENAME is in here because it is good form in security to include a complete and correct solution. QUOTENAME in here is actually protecting against a distinct, but similar potential problem know as latent injection.

I should note that you can do the same thing with dynamic Column Names and the INFORMATION_SCHEMA.COLUMNS table.

You can also bypass the need for stored procedures by using a parameterized SQL query instead (see here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8). But I think that stored procedures provide a more manageable and less error-prone security facility for cases like this.

How to take table name as an input parameter to the stored procedure?

CREATE PROCEDURE xyz 
@TableName NVARCHAR(128) 
AS 
BEGIN 
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT TOP 10 * INTO #Temp_Table_One 
              FROM ' + QUOTENAME(@TableName)
          + N' SELECT * FROM #Temp_Table_One '

 EXECUTE sp_executesql @Sql

END

How to pass list of table names to a stored procedure in SQL Server?

Ok, let's start off with the problems you have in your current set up. Firstly it sounds like you have a design flaw here. Most likely you are using a table's name to infer information that should be in a column. For example perhaps you have different tables for each client. In such a scenario the client's name should be a column in a singular table. This makes querying your data significantly easier and allows for good use for key constraints as well.

Next, your procedure. This is a huge security hole. The value of your dynamic object is not sanitised nor validated meaning that someone (malicious) has almost 100 characters to mess with your instance and inject SQL into it. There are many articles out there that explain how to inject securely (including by myself), and I'm going to cover a couple of processes here.

Note that, as per my original paragraph, you likely really have a design flaw, and so that is the real solution here. We can't address that in the answers here though, as we have no details of the data you are dealing with.

Fixing the injection

Injecting Securely

The basic's of injecting a dynamic object name is to make it secure. You do that by using QUOTENAME; it both delimit identifies the object name and escapes any needed characters. For example QUOTENAME(N'MyTable') would return an nvarchar with the value [MyTable] and QUOTENAME(N'My Alias"; SELECT * FROM sys.tables','"') would return the nvarchar value "My Alias""; SELECT U FROM sys.tables".

Validating the value

You can easily validate a value by checking that the object actually exists. I prefer to do this with the sys objects, so something like this would work:

SELECT @SchemaName = s.[name],
       @TableName = t.[name]
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE s.[name] = @Schema --This is a parameter
  AND t.[name] = @Table; --This is a parameter

As a result, if the FROM returns no values, then the 2 variables in the SELECT won't have a value assigned and no SQL will be run (as {String} + NULL = NULL).

The Solution

Table Type Parameter

So, to allow for multiple tables, we need a table type parameter. I would create one with both the schema and table name in the columns, but we can default the schema name.

CREATE TYPE dbo.Objects AS table (SchemaName sysname DEFAULT N'dbo',
                                  TableName sysname); --sysname is a sysnonym for nvarchar(128) NOT NULL

And you can DECLARE and INSERT into the TYPE as follows:

DECLARE @Objects dbo.Objects;

INSERT INTO @Objects (TableName)
VALUES(N'test');

Creating the dynamic statement

Assuming you are using a supported version of SQL Server, you'll have access to STRING_AGG; this removes any kind of looping from the procedure, which is great for performance. If you're using a version only in extended support, then use the "old" FOR XML PATH method.

This means you can take the values and create a dynamic statement along the lines of the below:

SET @SQL = (SELECT STRING_AGG(N'SELECT * FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',' ')
            FROM sys.schemas s
                 JOIN sys.tables t ON s.schema_id = t.schema_id
                 JOIN @Objects O ON s.name = O.SchemaName
                                AND t.name = O.TableName);

The Stored Proecure

Putting all this together, this will give you a procedure that would look like this:

CREATE PROC schemaName.spDynamicTableName @Objects dbo.Objects AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = (SELECT STRING_AGG(N'SELECT N' + QUOTENAME(t.[name],'''') + N',* FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';',@CRLF) --I also inject the table's name as a column
                FROM sys.schemas s
                     JOIN sys.tables t ON s.schema_id = t.schema_id
                     JOIN @Objects O ON s.name = O.SchemaName
                                    AND t.name = O.TableName);

    EXEC sys.sp_executesql @SQL;

END;

And then you would execute it along the lines of:

DECLARE @Objects dbo.Objects;

INSERT INTO @Objects (SchemaName,TableName)
VALUES(N'dbo',N'MyTable'),
      (N'dbo',N'AnotherTable');

EXEC schemaName.spDynamicTableName @Objects;

Pass a table name to stored procedure as a parameter

how about try this concept :

CREATE TABLE #tempTable(abc int);
declare @strSQL nvarchar(255)
SET @strSQL = 'insert into #tempTable select 123'
EXEC sp_executesql @strSQL
declare @abc int
select top 1 @abc = abc from #tempTable
drop table #tempTable
select @abc

Stored procedure with table name as parameter amongst others

Suggestion 1:
Use QUOTENAME() to handle proper escaping of the table name.

Suggestion 2: You are inserting the value of the parameter into @sql. Don't do that. Instead you should use pameterized the sql.

Suggestion 3: Eliminate the OR logic by conditionally building the query's WHERE clause.

 CREATE PROCEDURE cafgTenantNamesTEST2
    @TableName sysname,
    @Square nvarchar(100) = null,
    @Location nvarchar(100) = null,
    @Name nvarchar(100) = null,
    @NormalizedName nvarchar(100) = null,
    @SharedLand int = 0,
    @FieldNumber int = 0,
    @Description nvarchar(255) = null,
    @Dwelling nvarchar(100) = null
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX)
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName ) + 
    ' WHERE 1=1 '
    IF  @Square IS NOT NULL
      SET @sql = @sql + ' AND ([Square] LIKE   @Square )'  -- still patameterized
   IF @Location IS NOT NULL
      SET @sql = @sql + N'  AND ([Location] = @Loc )'
    ...
    ...
--PRINT @sql
EXEC sp_executesql @sql, N'@Square nvarchar(100), @Loc nvarchar(100)...', @square=@square, @loc=@location  -- the param names can be the same or different, sp_executesql has it's own scope.
END

Sp_executesql can execute parameterized sql in addition to plain sql. It is the underlying system stored procedure that is used by client libraries to execute parameterized code. For example, System.Data.SqlClient.SqlCommand will call sp_executesql if you have added any parameters. It is atypical in that it accepts a variable number of parameters. The msdn docs on sp_executesql provide some good information, but isn't clear. Capturing activity in SQL profiler is the easiest way to see sp_executesql in action.

How to pass table name and column name dynamic in SQL

I think you may need to modify you value passing and your concatenation values.

from this statement you need to remove the semi colon as it will throw error

SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

While passing blank values you need additional quotes

SELECT @final = @sql + 'WHERE ' + @PassedColumnName + ' IS NULL  OR ' + @PassedColumnName + '= '''''

While execution I believe you wanted to execute final instead of SQL

I think below should give your output:

CREATE PROC [lnd].[Get_ANCNotullColumn]( @PassedTableName as NVarchar(255),@PassedColumnName AS 
     NVARCHAR(100)) 
 AS
 BEGIN
 DECLARE @ActualTableName AS NVarchar(255)

SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName

DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' '

DECLARE @final AS NVARCHAR(MAX)
SELECT @final = @sql + 'WHERE ' + @PassedColumnName + ' IS NULL  OR ' + @PassedColumnName + '='''''

EXEC(@final)
END

How to create a simple stored procedure with table name as an input

I'm assuming you want to update a physical table. SQL Server table variables don't work that way, rather they are a way to pass a transient result set to a stored procedure. There is no persistence if your stored procedure does not do so.

If you are looking to update the same table, then just write the procedure to work on that table.

If you are looking to update multiple tables using the same script then you should change your procedure to accept a string parameter that would be the name of the table you want it to work on and then use dynamic SQL in your stored procedure.

Something like

CREATE PROCEDURE updteleform  @tablename sysname
AS
BEGIN

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
update ' + QUOTENAME(@tablename) + '
set Recog= 0
where Recog is null;';

EXEC sp_executesql @sql;

END
GO

And then call it with something like:

EXEC updteleform @tablename = 'table1';
EXEC updteleform @tablename = 'table2';
EXEC updteleform @tablename = 'table3';
...

Pass table name and date as parameters to stored procedure to delete rows with where clause

Just use a parameter in your dynamic statement:

CREATE PROCEDURE usp_delete_qu
   @table NVARCHAR(128),
   @new_date datetime
AS BEGIN
   DECLARE @sql NVARCHAR(MAX);
   DECLARE @rc int

   -- construct SQL
   SET @sql = N'delete FROM ' + QUOTENAME(@table) + N' where modified_date < @new_date'

   -- execute the SQL
   EXEC @rc = sp_executesql @sql, N'@new_date datetime', @new_date
   IF @rc <> 0 PRINT 'Error'
END