looping with SQL

Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFOYou don't need to loop. It would have been nice if you supplied exact DDL
and real INSERT statements. Here's an untested solution:
select
o.*
from
MyTable
where
o.id in
(
select top 1
i.id
from
MyTable i
where
i.Company = o.Company
order by
case
when i.Title like '%CEO%' then 1
when i.Title like '%CFO%' then 2
else 3
end
, o.id
)
It's not clear what you mean by the "first" title, so I used id as the
tiebreaker.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Logger" <Logger@.discussions.microsoft.com> wrote in message
news:635B49E6-0714-46FA-997D-B44B4F1F0085@.microsoft.com...
Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFO

Looping vs. Set operation - question

Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran DjuranovicIn the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join o
n IDs, because IDs from StaginTable (table variable in my case) are auto-inc
remented when populated from an XML file, so they don't necesserally match t
he IDs from ProductionTable.
Thanks
Goran
"JT" <someone@.microsoft.com> wrote in message news:ePL0zi8QGHA.3916@.TK2MSFTN
GP11.phx.gbl...
In the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||From your question, it sounded as if you have a situation where you are inse
rting from a staging table into a production table but the problem is that s
pecific rows cause the insert to fail. I was thinking perhaps the situation
is that the new row contains an ID that conflicts with the unique key constr
aint in the production table.
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:uKEm
7QHRGHA.3916@.TK2MSFTNGP11.phx.gbl...
Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join o
n IDs, because IDs from StaginTable (table variable in my case) are auto-inc
remented when populated from an XML file, so they don't necesserally match t
he IDs from ProductionTable.
Thanks
Goran
"JT" <someone@.microsoft.com> wrote in message news:ePL0zi8QGHA.3916@.TK2MSFTN
GP11.phx.gbl...
In the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Goran Djuranovic wrote:
> Hi JT,
> Thanks for your response, but you didn't answer my question.
That's because you didn't supply enough information. You didn't tell us
what "row failed" means. I assume you got an error message, so it would
help to state what the message is. Posting DDL also usually helps.
The point of JT's example is that if the error was a constraint
violation then you can use a query to filter out that row during the
INSERT. You can use a similar query to see which row(s) would have
failed. Since you didn't tell us what the error message is or show us
any data we can't give you the whole solution.
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
--|||The short answer is, you have to use a cursor if you want isolate the exact
row where the error occured.
JT supplied you with a process for eliminating the errors, in this case dupi
cate key errors. You can also get null errors, data type conversion errors,
and constraint errors, not to mention truncation of data which can be lost
without generating an error.
Unless you know exactly what data is coming through and can identify the exp
ected errors ahead of time, you will have to use a cursor or have your appli
cation loop therough the records and make individual calls to the database,
which amounts to the same thing (although doing it in the app is more portab
le). There is another alternative, and that is to use a DTS package to load
the data and set the max errors accordingly. When the package is done inse
rting into the table you can use a select similar to what JT provided to ide
ntify the failed rows.
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Have you looked at Savepoints. They are a mechanism to roll back
portions of transactions. You create a savepoint using the SAVE
TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
TRANSACTION savepoint_name statement to roll back to the savepoint
instead of rolling back to the start of the whole transaction.|||Thanks to your responses, guys. I decided to go with the SET operation, and
before doing SET insert I just validate the data (not 100% safe but what is?
:-) ).
Goran Djuranovic
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142043315.953389.314120@.v46g2000cwv.googlegroups.com...
> Have you looked at Savepoints. They are a mechanism to roll back
> portions of transactions. You create a savepoint using the SAVE
> TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
> TRANSACTION savepoint_name statement to roll back to the savepoint
> instead of rolling back to the start of the whole transaction.
>

LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.

> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql

LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>

looping to get correct data

I'm a newbie so I'll explain what I'm trying to achieve the best I can ...

I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.

How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?

Thanks!
You could do looping by using cursors by usually you should be able to formulate the query in a required way and it will be more performant.
How does the query look like? If it is simple enough you might achieve the result by using DISTINCT argument of the SELECT clause. Have a look at http://msdn2.microsoft.com/en-us/library/ms176104.aspx|||

I agree with Anton, if you formulate your query correctly you should not have to do this work on the client side. This also reduces internet traffic which could additionally speed up your end-to-end performance. If you could formulate the goal of your query as well as the structure of your tables and provide your current query, we could likely help you to write a SQL query that does the trick without this overhead.

Thanks,

John (MSFT)

looping to get correct data

I'm a newbie so I'll explain what I'm trying to achieve the best I can ...

I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.

How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?

