how to create stored procedure in sql

YES.

All kind of Parameters in Procedures(IN/OUT) and Functions has to be Prefixed with '@' symbol. YES Its mandatory!

In your code, @StudentID is an IN Parameter. So, It has has been prefixed with '@'

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

SYN TAX

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here like
<@Param1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>

AS
BEGIN
-- Your Normal SQL Query goes here
SELECT <@Param1, sysname, @p1> FROM Table_Name
END

Thanks & Regards,
Kumutha G

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

go
create proc proce_name(@eid int,@ename varchar(20)) as
begin
insert into Your_table_name values(@eid,@ename)
end
go

vijay.k

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Yes it is mandatory

mahesh

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

@ symbol is manadatory with every input and output parameter

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE Getstudentname
-- Add the parameters for the stored procedure here
@studentid INT
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid

-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- there was an error
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
GO

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Hi Shanky11,
Please mark as answer if it helpful to you..That helps others who search the same...

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Eg: Creating for Employee Records
Create Procedure Procedure name
(@action char(1),@Empid varchar(10),@Empname varchar(25),@Age int,@Sal int,@Address varchar(50)
as
begin
if(@action='i') //i=insert
insert into tblemp (@Empid,@Empname,@Age,@Sal,@Address) values(Empid,Empname,Age,Sal,Address)
end
begin
if(@action='u')//update
update tblemp @Emp=Emp and @Age=Age and @Sal=sal where @Empid=Empid
end
begin
if(@action='s')//select
Select * from tblemp
end

Thanks & Regards
Hari

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Hi Shanky11,
Please mark as answer if it helpful to you..That helps others who search the same...

Thanks & Regards
Hari

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

CREATE PROCEDURE SP_EMPLOYEE
@PRODID INT ,
@PRONAME VARHCAR(90),
@PROADDRESS VARCHAR(90),
BEGIN TRY
INSERT INTO PROGRAM (PRO_ID,PRONAME,PROADDRESS)VALUES(@PROID,@PRONAME,@PROADDRESS)
END TRY

BEGIN CATCH
PRINT @@ERROR
PRINT 'APPROPRIATE ERROR'
END CATCH
END

HOPE THIS WILL HELP......................

sriram

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

hello

CREATE Proc [dbo].[GetStatuswiseCompanies]
(
@Status tinyint
)
as
begin
select * from CompanyMaster where Status = @Status
end

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator