Hi
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
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 Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)
> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
