Mixed Table Type with other types as parameters to Stored Procedured c#

Question

Hi,

I am asking about how could i pass multi parameters to a stored procedure, one of these parameters is user defined table. When I tried to do it it shows this error:

INSERT INTO BD (ID, VALUE, BID) values( (SELECT t1.ID, t1.Value FROM @Table AS t1),someintvalue) here @Table is the user defined table parameter.

Msg 116, Level 16, State 1, Procedure UpdateBD, Line 12 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Msg 109, Level 15, State 1, Procedure UpdateBD, Line 11 There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Thank you

Answer 1

You need to pass in the table name as a varchar and your query needs to be a varchar as well. It would look like:

CREATE PROCEDURE MyStoredProc(
    @MyTableNameParameter varchar(50) = null
)
AS

DECLARE @Query nvarchar(MAX)

SET @Query = N'SELECT * FROM ' + @MyTableNameParameter + ' WHERE 1=1'

EXEC (@Query)

GO

I would not recommend using this technique though because it is vulnerable to SQL injection