running sproc inside stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

running sproc inside stored procedure?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-21 : 08:01:50

Hi,

I have a few stored procedures that have conditional statements inside them, and if 1 is met I do an insert into another table.

Everything is working fine, I'm just wondering if it would be better practice to encapsulate the logic of the insert into a SPROC ?

Im trying to figure out if theres a standard best practice for these types of situations.

Thanks!
Mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-21 : 10:25:43

well i guess you could use temp tables...
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 10:30:28

or even OPENROWSET.
SELECT * FROm OPENROWSET(....,EXEC YourSP param1val,param2val,...)

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-21 : 11:09:27

It's for an INSERT statement tho ?

It's not really that big of a deal, I'm pretty happy with the way its working, was just wondering if anyone had a way that was clearly better.

Thanks!
mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-21 : 11:13:20

well i see no problem with encapsulating insert statements inside the sproc. it makes it one place where you have to change stuff instead of N places.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-21 : 11:16:27

quote:
Originally posted by spirit1

well i see no problem with encapsulating insert statements inside the sproc. it makes it one place where you have to change stuff instead of N places.

Hi Spirit1,

Whats the syntax? How exactly would I go about encapsulating a simple INSERT statement?

Thanks again,
Mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-21 : 11:20:30

something like this:


IF OBJECT_ID('[Person].[usp_AddressInsert]') IS NOT NULL
BEGIN 
    DROP PROC [Person].[usp_AddressInsert] 
END 
GO
CREATE PROC [Person].[usp_AddressInsert] 
    @AddressLine1 nvarchar(60),
    @AddressLine2 nvarchar(60),
    @City nvarchar(30),
    @ModifiedDate datetime,
    @PostalCode nvarchar(15),
    @rowguid uniqueidentifier,
    @StateProvinceID int
AS 
	SET NOCOUNT ON 
	SET XACT_ABORT ON  
	
	BEGIN TRAN
	
	INSERT INTO [Person].[Address] ([AddressLine1], [AddressLine2], [City], [ModifiedDate], [PostalCode], [rowguid], [StateProvinceID])
	SELECT @AddressLine1, @AddressLine2, @City, @ModifiedDate, @PostalCode, @rowguid, @StateProvinceID
		
	SELECT [AddressID], [AddressLine1], [AddressLine2], [City], [ModifiedDate], [PostalCode], [rowguid], [StateProvinceID]
	FROM   [Person].[Address]
	WHERE  [AddressID] = SCOPE_IDENTITY()
               
	COMMIT
GO

I've created this with and add in i've created for SSMS called SsmsToolsPack. you can get it on the page in my sig below.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources