I'm working on a logistics system with inputs from our users.
I'm using 
Upon pressing any button the value shown is saved as a variable and the SQL-statement is being executed.
A "normal" statement would looks like this:
INSERT INTO dbo.table (row1, row2, row3) VALUES (val1, @Input_User, val3)
The value of the variable, which has the value of the pressed tile, is being inserted for row2.
Now, imagine the user wants 3 pallets to be picked up: Since the forklift can only pick up one pallet at the time I have to create 3 transport orders out of one button press.
I tried using a Case When statement that looked somewhat like this:
CASE @Input_User <---- parameter by GAPTEQ which has the value of the pressed tile
WHEN '1 Palette' THEN INSERT INTO table(row1, row2 ,row3) VALUES (val1, val2, val3)
WHEN '2 Paletten' THEN INSERT INTO table(row1, row2 ,row3) VALUES (val1, val2, val3), (val1, val2, val3)
...
which didn't work. My intent was to insert two rows for '2 Paletten', three for '3 Paletten' and so on.
What I'm looking for is a way to make as many inserts as the tile the user presses shows with a single sql statement (if that is even possible).
What ways are there?
EDIT: Changed the question quite a bit for clarity. I hope it is much more understandable now.
CodePudding user response：
Looking at this again I think you want a table value parameter then you can just do the following (without the case statement)
INSERT INTO YOURTABLE
SELECT * FROM TABLEVALUEPARAM
In fact the Microsoft page on TVP has this exact example as their first example.