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
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