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...
Loading
Scott LyslePosted Jan 19, 2008, 6:03 AM
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 )