How to Create Stored Procedure ??

Bryian have answered the question

i would like to add

you need an Framwork of SP , Table, veiw etc.....
The framwork will have all the wistle and bells things like error handling, comments,headers, naming convensions, etc...
a FW will make coding more accurate and consistence
FW for SP must be rerunable
and lots more

This is a sample that i have i have removed lots of fetures like error handling but you are geeting a big shell to start with

/*
-- ============================================================================
Authour: Nik - Shahriar Nikkhah Date: 2018-04-21
Name :
Input :
Output :
How to use/call :
SQL Server Version : Tested on SQL 2016
Description:
For the next section please add : Your name , Date , what was modified or added
Last Modification :
============================================================================
This script is intended only as a supplement to demos and lectures
given by "Nik - Shahriar Nikkhah"
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============================================================================
-- ============================================================================
*/
USE [YourDBName]
GO
;
-- ****************************************************************************
-- Cleanup
-- ****************************************************************************
IF OBJECT_ID('[dbo].[uspYourSPName]') IS NOT NULL AND 2 = 2 -- ( 2 = 2 Is a safty net for design time)
DROP PROCEDURE [dbo].[uspYourSPName]
GO
;
-- ****************************************************************************
-- Some Settings
-- ****************************************************************************
SET ANSI_NULLS ON
GO
;
SET QUOTED_IDENTIFIER ON
GO
;
-- ****************************************************************************
-- Create Object (Table/View/SP/Fn/...)
-- ****************************************************************************
CREATE PROCEDURE [dbo].[uspYourSPName]
(
@Para1 nVarChar(100) --- Needs comments
, @Para2 Int --- Needs comments
, @Para3 Int = 0 OUTPUT --- Needs comments
)
AS
BEGIN
SET NOCOUNT ON
;
-- ****************************************************************************
-- Declare main variables
-- ****************************************************************************
DECLARE @sql nVarChar(2000) = ''
-- ****************************************************************************
-- Add comment
-- ****************************************************************************
SET @sql = '---- Add comment
----IF @Para3 = 0
SELECT Top 1 @Para3 =
.
.
.
'
--- dont forget error handling
END
GO
;
-- ****************************************************************************
-- Test
-- ****************************************************************************
/*
USE [YourDBName]
GO
;
DECLARE @Para2 Int = 22
DECLARE @Para3 Int = 0
EXEC [dbo].[uspYourSPName] 'AnyValue' ,@Para2 , @Para3 OUTPUT
SELECT @Para3 AS '@Para3' , @Para2 AS '@Para2'
*/

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

souvik sardar8y ago
Accepted answer
For insert:::
CREATE PROC SP_insert()
@id int,@name varchar(50) ,@contentBody varchar(50),@image varchar(50)
as
begin
inert into tbl_aboutus values(@id,@name,@contentBody,@image);
END;
For Update::::
CREATE PROC SP_update()
@P_id int,@name varchar(50) -- params that need update with primary key
as
begin
update tbl_aboutus set name =@ name where id=@P_id;
END;
Fo Delete:::
CREATE PROC SP_delete()
@P_id int -- primary key
as
begin
delete from tbl_aboutus where id=@P_id;
END;
+3
Shahriar Nikkhah8y ago
I am sure that stored procedure naming conventions has a standard, for example you should not have your stored procedure name starting with sp_ it should be usp_

FYI
http://www.sqlnuggets.com/blog/do-not-prefix-stored-procedures-with-sp_/

Good luck
+2
Shahriar Nikkhah8y ago
Hi
Bryian have answered the question
i would like to add
you need an Framwork of SP , Table, veiw etc.....
The framwork will have all the wistle and bells things like error handling, comments,headers, naming convensions, etc...
a FW will make coding more accurate and consistence
FW for SP must be rerunable
and lots more
This is a sample that i have i have removed lots of fetures like error handling but you are geeting a big shell to start with
/*
-- ============================================================================
Authour: Nik - Shahriar Nikkhah     Date: 2018-04-21
Name :
Input :

Output :
How to use/call :

SQL Server Version : Tested on SQL 2016

Description:

For the next section please add : Your name , Date , what was modified or added
Last Modification :
============================================================================
  This script is intended only as a supplement to demos and lectures
  given by "Nik - Shahriar Nikkhah"

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================
-- ============================================================================
*/
USE [YourDBName]
GO
;


-- ****************************************************************************
-- Cleanup
-- ****************************************************************************
IF OBJECT_ID('[dbo].[uspYourSPName]') IS NOT NULL AND 2 = 2 -- ( 2 = 2 Is a safty net for design time)
    DROP PROCEDURE [dbo].[uspYourSPName]
GO
;

-- ****************************************************************************
-- Some Settings
-- ****************************************************************************
SET ANSI_NULLS ON
GO
;
SET QUOTED_IDENTIFIER ON
GO
;

-- ****************************************************************************
-- Create Object (Table/View/SP/Fn/...)
-- ****************************************************************************
CREATE PROCEDURE [dbo].[uspYourSPName]
(
        @Para1      nVarChar(100)       --- Needs comments
    ,   @Para2      Int                 --- Needs comments
    ,   @Para3      Int =   0   OUTPUT  --- Needs comments
)
AS
BEGIN

    SET NOCOUNT ON
    ;
    -- ****************************************************************************
    -- Declare main variables
    -- ****************************************************************************
    DECLARE @sql                nVarChar(2000)  =   ''

    -- ****************************************************************************
    -- Add comment
    -- ****************************************************************************
    SET @sql = '---- Add comment
                ----IF @Para3 = 0
                    SELECT Top 1 @Para3 =
                    .
                    .
                    .
                '
  --- dont forget error handling
END
GO
;

-- ****************************************************************************
-- Test
-- ****************************************************************************
/*
USE [YourDBName]
GO
;
DECLARE @Para2      Int             =   22
DECLARE @Para3      Int             =   0
EXEC [dbo].[uspYourSPName] 'AnyValue' ,@Para2 , @Para3 OUTPUT
SELECT @Para3 AS '@Para3' , @Para2 AS '@Para2'

*/
+3
Rafnas T P8y ago
hi,
first up all you can not save image in nvarchar, you have to use varbinary or image field
+2
Bryian Tan8y ago
Here are some examples:

https://www.sqlshack.com/creating-using-crud-stored-procedures/
https://social.technet.microsoft.com/wiki/contents/articles/32852.datagridview-crud-with-image-save-to-sql-server-using-c.aspx
https://www.c-sharpcorner.com/article/how-to-create-a-stored-procedure-in-sql-server-management-studio/
+3