Блог

Я использую SQL Server 2008 R2 . У меня возникли некоторые проблемы с поиском эффективного шаблона кодирования, для SQL которого поддерживается повторное использование кода, а также гибкость. Под повторным использованием я подразумеваю сохранение SQL запросов в Stored Procedures и User Defined Functions .

Теперь, если я выберу Stored Procedures , я пожертвую его удобством использования непосредственно в запросе. Если я выберу User Defined Functions , я не смогу использовать DML инструкции.

Например, предположим, что я создал, Stored Procedures который вставляет одну запись контакта. Теперь, если у меня есть таблица, которая может выступать в качестве источника нескольких записей контактов, все, что у меня остается, — это WHILE циклы или CURSOR s, что явно не рекомендуется из-за его недостатков в производительности. И из-за того, что инструкции DML не разрешены в User Defined Functions , я просто не могу использовать их для этой цели.

Хотя, если меня не интересует повторное использование кода, то вместо использования Stored Procedures я, безусловно, могу использовать один и тот же набор запросов снова и снова, чтобы избежать циклов while.

Какому шаблону я должен следовать?

Вот аналогичный Stored Procedures :-

 ALTER Proc [dbo].[InsertTranslationForCategory]                           
(                          
 @str nvarchar(max),                          
 @EventId   int,                         
 @CategoryName NVarchar(500),                        
 @LanguageId int,                       
 @DBCmdResponseCode Int Output,                    
 @KeyIds nvarchar(max)  Output                     
)as                          
BEGIN                    

DECLARE @XmlData XML                           
DECLARE @SystemCategoryId Int                      
DECLARE @CategoryId Int                      
Declare @Counter int=1                      
Declare @tempCount Int                      
Declare @IsExists int                      
Declare @TranslationToUpdate NVarchar(500)                     
Declare @EventName Varchar(200)                       
declare @Locale nvarchar(10)                    
declare @Code nvarchar(50)                    

declare @KeyName nvarchar(200)                    
declare @KeyValue nvarchar(500)                    

select @Locale=locale from languages where languageid =  @LanguageId                    

  SET @DBCmdResponseCode = 0                     
  SET @KeyIds = ''                    
  select   @EventName = eventName from eventLanguages                    
  where eventID = @EventId                    
--BEGIN TRY                         

   Select @SystemCategoryId=CategoryId from SystemCategories  where Name=rtrim(ltrim(@CategoryName))                      
   Select @CategoryId=CategoryId from Categories  where Name=rtrim(ltrim(@CategoryName)) and EventId=@EventId                      
   if (@str='deactivate')              
  Begin              
  Delete from Codetranslation where CategoryId=@CategoryId               
  Update Categories set [Status]=0, Isfilter=0 where  CategoryId=@CategoryId  and Eventid=@EventId              
    Set @DBCmdResponseCode=2              
  return              
  End                 
   set @XmlData=cast(@str as xml)                      
 DECLARE @temp TABLE                      
 (                      
   Id int IDENTITY(1,1),                      
   Code varchar(100),                      
   Translation varchar(500),                      
   CategoryId  int                       
 )                      



 Insert into @temp (Code,Translation,CategoryId)                      
 SELECT                         
    tab.col.value('@Code', 'varchar(200)'),                                            
    tab.col.value('@Translation', 'varchar(500)'),@SystemCategoryId                              
    FROM   @XmlData.nodes('/Data') AS tab (col)                       



  select @tempCount=Count(*) from @temp                      

  if(IsNull(@CategoryId,0)>0)                      
  Begin                        

    While (@Counter <= @tempCount)                      
    Begin                       

       Select @IsExists= count(sc.categoryid) from @temp t Inner Join SystemCodetranslation sc                      
       On sc.categoryid=t.CategoryId                      
       where ltrim(rtrim(sc.code))=ltrim(rtrim(t.code)) and  ltrim(rtrim(sc.ShortTranslation))=ltrim(rtrim(t.Translation))                      
      and t.Id= @Counter                      
         print @IsExists              
         Select @Code = Code , @KeyValue = Translation from @temp where id=@counter                        


      set @KeyName =   ltrim(rtrim(@EventName))   '_'   ltrim(rtrim(@CategoryName))    '_'     ltrim(rtrim(@Code))   '_LT'                    
      exec dbo.AddUpdateKeyValue  @EventId,@Locale, @KeyName,@KeyValue,NULL,12                    

      select  @KeyIds = @KeyIds    convert(varchar(50),keyvalueId)   ',' from dbo.KeyValues                    
      where eventid = @EventId and keyname = @KeyName and locale =  @Locale                    


      set @KeyName = ''                    
      set @KeyValue = ''                    

     Set @Counter= @Counter   1                      

     Set @IsExists=0                      

    End                      

  End                       




  --- Inser data in Codetranslation table                      
  if(isnull(@CategoryId,0)>0)                      
  Begin                      
  print @CategoryId                      
  Delete from codetranslation where categoryid=@CategoryId                     
  Insert into codetranslation (CategoryId,Code,LanguageId,ShortTranslation,LongTranslation,SortOrder)                      
   SELECT                         
    @CategoryId,                      
    tab.col.value('@Code', 'varchar(200)'), @LanguageId,                                           
    tab.col.value('@Translation', 'varchar(500)'),                       
    tab.col.value('@Translation', 'varchar(500)'),0                             
    FROM   @XmlData.nodes('/Data') AS tab (col)                  
    Update Categories set [Status]=1 where  CategoryId=@CategoryId  and Eventid=@EventId                

  End                      
  Set @DBCmdResponseCode=1                      
set @KeyIds =  left(@KeyIds,len(@KeyIds)-1)                                            


END     
  

Вы можете использовать параметр переменной таблицы для своих пользовательских функций.

следующий код является примером использования параметра табличной переменной в хранимой процедуре.

 CREATE TYPE IdList AS TABLE (Id INT)

CREATE PROCEDURE test 
    @Ids dbo.IdList READONLY
AS
   Select *
   From YourTable
   Where YourTable.Id in (Select Id From @Ids)
End
GO
  

Для выполнения вашей хранимой процедуры используйте следующий формат:

 Declare @Ids dbo.IdList
Insert into @Ids(Id) values(1),(2),(3)

Execute dbo.test @Ids
  

Редактировать

Чтобы вернуть вставленный идентификатор, я не использую параметр переменной from Table. Для этой цели я использую следующий пример запроса.

 --CREATE TYPE NameList AS TABLE (Name NVarChar(100))

CREATE PROCEDURE test
    @Names dbo.NameList READONLY
AS
   Declare @T Table(Id Int)

   Insert Into YourTable (Name)
   OUTPUT Inserted.Id Into @T
   Select Name 
   From @Names

   Select * From @T
End
GO