SQL Server is one of the best products to come out of Microsoft, but not every professional knows how to use it effectively. For example, some might find it difficult to create a stored procedure but worry not if you fall in that bracket because you’ve come to the right place. Now, learning how to create a SQL Stored Procedures via SQL Server is something all professionals in this field should know how to accomplish.
How to create a stored procedure in SQL Server
Creating SQL Stored Procedures via SQL Server is easier than you might have originally thought, so let us explain what to do. Simply follow the steps here:
- Click on New Query
- Type a CREATE PROCEDURE statement
- Manually write a CREATE PROCEDURE Statement
- Call the Stored Procedure
1] Click on New Query
The first thing you must do in this situation is to click on the New Query button. This is easily done, so let us explain how.
- OK, so begin by opening the SQL Server tool.
- From there, choose the database where you want to create a stored procedure.
- Once it is up and running, you should click on the New Query button as soon as possible.
- You can find this button right on the toolbar.
2] Type a CREATE PROCEDURE statement
Moving on, you must now type a create procedure statement from the text area provided.
Here is an example of what a create procedure statement looks like:
CREATE PROCEDURE LatestTasks @Count int AS SET ROWCOUNT @Count SELECT TaskName AS LatestTasks, DateCreated FROM Tasks ORDER BY DateCreated DESC
We must note that the above script is designed to create a stored procedure that is called TastestTasks, and it accepts a parameter called Count.
3] Manually write a CREATE PROCEDURE Statement
If you’re not sure how to create a procedure statement by your own design, then let us explain to aid in you becoming a master.
You begin the script with CREATE PROCEDURE (must always be in caps).
From there, hit the space button and type the procedure_name.
Follow it up with another space, then AS.
So, if done correctly, the basic script should look like the following:
CREATE PROCEDURE GetCustomer AS
Next, you must add the SQL code specifically for a stored procedure, and it must look similar to the first example above.
You see, if the stored procedure is to receive parameters, add the @ symbol along with the data type of Integer. This will prefix the parameter name, so when all is said and done, the script should look like the following:
CREATE PROCEDURE GetCustomer @CustomerId int AS
4] Call the Stored Procedure
To call, or execute the stored procedure, you must use either EXEC or the EXECUTE command. Worry not because both do the same thing.
If you follow closely, then the finished product should look like what is visible below:
EXEC GetCustomer @CustomerId = 7
OR
EXECUTE GetCustomer @CustomerId = 7
So, what is the number 7 all about? Well, the passed parameter executed a CustomerId that contains 7 as a value. That means if the number is changed then SQL will process a different customer.
READ: How to Download and Install MySQL on Windows
Is SQL Server free?
There is a free version of the SQL Server, and it is called SQL Server 2022 Express. It is specifically ideal for the development and production of desktop, web, and small server applications.
Is SQL Server difficult to learn?
Overall, we have to say that SQL is an easy language to learn. Even more so if you have prior programming experience because it opens the door for learning the language in mere weeks rather than months.
