How to manipulate comma-separated list in SQL Server

Question

I have a list of values such as

1,2,3,4...

that will be passed into my SQL query.

I need to have these values stored in a table variable. So essentially I need something like this:

declare @t (num int)
insert into @t values (1),(2),(3),(4)...

Is it possible to do that formatting in SQL Server? (turning 1,2,3,4... into (1),(2),(3),(4)...

Note: I can not change what those values look like before they get to my SQL script; I'm stuck with that list. also it may not always be 4 values; it could 1 or more.

Edit to show what values look like: under normal circumstances, this is how it would work:

select t.pk 
from a_table t
where t.pk in (#place_holder#)

#placeholder# is just a literal place holder. when some one would run the report, #placeholder# is replaced with the literal values from the filter of that report:

select t.pk 
from a_table t
where t.pk in (1,2,3,4) -- or whatever the user selects 

t.pk is an int

note: doing

declare @t as table (
num int
)

insert into @t values (#Placeholder#)

does not work.

Answer 1

Your description is a bit ridicuolus, but you might give this a try:

Whatever you mean with this

I see what your trying to say; but if I type out '#placeholder#' in the script, I'll end up with '1','2','3','4' and not '1,2,3,4'

I assume this is a string with numbers, each number between single qoutes, separated with a comma:

DECLARE @passedIn VARCHAR(100)='''1'',''2'',''3'',''4'',''5'',''6'',''7''';
SELECT @passedIn; -->: '1','2','3','4','5','6','7'

Now the variable @passedIn holds exactly what you are talking about I'll use a dynamic SQL-Statement to insert this in a temp-table (declared table variable would not work here...)

CREATE TABLE #tmpTable(ID INT);

DECLARE @cmd VARCHAR(MAX)=
'INSERT INTO #tmpTable(ID) VALUES (' + REPLACE(SUBSTRING(@passedIn,2,LEN(@passedIn)-2),''',''','),(') + ');';

EXEC (@cmd);

SELECT * FROM #tmpTable;
GO

DROP TABLE #tmpTable;

UPDATE 1: no dynamic SQL necessary, all ad-hoc...

You can get the list of numbers as derived table in a CTE easily. This can be used in a following statement like WHERE SomeID IN(SELECT ID FROM MyIDs) (similar to this: dynamic IN section )

    WITH MyIDs(ID) AS
    (
        SELECT A.B.value('.','int') AS ID
        FROM
        ( 
        SELECT CAST('<x>' + REPLACE(SUBSTRING(@passedIn,2,LEN(@passedIn)-2),''',''','</x><x>') + '</x>' AS XML) AS AsXml
        ) as tbl
        CROSS APPLY tbl.AsXml.nodes('/x') AS A(B)
    )
    SELECT * FROM MyIDs

UPDATE 2:

And to answer your question exactly:

With this following the CTE

insert into @t(num)
SELECT ID FROM MyIDs

... you would actually get your declared table variable filled - if you need it later...