• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 54
  • Last Modified:

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
0
Robb Hill
Asked:
Robb Hill
  • 5
  • 4
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now