c# - 如何在 SQL Server 2012 存储过程中声明表变量？

Question

我正在尝试创建一个存储过程，但出现以下问题：

消息 2715，级别 16，状态 3，过程 usp_UpatePriv，第 1 行列、
参数或变量 #1：找不到数据类型 tblScreen。参数或变量“@screen”的数据类型无效。

消息 1087，级别 16，状态 1，过程 usp_UpatePriv，第 8 行
必须声明表变量“@screen”。

这是存储过程：

create procedure usp_UpatePriv
    @screen tblScreen Readonly
as
    declare @username nvarchar(50)
    declare @prev bit
    declare @idscreen bigint

    declare privlange cursor for
        (select username, idScren, Prev from @screen)

    open privlange

    fetch next from privlange into @username, @idscreen, @prev

    while @@FETCH_STATUS = 0  
    begin
        update tblScreenPriv 
        set Prev = @prev 
        where Username = @username and idScreen = @idscreen
        
        fetch next from privlange into @username, @idscreen, @prev
    end
    
    close privlange;  
    deallocate privlange;

标签： c#sql-server

Answer 1

您需要声明表值类型，并且应该摆脱游标。例如：

create type tblScreen as table(username nvarchar(50), idScren int, Prev bit)
go

create proc usp_UpatePriv
 @screen  tblScreen Readonly
as
begin
        with q as 
        (
          select o.Prev, n.Prev NewPrev
          tblScreenPriv o
          join @screen n
            on o.UserName = n.UserName
           and o.idScreen = n.idScreen
        )
        --select * from q
        update q set Prev = NewPrev;
end