looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

looping through a recordset

Hi
I am a recordset which I would like to extract a field and make a string
from it, by appending values to it.
e.g
PolicyRef Product
C001 M
C001 B
C001 S
C002 N
C002 C
C002 T
Ideally, what I need is the products in one field:
e.g
PolicyRef Product
C001 M/B/S
C002 N/C/T
I am trying to create a Loop construct, but not having much luck.
Any ideas?
Kind Regards
RickyIf you are using SQL Server 2005 you can do this...
(taken from my blog entry:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select '/' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 2, 100 )
from mailing_list m1
gives...
alex r List A
joe r List A/List B
tony r List A/List B/List C
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Hi
> I am a recordset which I would like to extract a field and make a string
> from it, by appending values to it.
> e.g
> PolicyRef Product
> C001 M
> C001 B
> C001 S
> C002 N
> C002 C
> C002 T
> Ideally, what I need is the products in one field:
> e.g
> PolicyRef Product
> C001 M/B/S
> C002 N/C/T
> I am trying to create a Loop construct, but not having much luck.
> Any ideas?
> Kind Regards
> Ricky
>|||Hi Tony
Thanks for your reply, is there something more dynamic, I've been told to
try and use a WHILE loop and use the ROWCOUNT, but not sure how to implment
this?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> If you are using SQL Server 2005 you can do this...
> (taken from my blog entry:
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> create table mailing_list (
> individual_name nvarchar(100) not null,
> list_name nvarchar(10) not null
> )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> C' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
> A' )
> select distinct
> individual_name,
> list = substring(
> ( select '/' + list_name as [text()]
> from mailing_list m2
> where m2.individual_name = m1.individual_name
> for xml path(''), elements )
> , 2, 100 )
> from mailing_list m1
> gives...
> alex r List A
> joe r List A/List B
> tony r List A/List B/List C
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||What version of SQL Server are you using? If you are using 2005 then use the
FOR XML below because the WHILE loops are iterative whereas the FOR XML is
set orientated so will perform signifcantly better.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
> Hi Tony
> Thanks for your reply, is there something more dynamic, I've been told to
> try and use a WHILE loop and use the ROWCOUNT, but not sure how to
> implment
> this?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> SQL
>|||I'm using SS2K..
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using? If you are using 2005 then use
the
> FOR XML below because the WHILE loops are iterative whereas the FOR XML is
> set orientated so will perform signifcantly better.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
to
'List
'List
a
>|||Ok, here you go....
The basis of this is that you don't have that many Product's so you can
dynamically build the SQL using a cursor, so the performance comes from
orders of magnitude, for instance - far more performant to run around a
cursor once for just 5 rows then to run round a cursor once for every single
policy ref!
I'll leave you with figuring out how to get rid of the '/' on the end of the
string, should just be substring and datalength - if you get stuck then post
back.
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> I'm using SS2K..
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> the
> SQL
> to
> 'List
> 'List
> a
>|||Ok, so rather than leave the job half done...
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
> single policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
> the string, should just be substring and datalength - if you get stuck
> then post back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
>|||Hi Tony
Thank you for the posting, will there be a performance issue, if this is run
for many different policies?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
single
> policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
the
> string, should just be substring and datalength - if you get stuck then
post
> back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
use
a
told
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
from
>|||The actual SQL will produce this which is what does the work...
How many Products do you have?
This SQL will beat cursor or looping by orders of magnitude - try it.
Was the DDL you posted accurate to your own system, if not then best post
the DDL (including indexes) and I'll check to see if you'll get a good
plan...
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'B' ) then 'B/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'C' ) then 'C/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'M' ) then 'M/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'N' ) then 'N/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'S' ) then 'S/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'T' ) then 'T/' else
'' end
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi Tony
> Thank you for the posting, will there be a performance issue, if this is
> run
> for many different policies?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> single
> the
> post
> SQL
> use
> a
> told
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> from
>|||Hi Tony
The amount of products in a policy can range from anywhere to 1 (default) to
about 5?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%237tx4pdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> The actual SQL will produce this which is what does the work...
> How many Products do you have?
> This SQL will beat cursor or looping by orders of magnitude - try it.
> Was the DDL you posted accurate to your own system, if not then best post
> the DDL (including indexes) and I'll check to see if you'll get a good
> plan...
> select PolicyRef,
> Concat = substring( Concat, 1, len( Concat ) - 1 )
> from (
> select PolicyRef,
> Concat = case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'B' ) then 'B/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'C' ) then 'C/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'M' ) then 'M/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'N' ) then 'N/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'S' ) then 'S/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'T' ) then 'T/'
else
> '' end
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> ) as c
> order by PolicyRef
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
of
'+'
a
from
r',
r',
a
luck.
>

