i m using SQL Server 2000, in this i have the follwowing SP,
CREATE PROCEDURE AddNewCountry @CountryName varchar(50), @CountryCode varchar(5)
ASIF exists(Select CountryName from Countries where CountryCode=@CountryCode or CountryName=@CountryName)beginraiserror('Country Already Exist',16,1)endelsebeginINSERT INTO CountriesVALUES (@CountryName, @CountryCode)endGOin this SP in the where clause i used the following line :IF exists(Select CountryName from Countries where CountryCode=@CountryCode or CountryName=@CountryName) (used OR operator)but it inserted the data if i give 092,'america'
and if i used AND operator it chekc 092, 'america' if yes then shows error but if i give 092,'Japan' it inserted new row in the Countries table.
plz give me some other idea what changes i made in my SP as unique Country added with unique Code. if code exists then not allowed to used to enter data in the table same inthe case of country name .
plz give me idea what updation i made as after this my SP works fine.
In this case you have to use Or operator instead of AND operator.
And you can enforce Unique Constraint on both tables (not composite)
Create Table Countries(
CountryCode Int Unique,
CountryName Varchar(100) Unique
Microsoft is conducting an online survey to understand your opinion of the Msdn Web site. If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.
Would you like to participate?