You need to ensure that up_customers returns rows when the following statement is executed: EXECUTEup_customers’1,2,3,4,5′; What should you do?

Mick

This question seems fairly vague.

I can achieve the goal with the following code…

CREATE TABLE customers
(
customerId int identity
, FirstName NVARCHAR(255)
, LastName NVARCHAR(255)
, CustomerTypeId INT
)

INSERT INTO customers (FirstName, LastName, CustomerTypeId)

VALUES
(N’John’, N’Barnett’, 1)
, (N’John’, N’Bigboote’, 2)
, (N’John’, N’Camp’, 3)
, (N’John’, N’Runningbear’, 4)
, (N’John’, N’Careful Wlaker’, 5)
, (N’John’, N’Chief Crier’, 6)
, (N’John’, N’Cooper’, 7)
, (N’John’, N’Coyote’, 1)
, (N’John’, N’Edwards’, 2)

GO
CREATE PROCEDURE up_customers @CustomerTypeList NVARCHAR(400)
AS
DECLARE @tblCustList AS TABLE (intCustomerType INT)
DECLARE @custListXML AS XML
SELECT @custListXML = CAST(‘‘ + REPLACE(@CustomerTypeList, ‘,’, ‘‘)+ ‘‘ AS XML)
INSERT INTO @tblCustList
SELECT t.value(‘.’, ‘int’) AS intCustomerType
FROM @custListXML.nodes(‘/A’) AS x(t)

SELECT CustomerId
, FirstName
, LastName
–, intCustomerType
FROM customers
INNER JOIN @tblCustList ON CustomerTypeId = [@tblCustList].intCustomerType
–WHERE CustomerTypeId IN (@CustomerTypeList)

GO

EXEC up_customers @CustomerTypeList = ‘1,2,3,4,5’

—————-

NB: There are other ways to achieve this.

None of the distractors fully describes the solution required. Both B and C (assuming that by convert they don’t mean use tsql convert command) describe steps in the process.

I suppose that in the end you need to split the values supplied to a table variable so B. is nominally the best answer?