Computers in a specific state for a deployment - add collection prompt

Transact-SQL
1

I would like to add a prompt for collection to this report. I made a copy and added the CollectionID parameter prompt and what I thought was the right code, but I keep getting "Must declare the scalar variable '@CollectionID'. If I do declare it, I start syntax errors on a join. Has anyone ever attempted this before?

Thanks,
Jeff

2

What report? What collection?

Post your code!

3

I copied the States 4 - Computers in a specific state for a deployment into a new custom. Here is the main code. I get all the prompts and values, but the execution fails.

declare @asnid int = (select AssignmentID from fn_rbac_CIAssignment(@UserSIDs) where Assignment_UniqueID=@DEPLOYMENTID and AssignmentType in (1,5))
declare @CollectionID As varchar
select
s.ResourceID,
m.Name0 as ComputerName0,
m.User_Domain0+''+m.User_Name0 as LastLoggedOnUser,
asite.SMS_Assigned_Sites0 as AssignedSite,
m.Client_Version0 as ClientVersion,
s.StateTime as DeploymentStateTime,
(s.LastStatusMessageID&0x0000FFFF) as ErrorStatusID,
sn.StateName as Status,
a.Assignment_UniqueID as DeploymentID,
statusinfo.MessageName as ErrorStatusName
from fn_rbac_CIAssignment(@UserSIDs) a
join (
select AssignmentID, ResourceID, StateType, StateID, StateTime, LastStatusMessageID from fn_rbac_AssignmentState_Combined(@UserSIDs) where @STATUS/10000 in (300,301)
union all
select AssignmentID, ResourceID, TopicType, StateID, StateTime, LastStatusMessageID from fn_rbac_AssignmentStatePerTopic(@UserSIDs) where @STATUS/10000 in (302)
) s on s.AssignmentID=a.AssignmentID and s.StateType=@STATUS/10000 and s.StateID = @STATUS%10000
left join fn_rbac_StateNames(@UserSIDs) sn on sn.TopicType=s.StateType and sn.StateID=isnull(s.StateID, 0)
left join v_FullCollectionMembership_Valid on v_FullCollectionMembership_Valid.resourceid = m.ResourceID
where m.ResourceID in (select ResourceID from v_FullCollectionMembership_Valid where CollectionID = @CollectionID)
join fn_rbac_R_System(@UserSIDs) m on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)=0
left join fn_rbac_RA_System_SMSAssignedSites(@UserSIDs) asite on m.ResourceID = asite.ResourceID
left join fn_rbac_AdvertisementStatusInformation(@UserSIDs) statusinfo on statusinfo.MessageID=nullif(s.LastStatusMessageID&0x0000FFFF, 0)
where a.AssignmentID=@asnid
order by m.Name0

4 
WHERE     m.ResourceID IN (   SELECT ResourceID
                              FROM   v_FullCollectionMembership_Valid
                              WHERE  CollectionID = @CollectionID )
join fn_rbac_R_System(@UserSIDs) m on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)=0

You can't have JOIN after WHERE

5

Well, that confirms it. I'm a dim wit. The report processes now, but no data. That's still progress -:wink:

1 Like
6

:wink: And...may I suggest you use a SQL formatter? Like Apex SQL Refactor (free) or SQL Prompt ($) or Poor Man's SQL Formatter (free)

It makes your code easier to read and spot typos

7

I firmly believe that well considered coding standards (i.e. for layout of code) constitutes Defensive Programming. That stops bugs before they occur ...

... over the years we've changed coding style (which is a PITA because we then have "Old Code " and "New Code" that are different, and all the effort of "improving" the layout of the old code) specifically because we think that a given coding style reduces bugs.

8

My apologies about that. But thank you for getting me this far.

9

Are you familiar with https://www.red-gate.com/library/defensive-database-programming?

Also, I use SQL Code Guard https://www.red-gate.com/products/sql-development/sql-code-guard/index, currently free, to do static analysis.

It's amazing the things you miss when you eyeball your code or a colleague's

10

Thanks for the links, I'll take a look

Indeed :slight_smile: We have peer-review of code as part of check-in to code repository - validating against coding standards, and looking for trouble - but often the two are highlighting the same bug!

EDIT: No, not seen either of those, off to have a full look now, thanks again.

11

We don't usually do code review but if we do it is in production and it is done by meself :rofl::sweat_smile:

12

I don' think there is anything wrong with that.

On solo-projects I've done that. Build code during the week and then on a Saturday check the code into the repository, and do a DIFF against the previous version (already in the repository). Having left it several days since the code was written, and doing the compare-against-previous enables me to quickly decide that codes changes are acceptable. I certainly found issues with the code doing that, so was worth "something" at least :slight_smile: