id int identitty(1,1)
productname varchar(20)
Status char(1) like this
IF(@ACTION='U')
BEGIN
IF EXISTS(SELECT P_ID FROM TBL_PRODUCTS WHERE P_ID=@P_ID)
BEGIN
set @exp=(select COUNT(*) from TBL_PRODUCTS where P_NAME=@P_NAME)
set @exp1=(select count(*) from tbl_products where p_id=@P_ID and p_name=@P_NAME)
if((@exp<=1)and (@exp1=1))
Begin
UPDATE TBL_PRODUCTS SET
P_NAME=@P_NAME,
P_TAG_LINE=@P_TAG_LINE,
P_WEB=@P_WEB,
P_ACTIVE=@P_ACTIVE
WHERE P_ID=@P_ID;
IF(@@ERROR=0)
BEGIN
SET @ERROR_ID=1
END
ELSE
BEGIN
SET @ERROR_ID=0
END
End
ELSE
BEGIN
SET @ERROR_ID=-2
END
END
ELSE
BEGIN
SET @ERROR_ID=-2
END
END
here i can able to Update a record if the id is same and name is same and i want to do
product name should not repeat twice in table..
here i can able to Update a record if the id is same and name is same and i want to do
product name should not repeat twice in table..
Naveen KumarPosted Jul 22, 2014, 6:39 AM
It works fine but i want the thing is the product name should be unique it should not get repated.
While inserting i had kept the condition but while updating i need to check the product name already exists
if the id which is in edit mode and product name is same allowed to update otherwise
it should be update And throw Exception Product Name Already Exists..
Khan Abrar AhmedPosted Jul 22, 2014, 4:18 AM
use the below code
CREATE PROCEDURE P_Update_Product
(
@P_ID INT,
@P_NAME VARCHAR(512) =null,
@P_TAG_LINE VARCHAR(512) =null,
@P_WEB VARCHAR(512) =null,
@P_ACTIVE BIT =NULL,
@ACTION varchar(6)
)
AS
BEGIN
DECLARE @ERROR_ID INT=0;
IF ( @ACTION = 'U' )
BEGIN
IF EXISTS ( SELECT
P_ID
FROM
TBL_PRODUCTS
WHERE
P_ID = @P_ID )
BEGIN
UPDATE
TBL_PRODUCTS
SET
P_NAME = @P_NAME ,
P_TAG_LINE = @P_TAG_LINE ,
P_WEB = @P_WEB ,
P_ACTIVE = @P_ACTIVE
WHERE
P_ID = @P_ID
AND P_NAME = @P_NAME;
IF ( @@ERROR = 0 )
BEGIN
SET @ERROR_ID = 1
END
ELSE
BEGIN
SET @ERROR_ID = 0
END
END
ELSE
BEGIN
SET @ERROR_ID = -2
END
END
SELECT @ERROR_ID
END