Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using defined constants in stored procedures

Author  Topic 

poosten
Starting Member

4 Posts

Posted - 2008-04-21 : 16:40:00
I would like to create a file named macro_def.m that contains the following:

define(_HELP,1000)
define(_INSERT,2000)
define(_UPDATE,3000)
define(_DELETE,4000)

define(_retcode,`eval($1)')

Then in any stored procedure that I have I would like to do something like:

CREATE PROCEDURE [dbp].[sp_AddNew]
INCLUDE "macro_def.m"
BEGIN
insert into tablex (name,number) values ("text",_retcode(_INSERT))
END
GO

Questions:
What is the syntax in the SP to inlude a file like macro_def.m ?
Where do I save the macro_def.m file ?
Do I need to include a path in the INCLUDE statement ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-21 : 16:41:21
That isn't possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

poosten
Starting Member

4 Posts

Posted - 2008-04-21 : 22:19:28
Thank you for you quick reply.

However, I find it hard to believe that there is no way to define macros and be able to re-use them in other stored procedures.

The only thing I found sofar is the following link, but no syntax or example on how to implement it in a stored procedure.

http://www.edbarlow.com/macros/macro_4.htm

So, you are saying that when you have n stored procedures, then I would have to copy-paste a list of values into each procedure ?
Then if I add a new value or change a value, I would have to edit each stored procedure ?

I do not have a vast experience with writing Stored Procedures, but something as basic as defaults/globals/macros/constants would seem to be a rather trivial thing to support. I cannot think of any language that does not support this.
Go to Top of Page

poosten
Starting Member

4 Posts

Posted - 2008-04-21 : 22:39:12
I have also found two more sources, but with disadvantages.

http://www.datamanipulation.net/tsqlmacro/tsqlmacrointro.asp
This example leans towards including code rather than using a defined value. I could experiment with this,but I am missing information on to define the macro such as DEBUG in the Enterprise manager database.

The second source I found in the help of Enterprise Manager when I searched for "macro". There is information on creating an Extended stored procedure names 'xp_hello' where you compile a DLL and use it in stored procedures.
The drawback is that I would have to move all the stored procedures to a DLL, just to use some global defined values in each sp. It complicates maintenance because you need a compiler and recompile it each time something changes. Leaving it up to the DBA to maintain the DLL is not really desirable.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-21 : 22:53:18
create table appvalue (keyval varchar(20), data varchar(20))
insert appvalue select 'HELP','1000'
insert appvalue select 'INSERT','2000'
insert appvalue select 'UPDATE','3000'
insert appvalue select 'DELETE','4000'

then in sp
insert into tablex (name,number) select 'text',(select data from appvalue where keyval='INSERT')

You can also do this from a udf
insert into tablex (name,number) select 'text',dbo.GetAppValue('INSERT')
but that can have a big impact on speed but will allow you to code like in a client app language.

There's not usually much call to do what you are asking in t-sql.



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

poosten
Starting Member

4 Posts

Posted - 2008-04-21 : 23:57:14
Thank you for the quick reply.

To use a table to store the constants is perhaps the best alternative.
I actually came across a fairly detailed example a few minutes ago where someone did something quite similar. See:
http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx

The stored procedures using the constants will not be called often.
So the performance penalty should be very small.

Your time and effort is greatly appreciated.
Go to Top of Page
   

- Advertisement -