Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jacob Sebastian » Passing a Table to A Stored Procedure
Add to briefcase
101 posts, Page 1 of 11
12345»»»

Passing a Table to A Stored Procedure Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jacob sebastian
Posted Tuesday, May 01, 2007 5:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2977.asp

.
Post #362487
VincentRainardi
Posted Tuesday, May 29, 2007 11:29 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188

Thank you for your article Jacob. It is very useful. I read your other articles and I am impressed by your ability to explain things concisely and clearly.

Kind regards,
Vincent
Post #369750
Deepan-358776
Posted Wednesday, May 30, 2007 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 06, 2008 10:55 AM
Points: 7, Visits: 14
Nice Article with clear explanation
Post #369756
jacob sebastian
Posted Wednesday, May 30, 2007 1:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521
Thank you vincent.

.
Post #369782
ALUN WILLIAMS
Posted Wednesday, May 30, 2007 2:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:04 AM
Points: 15, Visits: 137
Hi Jacob
Nice and well written. You can also pass the data via temporary tables i.e. create the temp table in the caller proc (or prior) and refer to it in the callee proc. I prefer your method, however.
Kind regards
Alun
Post #369796
Sergiy
Posted Wednesday, May 30, 2007 4:47 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:49 PM
Points: 4,542, Visits: 8,186
Bad effort because of lack of knowledge.

From BOL:
A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

So, create a view. It may be real view or a dummy one:

CREATE VIEW MyView
AS
SELECT
CAST(NULL as int) as Col1,
CAST(NULL AS nvarchar(50) as Col2,
etc.

Create your SP as a INSTEAD OF INSERT trigger on this view.

Perform insert into the view.

Here it is: table passed to stored procedure as system table "inserted".

Works quickly, effectively, in both versions of SQL Server.
Post #369826
TheSQLGuru
Posted Wednesday, May 30, 2007 5:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 3,575, Visits: 5,115
Has anyone tested to see if doing the XML thing is more or less costly than using a temporary table?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #369838
sheepoo
Posted Wednesday, May 30, 2007 6:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 7:32 AM
Points: 121, Visits: 32

Question for Sergiy,

What if my View is a compund View. Can we update a compund view?
Post #369864
matt stockham
Posted Wednesday, May 30, 2007 7:16 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, March 14, 2013 10:22 AM
Points: 750, Visits: 2,937

A view is an interesting solution that I haven't seen before.  I suspect it probably has as much (if not more) overhead than passing XML, plus an additional effort in creating the table / view / trigger (but obviously no XML code to write).  Does anyone have information on how the two approaches scale up?

 
Post #369874
Scott Gammans
Posted Wednesday, May 30, 2007 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 09, 2008 6:02 AM
Points: 16, Visits: 2
I don't think Jacob is the one suffering from a lack of knowledge, Sergiy. What happens when two different users simultaneously call the same stored procedure? Kaboom, that's what happens. And if you think that scenario is unlikely, you haven't worked on OLTP systems.

Scott Gammans
scottgammans.com
Post #369895
« Prev Topic | Next Topic »

Add to briefcase
101 posts, Page 1 of 11
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.