SQL Server Stored Procedures – Fundamentals
With that last keystroke, you have created your first set of variables. To finish “usp_adduser”, we will have to figure out what we want the stored procedure to do, then add the appropriate code after the “AS” statement. This stored procedure will add a new record to the USERLIST table, so we should use an INSERT statement. The SQL should be:
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)
The INSERT clause is pretty standard. The VALUES clause is a bit more complex. If you have worked with databases, you are probably accustomed to seeing something like this:
VALUES (‘dnelson’, ‘dean2003′, ‘Dean’, ‘Nelson’, ’200 Berkeley Street’, ”, ‘Boston’, ‘MA’, ’02116′, ‘dnelson@test.com’)
Since we are passing values from variables, it will look a bit different. Instead of putting the actual values in the VALUES clause, we’ll just put the variables. You won’t need to use quotes.
VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)
What does the entire stored procedure look like? Let’s pull it all together.
/*
Name: usp_adduser
Description: Add new logins.
Author: Tom O’Neill
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
*/
CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)
AS
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)
VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)
It looks pretty long and complex, though we know from the process above that the stored procedure is not necessarily complex; it just contains a lot of data. If you have been working in a separate text editor, copy your stored procedure into the New Stored Procedure window in SQL Server, and check the syntax. The result should be a successful syntax check.
Now, we have a stored procedure that can accept external data. What do we do with it? How do we get the data? It’s not that hard; I promise. We’ll start with the “exec” statement we used when we wrote our first stored procedure. Remember?
exec usp_displayallusers
We have a new stored procedure to execute, so this time, the command will be:
exec usp_adduser
There is still the issue of how to get our data into the stored procedure. Otherwise, all those variables will be useless. To get data into our stored procedure, simply add the information (in single quotes ‘ ‘) after the execute statement.
exec usp_adduser ‘ ‘
Remember to pass as many parameters as you have variables, otherwise SQL Server will throw an error. Since we have ten variables, your execute statement should look like this:
exec usp_adduser ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘, ‘ ‘
Next, let’s include the data that we will want to pass to usp_adduser. Your execute statement will look like:
exec usp_adduser ‘dnelson’, ‘dean2003′, ‘Dean’, ‘Nelson’, ’200 Berkeley Street’, ‘ ‘, ‘Boston’, ‘MA’, ’02116′, ‘dnelson@test.com’
Running the query should be successful, and SQL Server will tell you that one row has been affected. Now, let’s try using input variables with some other query types.
creating and calling stored procedure in .net:
create procedure insert1(@userid int,@username varchar(50))
as
begin
insert into emp(id,name)values(@userid,@username)
end
NOTE:(id,name) are the columns of table in which you want to insert data.
CALLING STORED PROCEDURE :
initialize this globally:
public partial class1:class
{
sqlconnection con;
sqlcommand cmd;
}
on page_load(): // write on page load()
{
con=new sqlconnection(“Connection string”);
cmd=new sqlcommand();
cmd.connection=con;
cmd.commandtype=commandtype.stored procedure;
}
on button_click() // write on insert button_clik event
{
cmd.parameters.clear();
cmd.commandtext=”insert1″;
// stored procedure name just created.
cmd.parameters.addwithvalue(“@userid”,textbox1.text);
cmd.parameters.addwithvalue(“@username”,textbox2.text);
con.open();
cmd.executenonquery();
messagebox.show(“stored procedure inserted…”);
con.close();
}
Hi Tom,
I was reading your article and I would like to appreciate you for making it very simple and understandable. This article gives me a basic idea of stored procedure and it will help me a lot.
Thank you very much!
Bhuvan
Hi Tom
Your article are really awesome.actually i was in search for some good articles on stored procedures and finally i got one.
The most important is the simplicity which will be very helpful for the beginners.
Thanks
Avinash
Hi Tom,
This was a really informative article for us beginner SQL developers. The procedure was not very complex and easy to understand. I have been studying from a high level SQL developer in order to understand SQL better and he very much confused me with the very advanced code that he was using. Example of more complex code
/*
declare @today datetime
SET @Today = getdate()
EXECUTE sproc_insertvendor @name = ‘something Else’, @countofpayments = 3, @lastpaydate = @Today, @nextpaydate = @Today, @Comments = null
*/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = ‘sproc_insertvendor’)
DROP PROCEDURE sproc_insertvendor
GO
CREATE PROCEDURE sproc_insertvendor
@name varchar(200),
@countOfPayments int,
@LastPayDate datetime,
@nextpaydate datetime,
@comments ntext
AS
if exists (
select *
from vendor v
where v.name = @name
)
BEGIN
Raiserror(‘%s already exists!’,16,2,@name)
END
if @@error=0
BEGIN
INSERT INTO vendor (Name,CountOfPayments,LastPayDate,NextPayDate,Comments)
VALUES (@Name,@CountOfPayments,@LastPayDate,@NextPayDate,@Comments)
SELECT @@Identity
END
RETURN
GO
I forgot to thank “Om Mohokar” for his post, cheers mate.
Hi Tom,
This is the best article I have ever found online. Your instruction is extremely easy and helpfulfor me to understand and use for my work.
Truly appreciate you!
Best regards,
Patrick
Unfortunately, far more complex stored procedures have been written, Bryan, but I will walk through it quickly.
The first two lines are an example of how to declare variables in your stored procedure. DECLARE…uh, declares the variable. SET assigns the variable with a value. You can also do something like SELECT 1 INTO FROM .
The block that the first lines belong too just serve as an example. Other than the variables it is much like this article explained. Obviously, in the third line, the parameter declaration has values assigned to them.
The second block is searching for an existing version of a stored procedure with the name ‘sproc_insertvendor’. If it exists, then drop it so that you can create your new one or your new version if you are running this more than once.
Nothing really new in the third block although I would like to add a side note. To make a parameter optional, you can add a default value (e.g. @countOfPayments INT = 0) which is extremely valuable information to me at least.
The fourth block introduces you to Raiserror. I don’t personally know if this is built into SQL Server or not, but it is setting the @@error variable. @@ delineates system or global variables the best I can tell.
Finally, the fifth block is inserting a row as long as the person doesn’t already exist.
One last side note: try not to write if exists(select *… If you just wanna know if it exists, if exists(select 1… is more performant and gives you the same information you wanted to know.
Good article, author. I need to find one for advanced beginners now.
Thanks but one important part not mentioned here is “how to modify the stored procedure and save it as the same stored procedure?”
Right click on the edited query editor page and execute it to save the changes to the stored procedure.(when you change and try to close it prompts you to change as a query, but not as the stored procedure.