CODEWITHSUNDEEP
sql-servert-sql
Nick Sandel
Nick Sandel

Reputation: 112

What is the mandatory nature of User Defined Tables in Stored Procedures

At the company I work there are a range of stored procedures which take a user-defined table as an input parameter and is it happens this is not always needed for the procedure being used. In SSMS if write an Execute statement without defining the UDT I get the error line with a tooltip telling me the procedure expects the table to be provided, but if I try to run without providing the table it works just fine.

This makes me assume these are optional parameters even though they don't have a clear default like other input parameters would need? If that's the case how can you force them to be non-optional?

As it is for my company procedures the non-optional nature is preferable but I'd like to know why this is as a learning point and how to get around it please.

This SQL demonstrates my question:

CREATE TYPE Dummy_Table AS TABLE (ID INT, Name VARCHAR(50));
GO

CREATE PROCEDURE Dummy_Procedure @Mode VARCHAR(50), @Dummy_Table Dummy_Table READONLY
AS
BEGIN
SELECT @Mode

SELECT * FROM @Dummy_Table
END
GO

EXEC Dummy_Procedure @Mode = 'Dummy_Mode'

Upvotes: 0

Views: 65

Answers (1)

Tom
Tom

Reputation: 461

TVPs are optional, and they can produce some weird behavior if you are expecting records and don't get any. You could include some logic to check if there are any records in the table.

This article also helps: https://msdn.microsoft.com/en-us/library/bb510489.aspx

CREATE TYPE Dummy_Table AS TABLE (ID INT, Name VARCHAR(50));
GO

ALTER PROCEDURE Dummy_Procedure @Mode VARCHAR(50), @Dummy_Table Dummy_Table  READONLY
AS
BEGIN
SELECT @Mode

declare @test int
SELECT @test = COUNT(*) FROM @Dummy_Table

if @test = 0 
select 'stop' 
else 
select 'continue'

END
GO

Upvotes: 1

Related Questions