I have a stored procedure where there are no header comments. I want to add them, but whenever I try, it is not included.
In SQL Server Management Studio I :
1.Right-click my stored procedure and click modify
USE [ABigDB]
GO
/****** Object: StoredProcedure [dbo].[spDoWork] Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDoWork]
@Id uniqueidentifier,
@Session nvarchar(50),
@XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END
2.I paste comments above the stored procedure and run the script :
-- Stored Procedure
-- Author: Dave
-- Create date: 21/08/2015
-- Description: Does Stuff
-- Change history
-- 07/08/2015 - Overlord - Done stuff
-- 06/08/2015 - Kerrigan - Done more stuff
USE [ABigDB]
GO
/****** Object: StoredProcedure [dbo].[spDoWork] Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDoWork]
@Id uniqueidentifier,
@Session nvarchar(50),
@XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END
3.When I modify the same stored procedure it appears as :
USE [ABigDB]
GO
/****** Object: StoredProcedure [dbo].[spDoWork] Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDoWork]
@Id uniqueidentifier,
@Session nvarchar(50),
@XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END
So how do I get the comments to appear there?
To create line comments you just use two dashes "--" in front of the code you want to comment. You can comment out one or multiple lines with this technique. In this example the entire line is commented out.
Use SQL Server Management StudioExpand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to modify, and then select Modify. Modify the text of the stored procedure. To test the syntax, on the Query menu, select Parse.
The UPDATE statement is used to edit and update the values of an existing record. The DELETE statement is used to delete records from a database table. The following SQL stored procedure is used insert, update, delete, and select rows from a table, depending on the statement type parameter.
The command to modify an existing stored procedure is ALTER PROCEDURE or ALTER PROC.
I solved it by doing the following:
USE [ABigDB]
GO
/****** Object: StoredProcedure [dbo].[spDoWork] Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Stored Procedure
-- Author: Dave
-- Create date: 21/08/2015
-- Description: Does Stuff
-- Change history
-- 07/08/2015 - Overlord - Done stuff
-- 06/08/2015 - Kerrigan - Done more stuff
ALTER PROCEDURE [dbo].[spDoWork]
@Id uniqueidentifier,
@Session nvarchar(50),
@XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With