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 12345»»»

Passing a Table to A Stored Procedure Expand / Collapse
Author
Message
Posted Tuesday, May 1, 2007 5:18 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2977.asp

.
Post #362487
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: Friday, September 26, 2014 12:56 AM
Points: 81, Visits: 190

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
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 6, 2008 10:55 AM
Points: 7, Visits: 14
Nice Article with clear explanation
Post #369756
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: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Thank you vincent.

.
Post #369782
Posted Wednesday, May 30, 2007 2:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 6:31 AM
Points: 44, Visits: 175
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
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: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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
Posted Wednesday, May 30, 2007 5:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 4,439, Visits: 6,346
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
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: Saturday, August 31, 2013 11:41 PM
Points: 121, Visits: 34

Question for Sergiy,

What if my View is a compund View. Can we update a compund view?

Post #369864
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: Saturday, October 18, 2014 9:34 AM
Points: 754, Visits: 3,164

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
Posted Wednesday, May 30, 2007 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 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 12345»»»

Permissions Expand / Collapse