Create a temp table in SQL Server

"A dataset in SSRS, that will return a list of comma separated strings".

I'm not sure of which concept you're talking here. but a stored procedure could take in CSV and turn it into a table itself.

Since a few SQL Server versions you can SELECT * FROM String_Split('comma,separated,values',','), which could become a query within a stored proc acting on a single string parameter with this comma separated values. And if you use an earlier SQL Server version, this need to split a string into single values is age old and usually solved with a tally table like http://www.sqlservercentral.com/articles/Tally+Table/72993/

As you see this even has articles going into the detail of string splitting performance details.

So, do you have such a string forwarded to your report, which you want to split into rows and then combine with data or whatever? It could also point out, that the inverse is done before calling into your report to put this CSV together, and you might also simply change data input.

Bye, Olaf.

yea so I have an SSRS report that has a parameter list returned to the stored proc.  

I need to convert it to a temp table one time....as it will be compared in the where clauses about 40 + times.

So rather than reading it back and 4th 40+ times from SSRS I wanted to call it in the beginning of the proc...set to a temp table..and then use that temp table to evalate in the where clause.

I think thats the best way based on what I have to work with.

The strings could be up to 42 possible comma delimited values past from the SSRS .


I did something like this so far.

@Services VARCHAR(MAX)



    

as

begin
 
DECLARE @tblServices TABLE (
    [Data] Varchar not NULL
    )

INSERT INTO @tblServices(data)
SELECT splitdata FROM dbo.fnSplitString(@Services,',') s

Select allOpen in new window

Then I would assume in my where clause I might do the following:

cf.ic_lSTaxCCorp IN (@tblServices) 

Select allOpen in new window

Im not sure I have syntax correct here yet

Well I changed back to temp table.


INSERT INTO #tempTable(data)
SELECT splitdata FROM dbo.fnSplitString(@Services,',') s

now I would need to know how to use that in where clause

This worked...thanks

Select * into #tempTable
from (SELECT splitdata FROM dbo.fnSplitString('@Services',',') ) s
declare @servicesCOUNT int
select @servicesCOUNT = count(*) from #tempTable
select * from #tempTable
drop table #tempTable

Select allOpen in new window

thanks

Sorry I didn't get back earlier, but it seems you made it. Congrats.

When the code you have is all you need the temp table for, this seems unnecessary overhead.

All this code:

Select * into #tempTable
from (SELECT splitdata FROM dbo.fnSplitString('@Services',',') ) s
declare @servicesCOUNT int
select @servicesCOUNT = count(*) from #tempTable
select * from #tempTable
drop table #tempTable

Select allOpen in new window

Can be done with:

declare @servicesCOUNT int

SELECT splitdata FROM dbo.fnSplitString(@Services,',') 
Set @servicesCOUNT = @@ROWCOUNT

Select allOpen in new window

You may have other situations, in which you make more use of the #temp data, but even then you'd rather do:

Select splitdata into #tempTable FROM dbo.fnSplitString(@Services,',');

--...do stuff with #tempTable...

drop table #tempTable;

Select allOpen in new window

You seem to have your own SplitString function here. That's okay, just ensure splitdata is its result column name.

Bye, Olaf.

You had the syntax for declaring a table variable okay, but you have to give your varchar column a width, or it becomes varchar(1) only.

DECLARE @tblServices TABLE (
    [Data] Varchar(100) not NULL
    )

Select allOpen in new window

Bye, Olaf.