----- Original Message -----
From: Mike Townend
To: SQL
Sent: Friday, December 19, 2003 10:17 AM
Subject: RE: multi-step SP
sp_ is used by global stored procedures and so SQLServer checks the master
db for the procedure before it checks the current database the call is being
made from. I tend to call my procs with a spr_ prefix
HTH
-----Original Message-----
From: Antony Sideropoulos [mailto:[EMAIL PROTECTED]
Sent: Friday, December 19, 2003 01:49
To: SQL
Subject: Re: multi-step SP
What's wrong with sp_ ?? I thought I was quite clever using that as my
prefix?
:-(
>From: "Neil Robertson-Ravo - Team Macromedia"
><[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: SQL <[EMAIL PROTECTED]>
>Subject: Re: multi-step SP
>Date: Wed, 17 Dec 2003 21:11:46 -0000
>
>Hey Michael,
>
>Do you want this variable to be passed around from within an sproc itself
>or out from an sproc into another SP? It sounds and looks as though you
>can do the whole shebang in one SQL query/sproc.
>
>You can also set a variable in the SP can call it via:
>
>usp_yourproc @input , @result output
>
>where @result is defined and set inside the sproc.
>
>Are you planning to use cfstoredprocedure or EXEC within a cfquery block?
>
>Also, remember and NOT to name your sproc "sp_" as that will degrade SQL
>Server performance on your db operations.
>
>
>
>
>
>----- Original Message -----
> From: Michael Dinowitz
> To: SQL
> Sent: Wednesday, December 17, 2003 7:33 PM
> Subject: multi-step SP
>
>
> I've got a massive uploader where the data (after mapping) is put into a
>temp
> table and then has to be transformed some and put into 2 live tables. I
>need to
> do the following queries:
> 1. get lastid+1 from items table
> 2. generate parent/child relationship for item
> 3. put tempitem into items table
> 4. put tempitem into lotmatrix table
> 5. delete temp item
> These have to be done in a one at a time fashion and is a lot of
>queries. CFMX
> doesn't like 1500+ queries inside a CFTRANSACTION block (I'm assuming
>that's the
> source of the error as it works) so I have to do the whole operation one
>at a
> time.
> Does anyone have the syntax offhand for assigning variables from a query
>in an
> SP to another query in an SP? I want query 1 in the SP to get the lastid
>and
> store it as a var. Query2 in the SP will then use that var with others
>to
> insert.
> Thanks
> --
> Michael Dinowitz
> Finding technical solutions to the problems you didn't know you had yet
>
>
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
