Как передать выходной параметр SQL в rest framework

Я пытаюсь передать выходной параметр @response в представлениях Django, но мне не удалось этого добиться. Я пробовал два подхода, то, что я пробовал

views.py:

approach1:

  @api_view(['POST'])
  def FetchSuggestionAuditAgent(request):
    if request.method == 'POST':
        
        agents = request.data.get('Agents')
        Output = request.data.get('Answer')
        AuditorId = request.data.get('AuditorId')
    
        TicketId = request.data.get('TicketId')
        QId = request.data.get('QId')
        Answer = request.data.get('Answer')
        
        print('Agents--', agents)
        
        cursor = connection.cursor()
        cursor.execute('EXEC [dbo].[sp_FetchSuggestionAuditAgent] @agents=%s, @response=%s',
        (agents, ''))

        return Response({
            'Agents':agents })

approach2:

  @api_view(['POST'])
  def FetchSuggestionAuditAgent(request):
    if request.method == 'POST':
        
        agents = request.data.get('Agents')     
    sql = """\
    DECLARE @response nvarchar(max), @agents nvarchar(max);
    
    EXEC [dbo].[sp_FetchSuggestionAuditAgent] @agents = ?, @response = @out OUTPUT;
    SELECT @response AS the_output;
    """
    params = (agents, )
    cursor = connection.cursor()
    cursor.execute(sql, params)

    rows = cursor.fetchall()
    while rows:
        print(rows)
        if cursor.nextset():
            rows = cursor.fetchall()
        else:
            rows = None
    return Response(True)

SQL:

 CREATE [dbo].[sp_FetchSuggestionAuditAgent]

 @agents nvarchar(max),@response nvarchar(1000) OUTPUT        
AS      
BEGIN      
set @agents = replace(@agents,' ', '%')      
   
   select @response = FullName from (
select FullName from tblusers where IsActive=1
union all
select FullName from tblusers where IsActive=0
and UserRole='Snowuser'
)as a
where FullName like @agents--('%'+@agents+'%')      
 
order by FullName desc     

END

 

Вернуться на верх