CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Yousuf
Yousuf

Reputation: 11

Multi values passing through a single parameter

ALTER PROCEDURE Example
     (@BranchCode AS INT,
      @Department VARCHAR(8000),
      @DateFrom AS DATETIME,
      @DateTo AS DATETIME,
      @TransactionNumber AS NVARCHAR(30)
      -- @Delimiter char(1)
    )
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Sql NVARCHAR(MAX)

    SET @Sql = 'Select 
                    Mast.Branch_code, Mast.purerq_id as purerq_id,
                    Max(Mast.purreq_date) as purreq_date, Max(Mast.Dept_code) as Dept_code, 
                    Max(Mast.purreq_desc) as purreq_desc,
                    Sum(Det.purreq_qty2) as purreq_qty2, 
                    Det.Item_Code, Max(Mast.purreq_docecNo) as DocNo,
                    Sum(IsNull(Det.purreq_qty1,0)) as purreq_qty1,
                    IsNull(Det.purerq_TranNo,'') as purerq_TranNo,
                    IsNull(Max(Mast.purerq_adduserid),'') as Adduserid,
                    Max(Det.ItemPacking_Code) as ItemPacking_Code
                From 
                    tbl_purreqMaster Mast, tbl_purreq_detail1 Det
                Where 
                    Mast.Branch_code = Det.Branch_code And 
                    Mast.purerq_id = Det.purerq_id And
                    Mast.purreq_date Between ' + @DateFrom + ' And ' + @DateTo + ' And 
                    Mast.Dept_code IN (' + REPLACE( @Department, '''','') + ')  
                    Mast.Branch_code = ' + CAST(@BranchCode AS VARCHAR)   + ' AND
                    Mast.purreq_docecNo =' + @TransactionNumber + ' 
                Group By 
                    Mast.Branch_code, Mast.purerq_id, Det.Item_Code, Det.purerq_TranNo'

    PRINT @Sql 
    --exec sp_executesql @Sql
END
GO


EXEC Example 1,'7,8','2017-10-01 00:00:00','2017-10-30 00:00:00','ALIA/PURQGEN/0000001'

This query shows this error

Msg 241, Level 16, State 1, Procedure Example, Line 18
Conversion failed when converting date and/or time from character string.

However I have tried replace and cast functions to resolve this but I found no solution

Upvotes: 1

Views: 50

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

SQLZim's solution should work. But since he doesn't mention it, I will point out that the reason for the error is this line:

Mast.purreq_date Between ' + @DateFrom + ' And ' + @DateTo + ' And

You need to CAST/CONVERT your datetime variables to nvarchar, and surround them with single-quotes.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

I would use the @params that go along with sp_executesql for the parameters that do not need to be concatenated to your executed string. I would also recommend some more processing on @Department to prevent sql injection.

ALTER PROCEDURE Example
     (@BranchCode AS INT,
      @Department VARCHAR(8000),
      @DateFrom AS DATETIME,
      @DateTo AS DATETIME,
      @TransactionNumber AS NVARCHAR(30)
      -- @Delimiter char(1)
    )
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Sql NVARCHAR(MAX), @Params nvarchar(8000);
    set @Params = '@BranchCode INT, @DateFrom DATETIME, @DateTo DATETIME, @TransactionNumber NVARCHAR(30)'

    SET @Sql = 'Select 
                    Mast.Branch_code, Mast.purerq_id as purerq_id,
                    Max(Mast.purreq_date) as purreq_date, Max(Mast.Dept_code) as Dept_code, 
                    Max(Mast.purreq_desc) as purreq_desc,
                    Sum(Det.purreq_qty2) as purreq_qty2, 
                    Det.Item_Code, Max(Mast.purreq_docecNo) as DocNo,
                    Sum(IsNull(Det.purreq_qty1,0)) as purreq_qty1,
                    IsNull(Det.purerq_TranNo,'') as purerq_TranNo,
                    IsNull(Max(Mast.purerq_adduserid),'') as Adduserid,
                    Max(Det.ItemPacking_Code) as ItemPacking_Code
                From tbl_purreqMaster Mast
                  inner join tbl_purreq_detail1 Det
                    on Mast.Branch_code = Det.Branch_code
                   and Mast.purerq_id = Det.purerq_id
                Where Mast.purreq_date Between @DateFrom and @DateTo
                  and Mast.Dept_code IN (' + REPLACE( @Department, '''','') + ')
                  and Mast.Branch_code = @BranchCode
                  and Mast.purreq_docecNo = @TransactionNumber
                Group By 
                    Mast.Branch_code, Mast.purerq_id, Det.Item_Code, Det.purerq_TranNo'

    PRINT @Sql 
    --exec sp_executesql @Sql, @Params, @BranchCode, @DateFrom, @DateTo, @TransactionNumber
END

The best approach in SQL Server 2008 would be using table valued parameters for @DepartmentId:

Upvotes: 2

Related Questions