Sending a Dataset to stored proceedure.

[randy4126]

Hi, I am new to the stored proceedures and what I would like to do is send a data set to a stored proceedure. I don't know if this is possible. I though maybe you could do this if the stored proceedures had arrays. But I have looked in the books and found nothing on arrays. Below is an example of what I am doing with a single insert but my goal is to pass the proceedure the full data set without calling this multiple times like over 100,000 times.


CREATE PROCEDURE insertORupdate
@firstname char(10),
@lastname char(10),
@age char(10)
AS
Declare @lastnameresult char(10)
SELECT @lastnameresult=LastName from person where LastName = @lastname


if @lastnameresult = @lastname
BEGIN
print "Update Query"
UPDATE PERSON SET firstName = @firstname, age=@age where LastName = @lastname
END
ELSE
BEGIN
print "Insert Query"
INSERT INTO person values (@firstname, @lastname, @age)
END


Thanks for any help you can give me.


Randy

 

[hneal98]

What is your ultimate goal with this stored procedure?

 

[nigelrivett]

You could crate a temp table populate it then call the SP to insert from there.
Maybe faster is to put all the names into a disk file, use bcp in the SP to import it into a global temp table then insert from there to the table.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[randy4126]

Let me give some back ground on what I am doing. The processes I am running are on a linux machine. From there I am calling with an insert query to add the records in the table. I need to update if the key exists if the key doesn't exist do an insert query. the process will repeat every 5 minutes and insert a over a 100,000 records. what I have show for my code is an example where I am testing to see if it is possible before I modify everything to work that way. So creating a file or using BCP is now going to work. The collector process have to run on a linux machine. So any ideals on how to keep the inserts as efficient as possible. Thanks.

Randy