SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table variable as Output Parameter


Table variable as Output Parameter

Author
Message
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33730 Visits: 9518
You could just return your table data from your proc as SELECT output and then the caller could catch it by using the INSERT..EXEC.. statement.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207314 Visits: 41961
Grant Fritchey (12/31/2008)
Thanks for the correction. I wasn't sure and I haven't really looked at the table valued parameters yet. I should have kept my mouth shut.


Heh... nope... then I wouldn't have learned what I just learned. Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207314 Visits: 41961
aziz.kapadia (12/31/2008)
Can I declare table variable as output parameter in stored procedure?


Probably of more importance, what is it that you're trying to do? You'll be amazed at the possible solutions you'll get if you describe the original problem rather than a possible solution. Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207314 Visits: 41961
Jeff Moden (1/2/2009)
aziz.kapadia (12/31/2008)
Can I declare table variable as output parameter in stored procedure?


Probably of more importance, what is it that you're trying to do? You'll be amazed at the possible solutions you'll get if you describe the original problem rather than a possible solution. Wink


Heh... guess ya must of solved your problem, huh? ;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ichiyo85
ichiyo85
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 39
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!!
parthi-1705
parthi-1705
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1902 Visits: 2196
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
ichiyo85
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 39
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
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207314 Visits: 41961
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
marcjell
marcjell
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 92
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'...')



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search