stored procedure

Question

am a begginner ina sp.net...i am currently using stored procedure concept in VB.I want to know one thing also...can i use more than one action ina procedure..means....i tried one stored procedure for insert...in tha same application i want to delete,update,select also......for that i have to write separate procedure or same procedure for all those actions...

Scott Lysle · Answer

You can write your stored procedures so that they will do either an update or a insert depending upon whether or not the record already exists; here is a enough of an example for you to see what I mean; it CValue is unique; start off with a select to see if it exists if it does you do an update if it doesn't you do an insert: CREATE PROCEDURE [dbo].[usp_IUStates] ( @CValue CHAR(2) @CText VARCHAR(40) @CountryCode CHAR(10) ) AS IF EXISTS(SELECT 1 from TblStates WHERE CValue=@CValue ) UPDATE TblStates SET CValue=@CValue  CText=@CText  CountryCode=@CountryCode WHERE CValue=@CValue ELSE INSERT TblStates ( CValue CText CountryCode ) VALUES ( @CValue @CText @CountryCode )