Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
goJohn,
You cannot substitute a table with a variable. Use dynamic SQL for that. The reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go