MSSQL - How to drop and create a stored procedure with parameters

on MSSQL

This post describes how to create and drop a stored procedure with a parameter in MSSQL. If we have the following MyEntities table with an Id as an int and Text as NVARCHAR(MAX):

CREATE TABLE [dbo].[MyEntities](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](max) NOT NULL,
   CONSTRAINT [PK_MyEntities] PRIMARY KEY CLUSTERED 
   (
	  [Id] ASC
   )
)

We can create a simple stored procedure to fetch a row from this table:

IF OBJECT_ID('GetEntity', 'P') IS NOT NULL
DROP PROC GetEntity
GO

CREATE PROC GetEntity @Id int
AS
SELECT * FROM MyEntities e
WHERE [id] = @Id

GO

In the above we drop the procedure GetEntity if it does not exist, we then (re)create it as a simple SELECT * FROM .. with an integer Id as a parameter. If we insert a row into this table:

INSERT INTO [dbo].[MyEntities]
           ([Text])VALUES ('This is a text!')

We can select the row using our stored procedure using the EXEC command:

EXEC GetEntity @Id = 0

This returns the row we have just created. You can add additional parameters to the stored procedure by placing a comma in between them:

CREATE PROC GetEntity @Id int, @Text nvarchar(MAX)

In the above we also use the @Text as a parameter.

That is all

I hope you enjoyed this short post on how to (drop and) create a stored procedure with a parameter in MSSQL. Let me know what you think in the comments down below!