Table variable as Output Parameter

Question

Table variable as Output Parameter

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

ichiyo85 (1/31/2011)
Hi, on a similar note, I want to make a stored procedure where I can delete customers from a December customer table, but only those who will show up again in January. Keep those who are not returning, and insert the existing and new customers from January. In other word, I am trying to update a customer table every month by deleting the old and replace/insert the new.
Something like:
delete from AccountMaster
where AccountMaster.AccountID in (select AccountID from RevDec2010)
insert into AccountMaster (AccountID,Period)
select distinct AccountID, Period
from RevJan2011
How can I declare the table object "RevDec2010" and so forth in the stored procedure?
Thank you very very much in advance!!

Hi,

When ever you are posting staert in new post

If you are using SQL 2008 there is concept called MERGE is there, where u will be able to INSERT,UPDATE,DELETE on conditions.(If you are using 2005 ignore this just want to say there is a opition is there in 2008)

http://msdn.microsoft.com/en-us/library/bb510625.aspx

For upcoming data you need to check whether it EXISTS or not in the table if EXISTS you need to UPDATE the record or DELETE and INSERT the new record.Hints

you can use

1)EXISTS

2)NOT EXISTS

3)@Flag

4)MERGE--2008

etc...

There are so many options are there i have given only 1 or 2 there are lot of options are there try with your own and come back with your code if you are not able to get the solution

Thanks

Parthi

Thanks
Parthi

ichiyo85 SSC Veteran Points: 286 More actions · Answer 2

Thank you very much for your suggestion. I was able to use Merge to get the result that I wanted, but I am having issues making my code into a stored procedure. Below is my code:

MERGE AccountMaster2 AS TARGET

USING RevJuly2010$ AS SOURCE

ON (TARGET.AccountID = SOURCE.AccountID)

WHEN MATCHED THEN

UPDATE SET

target.AccountNumber = Source.AccountNumber,

WHEN NOT MATCHED BY TARGET THEN

INSERT (AccountID,AccountNumber)

VALUES (SOURCE.AccountID, Source.AccountNumber)

OUTPUT $action,

DELETED.AccountID AS TargetAccountID,

INSERTED.AccountID AS SourceAccountID,

deleted.AccountNumber as TargetAccountNumber,

inserted.AccountNumber as SourceAccountNumber,

;

Would I be able to make a stored procedure so that I only need to input the only TABLE NAME such as "RevJuly2010$" every month (I don't need to change the columns that I need to merge)?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Oddly enough, I haven't used MERGE in 2k8, yet. But, I believe you'll still need to make that query dynamic SQL using a variable for the USING table name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

marcjell Old Hand Points: 349 More actions · Answer 4

Instead of doing a self-referencing linked server, you can directly call sp_ExecuteSQL within the context of any database using three-part naming:

[mydatabase].sys.sp_ExecuteSQL (N'...')