RDL to SQL, Must declare the scalar variable

Question

I am trying to take a query I found in a RDL file and run it in SQL. I have no idea what I'm doing. I tried multiple solutions I found through searching the web to no avail. The errors I am getting are: "Must declare the scalar variable" for "@locale", "AuthListID", and "UserSIDs". Any suggestions on where to look or how I can fix this? I am very much a SQL n00b. Thank you in advance for any insight.

declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID(CAST (@locale AS varchar(50))
declare @AuthListLocalID as int = case when IsNumeric(@AuthListID)=1 then cast(@AuthListID as int) else (select CI_ID from fn_rbac_AuthListInfo(@lcid,@UserSIDs)  where CI_UniqueID=@AuthListID) end
declare @ci table(CI_ID int primary key, CI_UniqueID nvarchar(256), Title nvarchar(512), ArticleID nvarchar(64), BulletinID nvarchar(64), Vendor0 nvarchar(256))
insert @ci
select ui.CI_ID, ui.CI_UniqueID, ui.Title, ui.ArticleID, ui.BulletinID, ven.CategoryInstanceName
from fn_rbac_BundledConfigurationItems(@UserSIDs)  bci
join fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui on ui.CI_ID=bci.BundledCI_ID
left join fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) ven on ven.CI_ID=ui.CI_ID and ven.CategoryTypeName='Company'
where bci.CI_ID=@AuthListLocalID

Answer 1

It seems to me it should look something like this. Hopefully, you have access to all the functions being called, or you might have more digging to do.

DECLARE 
    @lcid int
    , @AuthListLocalID int
    , @locale varchar(50)
    , @AuthListID int
    , @UserSIDs int;
DECLARE @ci TABLE(CI_ID int primary key
                    , CI_UniqueID nvarchar(256)
                    , Title nvarchar(512)
                    , ArticleID nvarchar(64)
                    , BulletinID nvarchar(64)
                    , Vendor0 nvarchar(256));

SET @lcid = dbo.fn_LShortNameToLCID(@locale)
SET @AuthListLocalID = CASE 
                        WHEN IsNumeric(@AuthListID)=1 
                        THEN @AuthListID 
                        ELSE (SELECT 
                                CI_ID 
                                FROM fn_rbac_AuthListInfo(@lcid,@UserSIDs)  
                                WHERE CI_UniqueID=@AuthListID) 
                        END

INSERT INTO @ci
VALUES (SELECT 
    ui.CI_ID
    , ui.CI_UniqueID
    , ui.Title
    , ui.ArticleID
    , ui.BulletinID
    , ven.CategoryInstanceName
FROM fn_rbac_BundledConfigurationItems(@UserSIDs)  bci
INNER JOIN fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui 
    ON ui.CI_ID=bci.BundledCI_ID
LEFT OUTER JOIN fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) ven 
    ON ven.CI_ID=ui.CI_ID 
    AND ven.CategoryTypeName='Company'
WHERE bci.CI_ID=@AuthListLocalID)