Stored procedure to insert values into dynamic table

Question

I was wondering if I can make a stored procedure that insert values into dynamic table.

I tried

create procedure asd
(@table varchar(10), @id int)
as
begin
    insert into @table values (@id)
end

also defining @table as table var

Thanks for your help!

Solution 1

This might work for you.

CREATE PROCEDURE asd
(@table nvarchar(10), @id int)
AS
BEGIN
    DECLARE @sql nvarchar(max)
    SET @sql = 'INSERT INTO ' + @table + ' (id) VALUES (' + CAST(@id AS nvarchar(max)) + ')'
    EXEC sp_executesql @sql
END

See more here: http://msdn.microsoft.com/de-de/library/ms188001.aspx

Solution 2

Yes, to implement this directly, you need dynamic SQL, as others have suggested. However, I would also agree with the comment by @Tomalak that attempts at universality of this kind might result in less secure or less efficient (or both) code.

If you feel that you must have this level of dynamicity, you could try the following approach, which, although requiring more effort than plain dynamic SQL, is almost the same as the latter but without the just mentioned drawbacks.

The idea is first to create all the necessary insert procedures, one for every table in which you want to insert this many values of this kind (i.e., as per your example, exactly one int value). It is crucial to name those procedures uniformly, for instance using this template: TablenameInsert where Tablename is the target table's name.

Next, create this universal insert procedure of yours as follows:

CREATE PROCEDURE InsertIntValue (
  @TableName sysname,
  @Value int
) 
AS
BEGIN
  DECLARE @SPName sysname;
  SET @SPName = @TableName + 'Insert';
  EXECUTE @SPName @Value;
END;

As can be seen from the manual, when invoking a module with the EXECUTE command, you can specify a variable instead of the actual module name. The variable in this case should be of a string type and is supposed to contain the name of the module to execute. This is not dynamic SQL, because the syntax is not the same. (For this to be dynamic SQL, the variable would need to be enclosed in brackets.) Instead, this is essentially parametrising of the module name, probably the only kind of natively supported name parametrisation in (Transact-)SQL.

Like I said, this requires more effort than dynamic SQL, because you still have to create all the many stored procedures that this universal SP should be able to invoke. Nevertheless, as a result, you get code that is both secure (the @SPName variable is viewed by the server only as a name, not as an arbitrary snippet of SQL) and efficient (the actual stored procedure being invoked already exists, i.e. it is already compiled and has a query plan).

Solution 3

You'll need to use Dynamic SQL.

To create Dynamic SQL, you need to build up the query as a string. Using IF statements and other logic to add your variables, etc.

Declare a text variable and use this to concatenate together your desired SQL.

You can then execute this code using the EXEC command

Example:

DECLARE @SQL VARCHAR(100)
DECLARE @TableOne VARCHAR(20) = 'TableOne'
DECLARE @TableTwo VARCHAR(20) = 'TableTwo'
DECLARE @SomeInt INT

SET @SQL = 'INSERT INTO '

IF (@SomeInt = 1)
    SET @SQL = @SQL + @TableOne

IF (@SomeInt = 2)
    SET @SQL = @SQL + @TableTwo

SET @SQL = @SQL + ' VALUES....etc'

EXEC (@SQL)

However, something you should really watch out for when using this method is a security problem called SQL Injection.

You can read up on that here.

One way to guard against SQL injection is to validate against it in your code before passing the variables to SQL-Server.

An alternative way (or probably best used in conjecture) is instead of using the EXEC command, use a built-in stored procedure called sp_executesql.

Details can be found here and usage description is here.

You'll have to build your SQL slightly differently and pass your parameters to the stored procedure as arguments as well as the @SQL.