How to Create Stored Procedure ??

Question

Hey, guy,'s I have 1 table name tbl_aboutus(id primary key, name nvarchar, content nvarchar and image nvarchar) and how to create a stored procedure for insert, update and delete ?

souvik sardar · 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 ;

Shahriar Nikkhah · Answer

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' */

Bryian Tan · Answer

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/

Shahriar Nikkhah · Answer

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

Rafnas T P · Answer

hi, first up all you can not save image in nvarchar, you have to use varbinary or image field

How to Create Stored Procedure ??

Answers (5)