vendredi 19 avril 2019

How to pass a test data table as a parameter in a stored procedure

I have a stored procedure that I want to test. It uses an already created User-Defined Table Type ([TicketFields]) which essentialls just has a column called fieldNames that holds nvarchar field names ('Requestor Name, Requestor Org, etc...). It then creates a temporary table uses the @keyword user parameter to search for that keyword within the selected field names. My problem is that when I execute it to test, I can't simply plug in a datatable as a parameter.

ALTER PROCEDURE [dbo].[bcasp_GetTicketsByKeyword] 
@Keyword nvarchar(150),
@fieldsTable [TicketsFields] READONLY 
AS
BEGIN
CREATE TABLE #Ticket(
    [ID] [bigint] NULL,
    [TicketNumber] [nvarchar](255) NULL,
    [Complexity] [nvarchar](255) NULL,
    [NatureOfInquiry] [nvarchar](255) NULL,
    [SMEResponseDetail] [nvarchar](255) NULL
)

declare @fieldName nvarchar(100)
declare @sql nvarchar(max)
declare @initSql nvarchar(max)
set @initSql = 'insert into #Ticket (ID, TicketNumber, Complexity, 
NatureOfInquiry, SMEResponseDetail) SELECT ID, TicketNumber, Complexity, 
CASE
    WHEN Complexity = 1 THEN NatureOfInquiry_T1 
    WHEN Complexity In (2,3,4) THEN NatureOfInquiry_T234
    END, SMEResponseDetail FROM T_Ticket WHERE '
declare @whereClause nvarchar(100)
set @whereClause = ' Like' + '''%' + @Keyword + '%'''
DECLARE tblCursor CURSOR FOR SELECT FieldName from @fieldsTable
OPEN tblCursor
FETCH NEXT from tblCursor into @fieldName

WHILE @@FETCH_STATUS = 0  
BEGIN 
set @sql = @initsql + @fieldName + @whereClause
print @sql
EXEC(@sql)

FETCH NEXT from tblCursor into @fieldName
END
Close tblCursor
deallocate tblCursor

select distinct * from #Ticket 
END 

enter image description here

Scenario suppose that wanted to search for keyword "flood" in the columns with fieldNames of "NatureOfInquiry" and "SMEResponseDetail". What could I plug into the below to simulate that and get the correct rows to return?

USE [BCATicketManagementTest]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[bcasp_GetTicketsByKeyword]
        @Keyword = N'flood'
        ???
SELECT  'Return Value' = @return_value

GO

Aucun commentaire:

Enregistrer un commentaire