Thanks!
You could do looping by using cursors by usually you should be able to formulate the query in a required way and it will be more performant.
How does the query look like? If it is simple enough you might achieve the result by using DISTINCT argument of the SELECT clause. Have a look at http://msdn2.microsoft.com/en-us/library/ms176104.aspx|||

I agree with Anton, if you formulate your query correctly you should not have to do this work on the client side. This also reduces internet traffic which could additionally speed up your end-to-end performance. If you could formulate the goal of your query as well as the structure of your tables and provide your current query, we could likely help you to write a SQL query that does the trick without this overhead.

Thanks,

John (MSFT)

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

sql

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

Looping thru time..

I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.

I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.

Any thoughts on how I can do this?

Interval 1 = 12:00 AM

Interval 2 = 12:30 AM

Interval 3 = 1:00 AM

etc. etc.

Thanks!

M

I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:

Code Snippet

--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns

--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits

--drop #digits
drop table #digits

|||

Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).

The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.

|||

Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks

|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...

Looping thru time..

I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.

I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.

Any thoughts on how I can do this?

Interval 1 = 12:00 AM

Interval 2 = 12:30 AM

Interval 3 = 1:00 AM

etc. etc.

Thanks!

M

I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:

Code Snippet

--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns

--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits

--drop #digits
drop table #digits

|||

Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).

The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.

|||

Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks

|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...

looping thru a table and updating the contents

Hello, Its hard trying to explain this.

I have 3 tables

Table 1 is where the users are stored, each user has a username and a userrank

Table 2 is where the points that decides the userrank are stored

Table 3 contains the available userranks like this

Table 1 (user_list) looks briefly like this:
username nvarchar(20),
userrank int, -- Reference to Table3 id
... alot more fields

Table 2 (settings_profile) looks like this:
username nvarchar(20),
total_active_points int,
... some more fields

Table 3 (data_ranks) looks like this:
id int primary key auto inc,
rankname nvarchar(20),
min_pts int,
max_pts int

Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:

WHILE(SELECT @.username= username, @.id= id, @.temp1= ap_sentmails, @.temp2= ap_createdthreads, @.temp3= ap_createdanswers, @.temp4= ap_signguestbook, @.temp5= ap_blogcomment, @.temp6= ap_createblogentry, @.temp7= ap_profilefirsttime, @.temp8= ap_profilephoto, @.temp9= ap_activateguestbook, @.temp10= ap_addnewfriend, @.temp11= ap_superguruvote, @.temp12= ap_forumtopicvote, @.temp13= ap_labervote, @.temp14= ap_funstuffitemvote, @.temp15= ap_movievote, @.temp16= ap_actorvote, @.temp17= ap_money_newWHERE(created<Dateadd(dd,-7, @.todaysdate))
BEGIN
SET @.sum= 0
SET @.sum= @.temp1+ @.temp2+ @.temp3+ @.temp4+ @.temp5+ @.temp6+ @.temp7+ @.temp8+ @.temp9+ @.temp10+ @.temp11+ @.temp12+ @.temp13+ @.temp14+ @.temp15+ @.temp16+ @.temp17
UPDATE settings_profileSET total_active_points= total_active_points- @.sumWHERE(username= @.username)
DELETEFROM konto_daylistWHERE(id= @.id)
END

Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.
Something like this...

DECLARE @.username nvarchar(20)
DECLARE @.pts int, @.rank int

...something that starts a loop thru table A (user_list) and get the username into @.username...

SELECT @.pts =total_active_points FROM settings_profile WHERE (username = @.username)
-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranks
SELECT @.rank = id FROM data_ranks WHERE (pts_min => @.pts AND pts_max < @.pts)
UPDATE user_list SET rank = @.rank WHERE (username = @.username)

...next persion in the loop...

This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...

But how do i loop thru all the users? with a cursor?

I don't have time to write this for you, but here's the line of attack to use:

Create and test a query that computes the the correct score for each user.

then, issue an update in this format:

update settings_profile

set total_active_points = ( the query from above where settings_profile.username = other_table.username)

|||

Hi, i dont truly understand what you meant. I can compute the score for each user If i have their username, thats why i need to create a loop that loops thru every post in table A, so that i can update every single post with the rank they earned from the points. I need to find someway to loop thru all posts.

|||

No, you do not need to write a loop thru the users.

This is because the SQL Update statement provides an implied loop for you.

Example:

createtable xxx(idint, textvaluevarchar(20))

insertinto xxxvalues(1,'Nikki')

insertinto xxxvalues(2,'John')

update xxxset textvalue='Ginger'

select*from xxx

You will note that every xxx record now has Ginger in the textvalue column.

The update statement IS a loop!

And, while it is processing any given xxx record, it knows the value in each of that record's columns.

I do not have to hard-code the value that will be used to set textvalue with.

I can issue a query instead.

So, let's create a new table called nametable.

createtable nametable(idint,namevarchar(20))

insertinto nametablevalues(1,'Amber')

insertinto nametablevalues(2,'Tracy')

update xxx set textvalue = (select name from nametable where nametable.id = xxx.id)

select * from xxx

Note that the name for record 1 is now Amber and the name for record 2 is now Tracy

Hope that helps clarify things.

|||

Hi,

Its working like a clock now, thanks for the explanation.

I build this line with the example code you sent, and its brilliant.

UPDATE profile_publicinfoSET userrank=(SELECT data_userrank.idFROM settings_profileINNERJOIN data_userrankON(data_userrank.pointsmin<= settings_profile.total_active_pointsAND data_userrank.pointsmax> settings_profile.total_active_points)WHERE(settings_profile.username= profile_publicinfo.username))

|||

Great!

Now all you have to do is mark the thread as answered, so other folks don't spend their time trying to help you only to find out you don't need help anymore! :)

