Блог

Я получаю сообщение об ошибке при использовании хранимой процедуры. Ниже приведен код хранимой процедуры:

   ALTER PROCEDURE [dbo].[usp_specificorderchangedhistory]
        -- Add the parameters for the stored procedure here
                @startRowIndex int,
    @maximumRows int, 
    @OrderDate datetime= null   
AS
BEGIN

DECLARE @first int,@last int
  SET @first = @startRowIndex * @maximumRows
  SET @last = (@startRowIndex * @maximumRows)   @maximumRows
declare @sql varchar(max)
declare @sqlcnt varchar(max)
declare @cond varchar(max)

        if @OrderDate is not null 
        begin 
            set @cond = ' and convert(varchar,so.OrderDate,101) =' convert(varchar,@OrderDate,101) ' ' 
        end
        print @cond
set @sql= 'SELECT * FROM

(

    SELECT ROW_NUMBER() OVER (ORDER BY so.OrderID DESC) AS Row, 

   so.OrderID,

   u1.Firstname '   ''' '''   ' u1.Lastname as Username,

   U.Firstname as CustomerName,

   so.OrderNumber,

   so.TotalAmount,

   so.Status,

   so.OrderDate,

   so.Comment,

   p.PaymentMode as PaymentMethod,

   p.PaymentMethod as Payment_Mode,

   p.IsPaid

    FROM SpecificOrders AS so

    INNER join Users as U on so.UserID = U.UserID

    INNER join Users as u1 on so.CreatedBy = u1.UserID       

    INNER join SpecificOrderPayment as p on so.OrderID= p.OrderID  

 '  @cond  '

) spec

WHERE Row>' Convert(Varchar,@first ) '  AND 

  Row<=' Convert(Varchar,@last)
print @sql
exec(@sql)
set @sqlcnt= 'SELECT count(*) FROM

(

    SELECT ROW_NUMBER() OVER (ORDER BY so.OrderID DESC) AS Row

    FROM SpecificOrders AS so

    INNER join Users as U on so.UserID = U.UserID

    INNER join Users as u1 on so.CreatedBy = u1.UserID   

    INNER join SpecificOrderPayment as p on so.OrderID= p.OrderID  


 '  @cond  '

) spec'
exec(@sqlcnt)
end
  

когда я выполняю хранимую процедуру, используя запрос ниже :

 EXEC usp_specificorderchangedhistory '08/20/2016'
  

Получение ошибки как :

 Conversion failed when converting the varchar value '29/09/2016' to data type int.
  

Хотя я попытался выполнить тот же запрос без хранимой процедуры, используя

выберите

запрос.Это работает точно по желанию: ниже приведен запрос выбора:

 select * as Dateorder 
from SpecificOrders as s 
inner join SpecificOrderPayment as p 
    on s.OrderID = p.OrderID 
where convert(varchar,s.orderdate,101) =convert(varchar,'08/20/2016',101) 
  

Спасибо

Добавьте больше кавычек и используйте SELECT:

 ALTER PROCEDURE [dbo].[usp_specificorderchangedhistory]
        @OrderDate datetime= null   
AS
BEGIN
    declare @cond varchar(max)
    if @OrderDate is not null 
    set @cond = ' and convert(varchar,so.OrderDate,101) =''' convert(varchar,@OrderDate,101) '''' 

    SELECT @cond
end

EXEC usp_specificorderchangedhistory '08/20/2016' 
  

Вывод:

  and convert(varchar,so.OrderDate,101) ='08/20/2016'
  

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

Я не понимаю, почему вы используете динамический SQL? Все можно было бы сделать так:

 ALTER PROCEDURE [dbo].[usp_specificorderchangedhistory]
    @startRowIndex int,
    @maximumRows int, 
    @OrderDate datetime= null   
AS
BEGIN

    DECLARE @first int,
            @last int

    SET @first = @startRowIndex * @maximumRows
    SET @last = (@startRowIndex * @maximumRows)   @maximumRows

    SELECT * 
    FROM (
        SELECT  ROW_NUMBER() OVER (ORDER BY so.OrderID DESC) AS [Row], 
                so.OrderID,
                u1.Firstname ' ' u1.Lastname as [Username],
                U.Firstname as CustomerName,
                so.OrderNumber,
                so.TotalAmount,
                so.Status,
                so.OrderDate,
                so.Comment,
                p.PaymentMode as PaymentMethod,
                p.PaymentMethod as Payment_Mode,
                p.IsPaid
        FROM SpecificOrders AS so
        INNER join Users as U 
            on so.UserID = U.UserID
        INNER join Users as u1 
            on so.CreatedBy = u1.UserID       
        INNER join SpecificOrderPayment as p 
            on so.OrderID= p.OrderID  
                and so.OrderDate = @OrderDate
    ) spec
    WHERE [Row]>@first  AND [Row]<=@last

    SELECT count(*) 
    FROM (

        SELECT ROW_NUMBER() OVER (ORDER BY so.OrderID DESC) AS [Row]
        FROM SpecificOrders AS so
        INNER join Users as U 
            on so.UserID = U.UserID
        INNER join Users as u1 
            on so.CreatedBy = u1.UserID   
        INNER join SpecificOrderPayment as p 
            on so.OrderID= p.OrderID 
                and so.OrderDate = @OrderDate
    ) spec

end