Table-valued parameter error in SQL Server -
i'm working on reporting module company project. although use orm our application, i've decided write stored procedures reports in anticipation of migrating ssrs.
these stored procedures require table-valued parameter input. such, i've created table type:
use mydatabase go /****** object: userdefinedtabletype [dbo].[intlist] script date: 5/8/2013 5:20:59 pm ******/ create type [dbo].[intlist] table( [id] [int] not null, primary key clustered ( [id] asc )with (ignore_dup_key = off) ) go i have following sql server stored proc:
set ansi_nulls on go set quoted_identifier on go use mydatabase go -- ============================================= -- author: <lunchmeat317> -- create date: <05/06/2013> -- description: <file type report> -- ============================================= alter procedure report_filetype @filetype varchar(20) = null, @user intlist readonly, @group intlist readonly begin set nocount on; /* lf = libraryfile lfu = libraryfileassigneduser lfg = libraryfileassignedgroup */ select extension filetype, count(1) totalfiles libraryfile lf left join libraryfileassigneduser lfu on (select count(1) @user) != 0 , lfu.libraryfileid = lf.id left join libraryfileassignedgroup lfg on (select count(1) @group) != 0 , lfg.libraryfileid = lf.id ((@filetype null) or (extension = @filetype)) , ( ((@user null) or (lfu.userid in (select * @user))) or ((@group null) or (lfg.hubgroupid in (select * @group))) ) group extension end go when attempt alter stored procedure, continually error message
msg 137, level 16, state 1, procedure report_filetype must declare scalar variable "@user". msg 137, level 16, state 1, procedure report_filetype must declare scalar variable "@group". i can't figure out why happening. if use scalar type (and update code match) works. however, when try use tvp, can't compile stored procedure.
for it's worth, i've added type, haven't set permission on yet. however, don't expect cause compilation error; cause error @ runtime (which i've dealt before).
does have experience issue? thanks!
interesting, haven't used before, seems cannot test @user / @group null since table.
cf.
Comments
Post a Comment