The key to being ultra-productive with SQL is to think in terms of "sets of data" instead of looping thru records of data one at a time.

|||

Then i just have to ask, is it possible to update several records with a IF statement?

Like this,

UPDATE clubsSET isactive=(IF(clubs_account.money-clubs_account.cost< 0SELECT 1) ELSE (SELECT 0)FROM clubs_account)

Thats money - expenses, if its less then 0, return a 1 otherwise return a 0

|||

The SQL equivalent of an IF statement is a CASE statement.

But don't try to write it the way your if statement was coded.

Try this instead:

update clubs
set isactive = (select case clubs_account.money - clubs_account.cost < 0
when true then 1
else 0
end case
from clubs_account
where clubs_account.? = clubs.?
)

|||

Looks very good, but i just keeps getting an error when i tried it (i also tried to put () around the math calculations, but i still get the same result),

The clubs.inactive is a bit field if that is of any importance.

UPDATE clubsSET isactive=(SELECTCASE club_konto.money- club_konto.cost< 0WHEN trueTHEN 1ELSE 0ENDCASEFROM club_kontoWHERE club_konto.clubid= clubs.id)

Incorrect syntax near '<'.

|||

bit fields are 1s and 0s, not true and false.

Also, I work in too many different languages. :( It's END, not END CASE.

createtable clubs(idint, isactivebit)

createtable club_konto( clubidint,moneyint, costint)

insertinto clubsvalues(1,1)

insertinto clubsvalues(2,1)

insertinto clubsvalues(3,0)

insertinto clubsvalues(4,0)

insertinto club_kontovalues(1,50,10)

insertinto club_kontovalues(2,10,50)

insertinto club_kontovalues(3,50,10)

insertinto club_kontovalues(4,10,50)

select*from clubs

select*from club_konto

UPDATE clubs

SET isactive=(SELECTCASEWHEN club_konto.money- club_konto.cost< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

select*from clubs

I have to caution you about this - the select statement used to populate the isactive column must NOT return more than one row of data.

If it can return more than one row, you need to change it so it only returns one row. So, if there can be two or more club_konto records per club, the query above will not work. Instead you would need it to be something like this:

UPDATE clubs

SET isactive=(SELECTCASEWHEN sum(club_konto.money)- sum(club_konto.cost)< 0THEN 1ELSE 0END

FROM club_kontoWHERE club_konto.clubid= clubs.id)

|||

I see that i need to learn alot more about how data is handeled inside MS SQL 2005, its working perfect with your example,

Thanks alot

(I have afew more SPs with While loops that i have to go thru now and see if i can upgrade them according to the new update way.)

looping through XML with xquery

Hello,

for example:

declare @.xml xml
select @.xml = (select * from table for xml raw, elements)

... now i want to iterate through @.xml and get the values from field ID:

declare @.id int, @.x int, @.y int
select @.x = @.xml.value('data(count(/*))','int')
set @.y = 1
while @.y <= @.x begin
select @.id = @.xml.value('data(/row/ID)[' + cast(@.y as varchar) + ']','int')
set @.y * @.y + 1
end

... this is not working because for value() only string literals are allowed, so how can i do this?

thank you,
Helmut

You are on the right track, but you need to use the sql:variable function to do this. sql:variable gives you access to variables and parameters in scope.

http://msdn2.microsoft.com/en-us/library/ms188254.aspx

so you should be able to write your query as:

select @.id = @.xml.value('data(/row/ID)[sql:variable(@.y)]','int')

This also has the added advantage of avoiding sql or xquery injection.

|||Superb, works perfect!

thank you very much,
Helmut
|||SELECT SearchCriteriaXML.value('data(//ColumnName)[sql:variable(@.i)]','varchar(100)')
FROM SearchColumn INNER JOIN SearchCriteria ON SearchColumn.SearchCriteriaId = SearchCriteria.Id
WHERE (SearchCriteria.Id = 1) AND (SearchColumn.Id = 1)

I'm Getting below error for above query

Msg 2225, Level 16, State 1, Line 20
XQuery [SearchCriteria.SearchCriteriaXML.value()]: A string literal was expected
sql

looping through xml document with OpenXML

Hi,
I am trying to compare records from an XML document to records in a table.
If the records in the XML document are not in the table then I wish to
insert those records.
How do I loop through the XML document within a store proc?
Has anyone done this who can provide a sample.
Thanks
Rather than looping, it'd probably be more efficient to use a NOT IN clause.
Have a look at the following example and see if you can adapt it for your
data:
USE Northwind
GO
DECLARE @.productsDoc nvarchar(2000)
SET @.productsDoc = '
<Products>
<Product ProductID="1">
<ProductName>Chai</ProductName>
<UnitPrice>10</UnitPrice>
</Product>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Products>'
DECLARE @.h integer
EXEC sp_xml_preparedocument @.h OUTPUT, @.productsDoc
SET IDENTITY_INSERT Products ON
INSERT Products (ProductID, ProductName, UnitPrice)
SELECT * FROM
OPENXML(@.h, 'Products/Product', 2)
WITH
(
ProductID integer '@.ProductID',
ProductName nvarchar(40),
UnitPrice money
)
WHERE ProductID NOT IN
(SELECT ProductID FROM Products)
SET IDENTITY_INSERT Products OFF
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:8CF49B69-9E84-4B7D-96AE-C2E0FCE854E2@.microsoft.com...
Hi,
I am trying to compare records from an XML document to records in a table.
If the records in the XML document are not in the table then I wish to
insert those records.
How do I loop through the XML document within a store proc?
Has anyone done this who can provide a sample.
Thanks
|||Thanks Graeme.
Looks like your code should work and I will give it a try.
"Graeme Malcolm" wrote:

> Rather than looping, it'd probably be more efficient to use a NOT IN clause.
> Have a look at the following example and see if you can adapt it for your
> data:
> USE Northwind
> GO
> DECLARE @.productsDoc nvarchar(2000)
> SET @.productsDoc = '
> <Products>
> <Product ProductID="1">
> <ProductName>Chai</ProductName>
> <UnitPrice>10</UnitPrice>
> </Product>
> <Product ProductID="101">
> <ProductName>Porridge</ProductName>
> <UnitPrice>16</UnitPrice>
> </Product>
> <Product ProductID="102">
> <ProductName>Haggis</ProductName>
> <UnitPrice>19</UnitPrice>
> </Product>
> </Products>'
> DECLARE @.h integer
> EXEC sp_xml_preparedocument @.h OUTPUT, @.productsDoc
> SET IDENTITY_INSERT Products ON
> INSERT Products (ProductID, ProductName, UnitPrice)
> SELECT * FROM
> OPENXML(@.h, 'Products/Product', 2)
> WITH
> (
> ProductID integer '@.ProductID',
> ProductName nvarchar(40),
> UnitPrice money
> )
> WHERE ProductID NOT IN
> (SELECT ProductID FROM Products)
> SET IDENTITY_INSERT Products OFF
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:8CF49B69-9E84-4B7D-96AE-C2E0FCE854E2@.microsoft.com...
> Hi,
> I am trying to compare records from an XML document to records in a table.
> If the records in the XML document are not in the table then I wish to
> insert those records.
> How do I loop through the XML document within a store proc?
> Has anyone done this who can provide a sample.
> Thanks
>
>

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

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?
Stacy
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
|||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:
>
>
>
> 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
andcandidate_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)[vbcol=seagreen]
(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
>andcandidate_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.googlegr oups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> 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.. .
>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
sql

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:
>
>
>
>
>
>
> 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 tex
t -
> - 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)
(snip)[vbcol=seagreen]
>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/davidpor...ibrary/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...[vbcol=seagreen]
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> Naturally I agree:
> http://blogs.conchango.com/davidpor...al-Futures.aspx[
/vbcol]
'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...
>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 -
[url]http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html[/u
rl]