You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:
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?
A.
Update @CustcmerTypelist to use the int data type.
B.
Convert @customerTypeList to a table variable.
C.
Convert @CustomerTypeList to an XML variable.
D.
Update @CustomerTypeList to use the XML data type.
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?
0
0
Hmm. Part of the sql above has been stripped because it contained tags 🙁
Will code tags work here?
[code]SELECT @custListXML = CAST(‘‘ + REPLACE(@CustomerTypeList, ‘,’, ‘‘)+ ‘‘ AS XML)[/code]
0
0