CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresparameterssql-server-2012
Matt Hensley
Matt Hensley

Reputation: 883

SQL Parameter Syntax: Declare @Parameter [dbo].[Table]

I am working with a stored procedure that has a parameter syntax that I am unfamiliar with. 

CREATE PROC [dbo].[sproc_GetAppSetting]
    @AppSettingIDs [dbo].[SomeTable] READONLY,
    @AppSectionID INT,
    @KeyName varchar(255)
AS
SELECT
    as.AppSettingSettingID,
    as.Value,
    as.ConfigID
FROM
    AppSetting as WITH(NOLOCK)
JOIN
    @AppSettingIDs asIds ON asIds.Val = as.ConfigID
WHERE
    as.AppSectionID = @AppSectionID
    AND as.Status = 'A'
    AND as.Name = @KeyName

My question is about the line:

@AppSettingIDs [dbo].[SomeTable] READONLY

It appears that this variable is being used as a reference to a table later in the query:

JOIN
    @AppSettingIDs asIds ON asIds.Val = as.ConfigID

The table [dbo].[SomeTable] does not exist in the database I am working with. Further more, when I extract the @AppSettingIDs declaration line as an attempt to understand what is stored in the variable outside of the context of the stored procedure, I get a SQL error:

DECLARE @AppSettingIDs  [dbo].[SomeTable]

Msg 2715, Level 16, State 3, Line 1
Column, parameter, or variable #1: Cannot find data type dbo.SomeTable. Parameter or variable '@AppSettingIDs' has an invalid data type.

Can some one clarify what that parameter declaration is doing & how I might be able to replicate it outside of a stored procedure?

Upvotes: 1

Views: 2617

Answers (1)

StackUser
StackUser

Reputation: 5398

@AppSettingIDs [dbo].[SomeTable] READONLY

This means that you are declaring a variable using a user defined Table Type.

Your variable @AppSettingIDs can hold many rows as per your need. The structure of the variable is very similar to your User defined Table Type since it is copy of user defined Table Type.

Refer the screenshot to find the location of user defined table types in your database.

enter image description here

Upvotes: 1

Related Questions