CODEWITHSUNDEEP
sqlsql-serverstored-procedures
User7291
User7291

Reputation: 1115

drop rows in a user defined table type

i have the following type:

CREATE TYPE [dbo].[locationTable] AS TABLE(
[location_id] [varchar](100) NULL,
[name] [nvarchar](100) NULL,
[address] [varchar](100) NULL,
[latitude] [varchar](100) NULL,
[longitude] [varchar](100) NULL,
[distance] [varchar](100) NULL,
[state] [varchar](100) NULL,
[sub_cat] [varchar](100) NULL,
[idCat] [varchar](100) NULL,
[icon_link] [varchar](100) NULL,
[checkinsCount] [int] NULL
)
GO

and i'm passing a table as parameter having the above type to a stored procedure... but i need to delete some rows from this table in my stored procedure but i keep getting that it cannot be modified.... sql is always requesting to define the table as readonly and in this case i can not modify

Upvotes: 1

Views: 4091

Answers (1)

Andomar
Andomar

Reputation: 238068

A table parameter to a stored procedure must be readonly. MSDN says:

Note that the READONLY keyword is required for declaring a table-valued parameter.

You can solve this dilemma by copying the content to a local table variable. For example:

if exists (select * from sys.procedures where name = 'TestProc')
    drop procedure TestProc
if exists (select * from sys.types where name = 'TestType')
    drop type TestType
go
create type TestType as table (id int, name varchar(20))
go
create procedure dbo.TestProc
    @par TestType readonly
as
declare @t TestType

insert @t select * from @par

delete @t where id = 2

select * from @t
go
declare @p1 TestType
insert @p1 values (1,'a'), (2,'b'), (3,'c');
exec dbo.TestProc @p1

Upvotes: 3

Related Questions