Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid'
StacyOn Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>I have a temp table that is populated elsewhere in the stored proc:
>@.table
> (
> userid,
> spec1,
> spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid'
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
> >I have a temp table that is populated elsewhere in the stored proc:
> >@.table
> > (
> > userid,
> > spec1,
> > spec2
> > )
> >Spec1 = specialization 1, Spec2 = specialization2
> >Each user has two specializations, and a number of associated skills
> >for that specialization in a table.
> >now I need to loop through this table, pulling out only one userid at
> >a time, for my final query that displays a bunch of other things. I
> >have a UDF that concatenates the string of values from the different
> >rows from the skill table so that it displays the skills as one list:
> >Specialization: DBA
> >Skills: SQL, Data Migration, etc.
> >How do I loop through the @.table to pull out only one userid at a
> >time, then do the insert to another @.secondtable, then loop back
> >through to get another userid'
> >Stacy
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
and candidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:
>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
>> Why would you want to loop?
(snip)
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.
>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
> inner join skill on skill.skillid = candidate_skillsmatrix.skillid
> inner join specialization on specialization.specializationid =>candidate_skillsmatrix.specid
> inner join picklist on picklist.picklistid =>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
> and candidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.
>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get! :)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
Meantime SQL is what we have so there's nothing wrong with what Hugo
said.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
>> I argue for application development based on science, relational.
> Naturally I agree:
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -:)
www.alphora.com|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:
>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
>> You have given some descriptions in English, but description
>> is real SQL code are much less prone to communication errors and
>> misunderstanding.
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -:)
http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
>.
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
>.
'Naturally the new language would imply a new data model as well - the
relational data model instead of the SQL one - but I don't think it
would be difficult to build it on top of SQL Server's existing engine.'
Now I see where your coming from.
All your expertise, education and insight is secondary to your
allegience to MS. So much for your intellectual integrity, you
are, in the end, just another MS fanboy. MS see, David do.
Hypocrite.|||On 5 Jun, 21:01, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> 'Naturally the new language would imply a new data model as well - the
> relational data model instead of the SQL one - but I don't think it
> would be difficult to build it on top of SQL Server's existing engine.'
> Now I see where your coming from.
> All your expertise, education and insight is secondary to your
> allegience to MS. So much for your intellectual integrity, you
> are, in the end, just another MS fanboy. MS see, David do.
> Hypocrite.
Nothing of the kind. I'm suggesting that Microsoft improve their
product by remedying some of its defficiencies. The hypocracy is
committed by those who advocate another commercial product while
accusing others who propose a Microsoft-based solution of lacking
intellectual integrity. My opinion of you just hit rock bottom I'm
afraid.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--