I want to Create a table @tablename with data from a User-Defined Table Type but I get the error <<Must declare the table variable "@tablename">>.

ALTER PROCEDURE [dbo].[Prod_EntTable]
@paramEntTable typeTableEnt readonly,
@userid int
AS
BEGIN
Declare @tablename nvarchar(20)
Set @tablename ='mynewtable'   @userid
SELECT EntID, Title INTO @tablename FROM @paramEntTable
END

I tried with EXEC as

EXEC (N'SELECT EntID, Title INTO mynewtable'   @userid   ' FROM '   @paramEntTable)

the error was <<Must declare the table variable "@paramEntTable">>. Any idea what could I do ? Thank you in advance

CodePudding user response：

You want to create a new table every time based on the inputted @userid. This scenario required dynamic TSQL to execute and we can not concatenate type parameter in string so this seems impossible to me.

CodePudding user response：

Firstly, I need to repeat what has been said in the comments, the whole idea of what you want to do here is a design flaw. Creating a table for each user is wrong; it fundamentally breaks basic design rules. Each user should not have their own table, instead you should have one table which has a column UserID. Then when you need data for a specific user, you use a WHERE to filter to that data.

If your goal is to stop users being able to access the data of other users then use Row Level Security (RLS) to ensure that a user can only access to rows they are supposed to.

That being said, you can achieve what you want, but first, to explain why what you have isn't working.

1. Set @tablename ='mynewtable'   @userid
   

   This will error, as @userid is an int, and 'mynewtable' is clearly a literal varchar. As such 'mynewtable' will be implicit cast to an int and you get the following error:

   Conversion failed when converting the varchar value 'mynewtable' to data type int.

2. SELECT EntID, Title INTO @tablename FROM @paramEntTable
   

   SQL is not a scripting language, you cannot use variable to replace a literal. The above, would not be parsed as "SELECT INTO the table with name of the value of the variable @tablename" but as "SELECT INTO the table variable @tablename". This would obviously fail, as you can't use SELECT...INTO to create and insert into a table variable; you have to DECLARE and INSERT INTO.

3. EXEC (N'SELECT EntID, Title INTO mynewtable'   @userid   ' FROM '   @paramEntTable)
   

   This doesn't work because of point 1, and because @paramEntTable isn't in scope. Variables/parameters only exist in the scope they are defined in, they are not accessible from the outer or inner scopes. This is why the syntax EXEC ({SQL Statement}) is frowned upon, you can't parametrise the statement. You should be using sys.sp_executesql.

So, what is the solution? Well, if you take the points above, and concatenate using CONCAT, safely inject your table name, and parametrise your dynamic statement, this will work. This is not tested, due to a lack of sample data and DDL (for typeTableEnt), so any errors you'll need to fix. I also assume everything is on the dbo schema, as the schema has mostly been omitted.

ALTER PROCEDURE [dbo].[Prod_EntTable] @paramEntTable dbo.typeTableEnt READONLY,
                                      @userid int
AS
BEGIN
    DECLARE @TableName sysname, --Correct data type for object names
            @SQL nvarchar(MAX);

    SET @TableName = CONCAT(N'mynewtable', @userid); 
    SET @SQL = N'SELECT EntID, Title INTO dbo.'   QUOTENAME(@TableName)   N' FROM @paramEntTable;';

    EXEC sys.sp_executesql @SQL, N'@paramEntTable dbo.typeTableEnt READONLY', @ParamEntTable;

END;
GO

CodePudding user response：

I think, you should edit your code

ALTER PROCEDURE [dbo].[Prod_EntTable]
@paramEntTable nvarchar(max),
@userid int
AS
BEGIN
Declare @tablename nvarchar(20)
Set @tablename ='mynewtable'   @userid
SELECT EntID, Title INTO @tablename FROM @paramEntTable
END

CodePudding user response：

Hey the main problem with your sp is that you didnt create the table in which you want to select into. So First you need to create your Table if it isnt there. And then you can generate the Input Statement.

Im Not sure about the dynamic Tablenames in direct sql querys, therfore i generate the Select INTO in a second variable and execute it like i would for the creation.

ALTER PROCEDURE [dbo].[Prod_EntTable]
  @paramEntTable typeTableEnt readonly,
  @userid int
AS
BEGIN
   Declare @tablename nvarchar(20)
   Set @tablename ='mynewtable'   @userid

    IF Object_ID(@tablename) IS NULL
    BEGIN
       declare @createtablesql nvarchar(max);
       -- as a variable because of the dynamic table names
       SET @createtablesql = 'your create statement';
       EXECUTE sp_executesql @createTableSql 
    END

    declare @selectIntoSql nvarhcar(max);
    SELECT @selectIntoSql = CONCAT('SELECT EntID, Title INTO ', 
    @tablename,' FROM ', @paramEntTable);

    EXECUTE sp_executesql @selectIntoSql 

END