SQLStoredProcedure: new resource proposal

Description

I propose a resource that checks for the presence of a stored procedure. An example - it would be useful in scenarios where the op wanted to check that a particular diagnostic procedure is present & correct - e.g. sp_whoisactive.

Proposed properties

• [string] ServerName
• [string] Instance (MSSQLServer being the default)
• [string] Database in which the SP is to be installed
• [string] Name of the stored procedure
• [string] Path to ,SQL file containing stored procedure
• [string] Checksum of the stored procedure (Optional)

Special considerations or limitations

For the checksum - since Hash_Bytes is limited in the size of the text that can be supplied, the test process could be verified using fn_repl_hash_binary function in master - example;

declare @value varbinary(max)
select @value  = cast((SELECT OBJECT_DEFINITION (OBJECT_ID('sp_whoisactive'))) as varbinary(max))
select master.sys.fn_repl_hash_binary(@value)

A potentially less costly option could be to calculate the length of the stored procedure.

The proposal does not identify any files, tests, or entry points; begin by locating the repository's existing SQL Server DSC resources and their tests. Done means a resource can verify a stored procedure's presence and definition using the proposed properties, with its checksum or length behavior specified and tested.