GSquared (12/17/2009)

---

I agree, but I add an additional check.

if object_id(N'[ProcName]','P') is null and object_id(N'[ProcName]') is not null

raiserror('Object name in use', 21, 1) with log;

That aborts the...

Additionally it would probably be a good idea to do a check before trying the rollback. It is a good practice to ensure the transaction can be rolled back.

Possible...

Using a DROP/CREATE with an IF EXISTS clause instead of an ALTER statement for deploying SQL Server objects like stored procedures is one example of a standard that can be...

Your declared sizes are possibly going to cause truncation:

@ProcedureName VARCHAR(50) = NULL,

@SourceServer VARCHAR(50) = NULL,

@SourceDatabase VARCHAR(50) = NULL,

@TargetServer VARCHAR(50) = NULL,

@TargetDatabase VARCHAR(50) = NULL

maybe declare them as this:

@ProcedureName sysname =...

strategery

(pronounced struh-TEE-jer-ee)

If you want to script out the stored procedures and other objects to 1 file or multiple files you can do so using the "Generate Scripts" menu command in SSMS...

I am not saying whether you should or shouldn't use dynamic SQL but since you are trying to learn it, here is a way you might write something to play...

Also splitting it up across multiple stored procedures will be more conducive to concurrent development within a team of developers.

Steve Jones - Editor (11/13/2009)

---

Jeff Moden (11/12/2009)

---

Heh... I have an odd personal preference... I don't like languages that require semi-colons or line continuation characters. It seems that they should...

Paul White (11/7/2009)

---

Hey Adam,

Not trying to be 'smart' here...but ROLLBACK always rolls back completely, through any number of 'nested' transactions.

You...

Paul White (11/6/2009)

---

You might like to change the ROLLBACK TRANSACTION to:

IF XACT_STATE() != 0...

Paul White (11/6/2009)

---

Adam Gojdas (11/5/2009)

---

Please be aware the ERROR_MESSAGE() function returns a nvarchar(4000). So you will run the risk of truncating some of the messages using the variable declared...

Please be aware the ERROR_MESSAGE() function returns a nvarchar(4000). So you will run the risk of truncating some of the messages using the variable declared as varchar(100).

venu_ksheerasagaram (11/1/2009)

---

Hi All,

Here i am having a doubt regarding the Dynamic Query. My requirement is How to store the Dynamic Query resultset into a TempTable? Could it possibel?

Because...

Here this may help a little:

DECLARE @word sysname

EXEC sp_executesql

N'SELECT TOP 1 @DynamicWord = [name] FROM sysobjects' --dynamic sql...