We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.
It would be great to be able to set a global variable and use it anytime that code is needed.
Any ideas.The way I have accomplished this myself is to use a UDF (user-defined function). This will give you just one place to make the change.
CREATE FUNCTION [dbo].[getPhoneType]
(
)
RETURNS int AS
BEGIN
RETURN 731
END
To use it:
SELECT * FROM myTable WHERE PhoneType = dbo.getPhoneType()
Terri|||Since you've created another db to hold your add-ons, you could build tables to hold those type of values. I usually create a name/value table to hold things like key/value pairs and then just select the correct record from it when I need it.
No comments:
Post a Comment