Dynamically create stored procedures in a database using stored procedure in second database

Question

Post reply

Dynamically create stored procedures in a database using stored procedure in second database

TonyThorne

Right there with Babe

Points: 749
More actions
June 30, 2010 at 5:10 am

#222774

I have recently created a code generator which needed to generate 4 Stored Procedures in the destination database.
After finding little success searching online, I ended up working it out myself and I thought I would share my results to save others in a similar situation.
My problem was that I needed to create four stored procedures on each table in the destination DB. These were add, delete, get individual record and get collection. I had created the SQL text dynamically and I had the database name which I then needed to pass into a new stored procedure which would then generate my new stored procedures.
This was my result. It may not be the best solution, but it was the first one which worked for me.
CREATE PROCEDURE [dbo].[proc_StoredProcedures_Build]
@SQLString nvarchar(MAX),
@Database nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @SQLBuildString nvarchar(MAX)
declare @ConCat nvarchar(150)
Set @SQLBuildString = N''+@SQLString;
Set @ConCat = @Database+'.dbo.sp_executesql'
execute @ConCat @SQLBuildString
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What exactly is your question? :unsure:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

TonyThorne Right there with Babe Points: 749 More actions · Answer 2

Two reasons for submitting:

1. This solution worked for me. Was this the best solution to the problem?

2. Looking online, I could not find a solution. The code and following discussion may help others in the community.

Regards,

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Tony,

It's a good solution because it gets you past the problem of not being able to use a db-qualified procedure name in the CREATE PROCEDURE Statement. I'm not sure why you do an execute at one level and EXEC sp_ExecuteSQL at another level.

You would do well to post this over in the scripts section.

Thanks for sharing.

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gmahon1234@yahoo.com Newbie Points: 3 More actions · Answer 4

Thank you, Tony

This is exactly the codes that I need but couldn't figure it out by myself. I did a workaround codes but your one worked amazingly well.

Kathy