在具有存储过程的列中插入唯一值 [英] insert unique value in a column with stored procedure

问题描述

在我的应用程序中我插入存储过程的列名称我希望在一个列中插入唯一值我通过比较dataview中的值在Asp.net中完成它但我觉得它可能很慢所以任何人都可以帮助我的sp是

in my application i am inserting column names with stored procedure i want to insert unique value in one column I have done it in Asp.net by comparing value in dataview but i think it might be slow so can anyone help my sp is

ALTER PROCEDURE [dbo].[spInsertTenderDetail]  
    @TenderRefNumber nvarchar(100)  
   ,@TenderTitle nvarchar(255)  
   ,@MinistryID decimal(18,0)  
   ,@OrgNameID decimal(18,0)  
   ,@OrgTypeID decimal(18,0)  
   ,@ProductCatID decimal(18,0)  
   ,@ProductSubCatName nvarchar(100)  
   ,@TenderValue money  
   ,@TenderEMD money  
   ,@TenderDocCost money  
   ,@TenderTypeID decimal(18,0)  
   ,@FirstAnounceDate datetime  
   ,@PublicationDateOnPortale datetime  
   ,@PublicationDateOnPortaleAt nvarchar(50)  
   ,@LastDateOfDocumentCollection datetime  
   ,@LastDateOfDocumentCollectionUpto nvarchar(50)  
   ,@LastDateForSubmission datetime  
   ,@LastDateForSubmissionUpto nvarchar(50)  
   ,@OpeningDate datetime  
   ,@OpeningDateAt nvarchar(50)  
   ,@WorkDescription nvarchar(max)  
   ,@PreQualification nvarchar(max) 
   ,@ExtraInfo nvarchar(max) 
   ,@PreBidMeetDate nvarchar  
   ,@TenderDoc nvarchar(50)  
   ,@CorrigendumDoc nvarchar(50)  
   ,@BidDoc nvarchar(50)  
   ,@TechDoc nvarchar(50)  
   ,@Sector nvarchar(50)  
   ,@RegionID decimal(18,0)  
   ,@CountryID decimal(18,0)  
   ,@StateID decimal(18,0)  
   ,@CityID decimal(18,0)  
   ,@Location nvarchar(100)  
   ,@InfoName nvarchar(100)  
   ,@InfoEmail nvarchar(50)  
   ,@InfoPhone nvarchar(50)  
   ,@InfoFax nvarchar(50)  
   ,@InfoAdd nvarchar(max)  
   ,@InfoCity nvarchar(100)  
     
 AS  
 BEGIN  
  SET NOCOUNT ON  
 INSERT INTO [TenderDetail]  
           (  
            [TenderRefNumber]  
           ,[TenderTitle]  
           ,[MinistryID]  
           ,[OrgNameID]  
           ,[OrgTypeID]  
           ,[ProductCatID]  
           ,[ProductSubCatName]  
           ,[TenderValue]  
           ,[TenderEMD]  
           ,[TenderDocCost]  
           ,[TenderTypeID]  
           ,[FirstAnounceDate]  
           ,[PublicationDateOnPortale]  
           ,[PublicationDateOnPortaleAt]  
           ,[LastDateOfDocumentCollection]  
           ,[LastDateOfDocumentCollectionUpto]  
           ,[LastDateForSubmission]  
           ,[LastDateForSubmissionUpto]  
           ,[OpeningDate]  
           ,[OpeningDateAt]  
           ,[WorkDescription]  
           ,[PreQualification] 
           ,[ExtraInfo] 
           ,[PreBidMeetDate]  
           ,[TenderDoc]  
           ,[CorrigendumDoc]  
           ,[BidDoc]  
           ,[TechDoc]  
           ,[Sector]  
           ,[RegionID]  
           ,[CountryID]  
           ,[StateID]  
           ,[CityID]  
           ,[Location]  
           ,[InfoName]  
           ,[InfoEmail]  
           ,[InfoPhone]  
           ,[InfoFax]  
           ,[InfoAdd]  
           ,[InfoCity]  
           ,CreatedDate  
           ,Enabled  
           )  
     VALUES  
           (  
            @TenderRefNumber  
           ,@TenderTitle  
           ,@MinistryID  
           ,@OrgNameID  
           ,@OrgTypeID  
           ,@ProductCatID  
           ,@ProductSubCatName  
           ,@TenderValue  
           ,@TenderEMD  
           ,@TenderDocCost  
           ,@TenderTypeID  
           ,@FirstAnounceDate  
           ,@PublicationDateOnPortale  
           ,@PublicationDateOnPortaleAt  
           ,@LastDateOfDocumentCollection  
           ,@LastDateOfDocumentCollectionUpto  
           ,@LastDateForSubmission  
           ,@LastDateForSubmissionUpto  
           ,@OpeningDate  
           ,@OpeningDateAt  
           ,@WorkDescription  
           ,@PreQualification 
           ,@ExtraInfo 
           ,@PreBidMeetDate  
           ,@TenderDoc  
           ,@CorrigendumDoc  
           ,@BidDoc  
           ,@TechDoc  
           ,@Sector  
           ,@RegionID  
           ,@CountryID  
           ,@StateID  
           ,@CityID  
           ,@Location  
           ,@InfoName  
           ,@InfoEmail  
           ,@InfoPhone  
           ,@InfoFax  
           ,@InfoAdd  
           ,@InfoCity  
           ,GETDATE()  
           ,1  
           ) 
           
    Select @@IDENTITY  
       END  

推荐答案

在您想要独特的列上定义唯一索引...请参阅 http://msdn.microsoft.com/en-us/library/ms175132（V = SQL.105）的.aspx [ ^ ]

如果您尝试插入重复值，请准备好处理抛出的任何异常

Define a unique index on the column you want to be unique ... see You http://msdn.microsoft.com/en-us/library/ms175132(v=sql.105).aspx[^]
Be prepared to handle any exceptions thrown if you attempt to insert a duplicate value

插入之前检查记录是否已存在。

Hi,
Before Insert Check the record is already existed or not.

-- Which column is PK ? then using that U can do as like 
IF NOT EXISTS(SELECT 1 FROM TenderDetail WHERE Column_Name=Your_Value)
BEGIN
 INSERT Statement
 SELECT @@Identity
END

问候，

GVPrabu

Regards,
GVPrabu

这篇关于在具有存储过程的列中插入唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！