Checking a Stored Procedure for a specific comment using VB.NET
-
In a VB.net app, I need to test for the existence of an updated stored procedure on either a SS or Oracle Database. I plan to do this by placing a comment at the begining of the stored procedure indicating the version of the Stored Procedure. How would I code a vb.net routine to read the first several lines of a stored procedure to see if the version I am looking for is in the comments?
-
HI,
As long as the proc is not compiled with encryption (SQL Server) you can select text from the syscomments system table.
Select syscomments.text
from sysobjects,syscomments
where sysobjects.id = syscomments.id
and sysobjects.type = 'p'
and sysobjects.name = '<proc name>'
You can then parse the resulting record set for the string you are looking for.
I'm sure something like this is avaiable for Oracle.
-
We do it something like this:
create procedure yadayada
(...
...
@pVersion char(2) = NULL)
AS
declare @lVersion numeric(10,2)
SELECT @lVersion=1.01
IF (UPPER(COALESCE(@pVersion,'')) = '-V')
begin
SELECT @lVersion as [SPVersion]
return
end
...
return
This helps us determine within the app if we need to update a proc. All you have to do is send over @pVersion='-V' and it will send back the version and return without executing the rest of the SP. There are other refinements, such as a CRC check, that you can add, but this should get you started, keep you out of the system tables, and not have to worry about encryption. As a bonus, this technique will work just fine in any RDBMS.
-
scporich's solution is much better, plus it keeps you out of the system tables.
Brian
-
Thanks! This works slick and is much better than what I was trying to do.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply