Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Table variable as Output Parameter Expand / Collapse
Author
Message
Posted Friday, January 2, 2009 4:31 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #629162
Posted Friday, January 2, 2009 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 35,263, Visits: 31,751
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. :P


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #629171
Posted Friday, January 2, 2009 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 35,263, Visits: 31,751
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. ;)


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #629172
Posted Thursday, December 24, 2009 4:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 35,263, Visits: 31,751
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. ;)


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #839097
Posted Monday, January 31, 2011 7:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 15, 2011 7:35 AM
Points: 14, 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!!


Post #1056188
Posted Monday, January 31, 2011 8:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
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
Post #1056215
Posted Thursday, February 3, 2011 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 15, 2011 7:35 AM
Points: 14, 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)?
Post #1058127
Posted Friday, February 4, 2011 7:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 35,263, Visits: 31,751
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1058713
Posted Sunday, August 28, 2011 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:25 AM
Points: 2, Visits: 69
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'...')



Post #1166704
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse