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]