Loop through a recordset to populate columns in a temp table

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?

I have a recordset that looks like:

SELECT
a.Date,
a.Client --contents: Joe, Frank, Fred, Pete, Oscar
FROM
dbo.tblAppointments a
WHERE
a.date between ...(first and last day of the selected month)

What I want to do is to create a temp table that has 31 columns
to hold appointments and insert into each column any appointments for
the date...

CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
nvarchar(500), Day3 nvarchar(500), etc...)

Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

INSERT INTO
#Appointments(Day1)
SELECT
a.Client
FROM
dbo.tblAppointments a
WHERE
a.date = (...first day of the month)

(LOOP to Day31)

The results would look like
Day1 Day2 Day3 ...
Row1 Joe, Pete
Frank,
Fred

Maybe there's an even better way to handle this sort of situation?
Thanks,
lqYou can use a query to do reports like this without looping or temp tables.
As specified it seems a little strange to list the dates horizontally since
there is apparently no data on the vertical axis but the rest is really just
a matter of formatting once you have the basic query. Formatting is best
done client-side rather than in the database.

DECLARE @.dt DATETIME
/* First date of the month */
SET @.dt = '20050501'

SELECT
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=0 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=1 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=2 THEN client END),
...
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=30 THEN client END)
FROM tblAppointments
WHERE date >= @.dt
AND date < DATEADD(MONTH,1,@.dt)
GROUP BY client

--
David Portas
SQL Server MVP
--|||laurenq uantrell wrote:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
> I have a recordset that looks like:
> SELECT
> a.Date,
> a.Client --contents: Joe, Frank, Fred, Pete, Oscar
> FROM
> dbo.tblAppointments a
> WHERE
> a.date between ...(first and last day of the selected month)
> What I want to do is to create a temp table that has 31 columns
> to hold appointments and insert into each column any appointments for
> the date...
> CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
> nvarchar(500), Day3 nvarchar(500), etc...)
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.
> INSERT INTO
> #Appointments(Day1)
> SELECT
> a.Client
> FROM
> dbo.tblAppointments a
> WHERE
> a.date = (...first day of the month)
> (LOOP to Day31)
>
> The results would look like
> Day1 Day2 Day3 ...
> Row1 Joe, Pete
> Frank,
> Fred
> Maybe there's an even better way to handle this sort of situation?
> Thanks,
> lq

You're talking about crosstab queries. Here's a page of links that may
be of use:

http://www.google.com/custom?q=cros...ID%3A1%3B&hl=en

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
>...
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.

I'm a afraid that loop is what you will have to do. And write 31
UPDATE statements, one for each day of the month. There are ways to
build comma-separated lists with set-based statements, but the
methods used are unsupported and undefined, and cannot be trusted.
So the only way to build a CSV is to run a cursor.

OK, you don't really need 31 UPDATE statements. You could aggregate
data into a table with one per date, and then at then end run a
31-way self cross-join to produce the final result.

Certainly, a client program is much better apt to do this sort
of thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David, thanks for that. I thought it must be pretty straignt forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||David, thanks for that. I thought it must be pretty straight forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||Erland,
My solution is to run a UDFwithin a View that creates the comma
separated list for each date of appointments. That part works fine on a
single date, and now I'm just figuring out how to loop through the
dates: first day of the month + 31 days.
Thanks.
lq|||Erland,
You got me in the right direction and the solution works very fast:

The stored procedure:

@.ClienID int,
@.dt datetime /* first day of the selected month */

AS

DECLARE @.dtEnd datetime
SET @.dtEnd = DATEADD(DAY,-1,DATEADD(MONTH,1,@.dt)) /* last day of the
selected month */

SELECT
dbo.fn_ClientSked(@.dt, @.ClientID) AS D1,
dbo.fn_ClientSked(DATEADD(DAY,1,@.dt), @.ClientID) AS D2,
dbo.fn_ClientSked(DATEADD(DAY,2,@.dt), @.ClientID) AS D3,
etc...(for D4-D28)
CASE WHEN DATEADD(DAY,28,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,28,@.dt), @.ClientID) END AS D29,
CASE WHEN DATEADD(DAY,29,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,29,@.dt), @.ClientID) END AS D30,
CASE WHEN DATEADD(DAY,30,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,30,@.dt), @.ClientID) END AS D31

The UDF:

CREATE function dbo.fn_ClientSked(@.dtX as DateTime, @.ClientID as int)
returns
nvarchar(500)
AS
begin
declare @.ret_value nvarchar(500)
SET @.ret_value=''
Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments
WHERE
tblAppointments.ClientID = @.ClientID
AND
@.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
RETURN CASE WHEN LEN(@.ret_value)>0 THEN
RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
end

Note: This particular UDF returns all appointments by day of the month
for the same client to populate a monthly calendar. It can be easily
modified to show appointments by employee, etc.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> You got me in the right direction and the solution works very fast:
>...
> declare @.ret_value nvarchar(500)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
> FROM dbo.tblAppointments
> WHERE
> tblAppointments.ClientID = @.ClientID
> AND
> @.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
> RETURN CASE WHEN LEN(@.ret_value)>0 THEN
> RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
> end

While it may work and be fast, it relies on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Now you have me worried. Because I am using a UDF within the SELECT
statement? Isn't that what they're for?
I chose this solution because the other solutions I conceived required
31 server calls to populate 31 subforms (one for each day of the week.)
With the method above the data is all shoved down the pipe as one row.
Isn't that a preferable solution where a simple string output is all
that's required for each date?
lq|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Now you have me worried. Because I am using a UDF within the SELECT
> statement? Isn't that what they're for?

No, because of

Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments

This can give you want you want, or just one appointment. Or something
else. The result of this construct "concatentate aggregation" is not
defined.

If you want to play safe, write a cursor instead. (But you can still
do this in a UDF.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, thanks for the info. I hate messing with cursors...
lqsql

Lookup component question

Hi,

i am doing a lookup to insert new records when the lookup has failed.

this works perfectly normally. however when my recordset has a name-column of type string with width 5 and my lookup-table has a name-column of char(20) the lookup will always fail and henc always inserting new records although the name "foo" should match.

is there a workaround for this, or do the compare-columns always have to be of the same type/length ?

I would say it is always a good practice to have matching datatypes. I was not aware that lookup transform treated this situation as error; but it does not surprise me.

You can add a Data conversion before the lookup to assure matching data types

Lookup

Hi,

Before inserting records of a recordset I would like to check that each one of these records doesn't exist in the destination table 1. If the records exist, they should be redirect to other table 2.

For this purpose I use a lookup task. The referende table is the destination table 1. The green line is linked to table 2 and the red line is linked to table 1.

The first execution works properly and copies de records to table 1, but on the next executions the records are copied to table 1 instead of being copied to table 2.

Any idea?

Thanks.

What do you mean when you say next executions?

Are you using a for each/next loop or just running the packing over again?

You could try adding a unique index on table 1, skip the lookup, and insert records straight from the source to the destination with error rows to sent to table 2.

|||I mean just running the packing over again.|||

Andrew,

It looks like the Lookup is not finding any match in the 2nd execution. The lookup transformation is case sensitive; check that the CasInG of incoming rows and the rows also match, or use Upper/lower function in both the source and the lookup.