Need help improving the following stored procedure

[maflatoun]

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 WHERE Culture=@Culture
AND MsgKey='NoUpdateFound') > 0)
SELECT Message FROM ProductUpdateMsg WHERE Culture=@Culture AND
MsgKey=@MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' AND
MsgKey=@MsgKey
GO

Thank you
M.

 

[Michel Walsh]

I use an outer join:


SELECT Nz(MAX(Nz( b.wantedMessage,
a.wantedMessage )),
"No message for this error.")
FROM tableUS AS a
LEFT JOIN (SELECT *
FROM tableOther
WHERE MsgKey=wantedMessage
AND culture= wantedCulture) AS b
ON a.MsgKey=b.MsgKey



If the message does not exist in the other culture, in tableOther, but exist
in tableUS, the tableUS message will be used. If the message exist in
tableOther, and it tableUS, the one from tableOther will be returned. If the
message does not exist in tableUS, "No message for this error." is returned
(even if the message exists, or not, in tableOther).

Since it seems you are using MS SQL Server, use COALESCE (two places)
instead of Nz. You can also use a full outer join, rather than the proposed
simple outer join, so that if the message exist in tableOther but not in
tableUS, a foreign language, rather than "No message for this error." will
be returned, which MAY, in definitive, be more instructive (of 2 problems,
the initial one reported, and the problem with the tableUS missing message).



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

.... should have added

WHERE a.MsgKey=wantedMessage

at the end.


Vanderghast, Access MVP