Pls. help me optimize this stored procedure

Hi,

I'm kind of rusty when it comes stored procedures and I was wondering if someone could help me with the following SP. What I need to do is to query the database with a certain parameter (ex. Culture='fr-FR' and MsgKey='NoUpdateFound') and when that returns no rows I want it to default back to Culture 'en-US' with the same MsgKey='NoUpdateFound'). This way our users will always get an error message back in English if their own language is not defined. Currently I'm doing the following,

- Check row count on the parameter passed to sql and if found again do a select returning the values
- else do a select on the SQL with the default parameters.

I'm guessing there is a way to run a SQL and return it if it contains rows otherwise issue another query to return the default.

How can I improve this query. Your help is greatly appreciated. Also, if I do any assignment since 'Message' is defined as text I get the following error message

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or
aggregate expression.

CREATE PROCEDURE GetMessageByCulture
(
@.Culture varchar(25),
@.MsgKey varchar(50)

)
AS
SET NOCOUNT ON;

IF ( (SELECT COUNT(1) FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound') > 0)
SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey
GO

Thank you
M.

Why don't you use resource files with globalization? This functionality is built in; all you have to do is edit an XML file to update the messages, and the message will always be displayed in the default language if the user's culture is not supported.

|||

IF EXISTS (SELECT * FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound')

SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey

I am using * for the first statement because I don't know your columns. Ideally, you just only select one column instead of *.