how to create stored procedure in sql

Posted by Shanky11 under Sql Server on 8/25/2012 | Points: 10 | Views : 856 | Status : [Member] | Replies : 10
Create PROCEDURE Getstudentname(

@studentid INT --

)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END
--------IN Upper code code @student id is it manadatory to declare with @ symbol




Responses

Posted by: Pandians on: 8/25/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Kumuthag on: 8/25/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Vijay.K on: 9/6/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Maheshvishnu on: 9/6/2012 [Member] Starter | Points: 25

Up
0
Down
Yes it is mandatory

mahesh

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

Posted by: Muhsinathk on: 9/17/2012 [Member] Bronze | Points: 25

Up
0
Down
@ 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

Posted by: Muhsinathk on: 9/17/2012 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Hariinakoti on: 9/17/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Hariinakoti on: 9/17/2012 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Sriramnandha on: 5/11/2013 [Member] Starter | Points: 25

Up
0
Down


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

Posted by: Raj.Trivedi on: 5/12/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
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

Login to post response