I create StringBuilder type for
concating string to create a lot of stored procedure at once
However When I use this command
BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////
------- This any command
--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY
BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;
on any command
If I use
Create a lot of Tables
such as
BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////
CREATE TABLE [dbo].[Table1](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]
--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY
BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;
It correctly works.
But if I need create a lot of Stored procedure
as the following code :
BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////
CREATE PROCEDURE [dbo].[DeleteItem1]
@.ProcId Int,
@.RowVersion Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId AND
[RowVersion] = @.RowVersion
END
CREATE PROCEDURE [dbo].[DeleteItem2]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END
CREATE PROCEDURE [dbo].[DeleteItem3]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END
--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY
BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;
It occurs Error ???
Please help me
How should I solve them ?
the stored procedure create
CREATE PROCEDURE ..
have to be first in T-SQL command batch. You can do what you need by inserting each single procedure code into varchar(max) variable and run this code using EXEC command like
DECLARE @.lcCommand as varchar(max)
SET @.lcCommand ='CREATE PROCEDURE PROC1 .....'
EXEC (@.lcCommand)
SET @.lcCommand ='CREATE PROCEDURE PROC2 .....'
EXEC (@.lcCommand)
remember if you procedure definition is longer than 8000 chars split it into chunks not longer than 8000 chars to prevent errors( for some reason string passed to varchar(max) in single assign is cut at 8000 position if longer than 8000 chars)
y