Need to query an SP to determine its version number

Hi
I have a StoredProc which returns data to a sheet in an Excel file in a library on a Sharepoint.
I would like some method for the Excel file to determine if it is running the latest version of the StoredProc, the purpose of this is for when a user downloads a copy of the StoredProc to their local machine, so it may be the case that at some time they may not be using the latest version of the file since they are not accessing it from the Sharepoint.

As a side note: When changes are made to the StoredProc (eg; if a column is added in the middle of the existing columns), for some reason the column is added at the end of the returned Excel table as opposed to where the StoredProc advises where it should be.
If I remove the query and re-add it to the Excel, then the correct column order is preserved.

Anyway, I need the StoredProc to be updated in some way that when it executes with a certain flag/parameter set (eg; @VerInfo) then it returns a line of text advising the Version Info of the StoredProc.
Just not too sure of the best method to do this or whether you guys have some better suggestions.
Thanks
Fergal.
fjkilkenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

armchair_scouseCommented:
Hi Fergal,

You could keep a version number just as a variable in your stored procedure, e.g. @SP_VERSION_NO, and add some code that checks your @VerInfo parameter; if the @VerInfo parameter is set, then return your @SP_VERSION_NO variable.

You could also keep a table of your stored procedures and the up-to-date version numbers in a table, and then compare the version number in your stored procedure with the value in the table, and report back if the user is not using to most up-to-date version.

You could also potentially use extended properties, though I have no experience of these:  however, this link might be worth a look: Managing Extended Properties.

Hope this helps.
0
ste5anSenior DeveloperCommented:
when a user downloads a copy of the StoredProc to their local machine

Your users are running their own SQL Servers? What does "download a procedure" mean exactly?

When changes are made to the StoredProc (eg; if a column is added in the middle of the existing columns), for some reason the column is added at the end of the returned Excel table as opposed to where the StoredProc advises where it should be.  If I remove the query and re-add it to the Excel, then the correct column order is preserved.

D'oh? Sounds like your using the asterisk where you shouldn't.

Anyway, I need the StoredProc to be updated in some way that when it executes with a certain flag/parameter set (eg; @VerInfo) then it returns a line of text advising the Version Info of the StoredProc. Just not too sure of the best method to do this or whether you guys have some better suggestions.

It should not be possible to "download" it in the first place.. How do you handle dependcies to other database objects? It sounds like you need a more general deployment scenario.
0
fjkilkenAuthor Commented:
hi ste5an
Apologies - I had mean to type "when a user downloads a copy of the Excel file to their local machine"
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

fjkilkenAuthor Commented:
hi armchair_scouse
extended properties do look interesting alright, I have run some code and was able to create some extended properties for my SP

EXEC sys.sp_addextendedproperty  
         @name = N'CurrentVersion',
         @value = N'1.0',  
         @level0type = N'SCHEMA',  @level0name = 'dbo',  
         @level1type = N'PROCEDURE', @level1name = 'my_stored_procedure';

Open in new window


This nearly gets me what I need, however, I'd like to add another property to the SP - ie; a small description of the SP, I simply tried adding the line:
         @level2type = N'DESCRIPTION',  @level2name = 'Updated stored proc to add various fields';
but this failed "An invalid parameter or option was specified for procedure 'sp_addextendedproperty'."

Any idea of how I would add a DESCRIPTION field to the extended properties for this SP?
thanks
0
Scott PletcherSenior DBACommented:
EXEC sys.sp_addextendedproperty  
         @name = N'DESCRIPTION',
         @value = N'<Your description goes here.>',  
         @level0type = N'SCHEMA',  @level0name = 'dbo',  
         @level1type = N'PROCEDURE', @level1name = 'my_stored_procedure';
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fjkilkenAuthor Commented:
thanks guys - the extended properties aspect is very useful!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.