Create a temp table in SQL Server

I have a dataset in SSRS that will return a  list of comma delimited strings.

based on the values returned...I need this list of strings turned into a temp table...so I can evaluate the temp table in the stored proc in an In clause.


Please help
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
"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.
0
Robb HillSenior .Net DeveloperAuthor Commented:
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.
0
Olaf DoschkeSoftware DeveloperCommented:
Well, then all you do within the stored proc is
CREATE FUNCTION [dbo].[yourfunctionname]     (@pString VARCHAR(8000), @pDelimiter CHAR(1))

CREATE TABLE #mytemp(value varchar(100));
INSERT INTO #mytemp SELECT value FROM string_split(@pString,@pDelimiter)

Open in new window

and the rest of the code can use #mytemp.

You call that via dbo.yourfunctionname('comma,separated,values',','), you might make ',' the default for @pDelimiter or not have this parameter at all, but it might be handy, once you have a pipe or semicolon delimited string.

In this case you could also declare a table variable and use that instead of a temp table or you can use a global temp table with ## to be usable for multiple stored procs.

On the other side, string_split is fast. How long are your strings, how many values inside?

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Robb HillSenior .Net DeveloperAuthor Commented:
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

Open in new window



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


cf.ic_lSTaxCCorp IN (@tblServices) 

Open in new window



Im not sure I have syntax correct here yet
0
Robb HillSenior .Net DeveloperAuthor Commented:
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
0
Robb HillSenior .Net DeveloperAuthor Commented:
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

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
thanks
0
Olaf DoschkeSoftware DeveloperCommented:
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

Open in new window


Can be done with:
declare @servicesCOUNT int

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

Open 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;

Open 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.
0
Olaf DoschkeSoftware DeveloperCommented:
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
    )

Open in new window


Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.