Geek Answers Handbook

Creating and passing a table value in a separate row

Using SQL Server 2012, can you eliminate the need to declare a table parameter (TVP) in order to pass it to a stored procedure? Below is a very simple example of a stored procedure (SP) that accepts a TVP and a working example to run this SP, where I have to declare a TVP, populate it and pass it to the SP. I would like to be able to simply pass the criteria of the population directly to an EXEC call. Is it possible?

Script setup:

-- Create a sample Users table
CREATE TABLE Users (UserID int, UserName varchar(20))
INSERT INTO Users VALUES (1, 'Bob'), (2, 'Mary'), (3, 'John'), (4, 'Mark')

-- Create a TVP Type
CREATE TYPE UserIdTableType AS TABLE (UserID int)

-- Create SP That Uses TVP Type
CREATE PROCEDURE GetUsers
@UserIdFilter UserIdTableType READONLY
AS
    SELECT * FROM @UserIdFilter WHERE UserID > 2

The working method to execute:

DECLARE @MyIds AS UserIdTableType
INSERT INTO @MyIds SELECT UserID FROM Users
EXEC GetUsers @MyIds

The requested method to execute:

EXEC GetUsers (SELECT UserID FROM Users)
+4
source share
1 answer

, TVP CAST/CONVERT. " ", INT, VARCHAR, DATETIME ..; " ", . (UDTT) - , / . , TVP (Table-Valued Parameter). , , tempdb. , , CAST CONVERT , , .

, , , , , - / ( SELECT * FROM Users WHERE UserID > 2). , T-SQL / , / .

, , , , , , .

  • XML
  • temp ( SQL), , , - ( , //query)

, TVP, TVP . , , - :

Proc_1

SELECT *
INTO #MyTemp
FROM sys.tables;

EXEC dbo.Proc_4 @StartsWith = 'a', @HowMany = 10;

Proc_2

SELECT *
INTO #MyTemp
FROM sys.columns;

EXEC dbo.Proc_4 @StartsWith = 'bb', @HowMany = 20;

Proc_3

SELECT *
INTO #MyTemp
FROM sys.views;

EXEC dbo.Proc_4 @StartsWith = 'ccc', @HowMany = 33;

Proc_4

SELECT TOP (@HowMany) tmp.*
FROM   #MyTemp tmp
WHERE  tmp.[name] LIKE @StartsWith + '%'
ORDER BY tmp.[object_id] ASC;
+4

More articles:


All Articles