Usage of stored procedures and differences with C # code

 

The following is a stored procedure application.

 

Set ANSI_NULLS ON

Set QUOTED_IDENTIFIER ON

GO

-- ===================================================== ======

-- Author: <Author, Name>

-- Create date: <Create Date,>

-- Description: <Description,>

-- ===================================================== ======

Create procedure <Procedure_Name, sysname, ProcedureName>

-- Add the parameters for the stored procedure here

@ Title nvarchar (100), -- news title

@ Content TEXT, -- news content

@ PubUser NVARCHAR (50), -- publisher

@ Catids varchar (200), -- news category list, separated ":"

@ Error nvarchar (200) OUTPUT -- used to return error information

AS

BEGIN

-- Set nocount on added to prevent extra result sets from

-- Interfering with SELECT statements.

Set nocount on;

Declare @ newsid int -- news id

Declare @ catid varchar (10) -- news category id

Declare @ pos int -- the position of the separator ":" In the category list

Begin transaction

Begin try

Insert into newscontent (title, [content], pubuser) values (@ title, @ content, @ pubUser)

Set @ newsid =@@ identity

While (len (@ catids)> 0)

Begin

Set @ pos = charindex (':', @ catids)

If (@ pos <> 0)

Begin

Set @ catid = substring (@ catids, 1, @ pos-1)

Set @ catids = substring (@ catids, @ pos + 1, @ pos)

End

Else

Begin

Set @ catid = @ catids

Set @ catids =''

End

Insert into newscategory (newsid, catid) values (@ newsid, cast (@ catid as int ))

End

Commit transaction

Return 0

End try

Begin catch

Set @ error = error_message ()

Rollback transaction

Return 1

End catch

END

 

The content of the stored statement must be written between begin and end.

For example:

If statement in c #

If (){};

The if statement in the Stored Procedure

If ()

Begin statement end

 

Write the catch statement

Begin catch

// Statement

End catch

 

Different methods are used to declare variables in the form of declare @ num nvarchar.

Use set to assign values

From: column yu851293483