> Please excuse my trying to squeeze an omlette into a eggshell.  

> The following code represents my attempt to turn a set of sql
> statements into a stored procedure.

> The objective is to create a table with a column of ranked items based
> on how the sys_id is grouped. I like the solution that I was provided
> but am getting an unusual error (unusual for me!).

> In either case the errors that I need help resolving are indicated
> below.
> Server: Msg 207, Level 16, State 3, Line 3
> Invalid column name 'n'.
> Server: Msg 207, Level 16, State 1, Line 3
> Invalid column name 'n'.

> I can fix this one..
> Server: Msg 3701, Level 11, State 5, Procedure XPT_TBLTRANS, Line 13
> Cannot drop the table 'T1', because it does not exist in the system
> catalog.

> I'm executing this:

> 'matter__cost_'

> Here's the code:
> -- START OF CONVERSION TO PROCEDURE DEFINITION
> create procedure XPT_TBLTRANS


> AS
> BEGIN



> -- START PROCESS
> --drop table #T1


> drop table T1


> + char(10) + char(13)

> +  char(10) + char(13)

> FROM T1 AS T2 WHERE T2.sys_id = T1.sys_id AND T2.n <= T1.n) '  +
> char(10) + char(13)

> + 'ALTER TABLE T1 DROP COLUMN n ' +  + char(10) + char(13) + 'select

> from T1 '  + char(10) + char(13)

> char(10) + char(13)


> END
> -- END OF CONVERSION TO PROCEDURE DEFINITION

> NOTE: I'd tried to program this using the sp_executesql (as soon as I
> moved to sql 7) but it didn't seem to be the right tool because I
> don't have much internally changing parameter...(and it was/is a tough
> read on SBO)

>                   ******   PLEASE READ LINE BELOW  ******
> BTW - (this sql stuff isn't that easy -- can someone recommend the
> best book on intermediate & advanced sql programming concepts)

> I did get this going.. Here's the code; only problem is it took 1 min
> 21 secs to execute on about 30,000 rows (Don't know if that's really
> bad or not but I get a sense it is for an sp).

> Any thoughts on how to optimize the following? Please post it,

> & Thanks again,
> Tommie
> ------
> <CODE>
> create procedure XPT_TBLTRANS


> AS
> BEGIN

> drop table T1

> mkt_companyname.mclient'

> UPDATE T1
>   SET mv_sys_id = (SELECT COUNT(*)
>                  FROM T1 AS T2
>                  WHERE T2.sys_id = T1.sys_id
>                    AND T2.nbr <= T1.nbr)'

> END

> > Hi all,

> > Please excuse my trying to squeeze an omlette into a eggshell.  

> > The following code represents my attempt to turn a set of sql
> > statements into a stored procedure.

> > The objective is to create a table with a column of ranked items based
> > on how the sys_id is grouped. I like the solution that I was provided
> > but am getting an unusual error (unusual for me!).

> > In either case the errors that I need help resolving are indicated
> > below.
> > Server: Msg 207, Level 16, State 3, Line 3
> > Invalid column name 'n'.
> > Server: Msg 207, Level 16, State 1, Line 3
> > Invalid column name 'n'.

> > I can fix this one..
> > Server: Msg 3701, Level 11, State 5, Procedure XPT_TBLTRANS, Line 13
> > Cannot drop the table 'T1', because it does not exist in the system
> > catalog.

> > I'm executing this:

> > 'matter__cost_'

> > Here's the code:
> > -- START OF CONVERSION TO PROCEDURE DEFINITION
> > create procedure XPT_TBLTRANS


> > AS
> > BEGIN



> > -- START PROCESS
> > --drop table #T1


> > drop table T1


> > + char(10) + char(13)

> > +  char(10) + char(13)

> > FROM T1 AS T2 WHERE T2.sys_id = T1.sys_id AND T2.n <= T1.n) '  +
> > char(10) + char(13)

> > + 'ALTER TABLE T1 DROP COLUMN n ' +  + char(10) + char(13) + 'select

> > from T1 '  + char(10) + char(13)

> > char(10) + char(13)


> > END
> > -- END OF CONVERSION TO PROCEDURE DEFINITION

> > NOTE: I'd tried to program this using the sp_executesql (as soon as I
> > moved to sql 7) but it didn't seem to be the right tool because I
> > don't have much internally changing parameter...(and it was/is a tough
> > read on SBO)

> >                   ******   PLEASE READ LINE BELOW  ******
> > BTW - (this sql stuff isn't that easy -- can someone recommend the
> > best book on intermediate & advanced sql programming concepts)