Reuse of Stored Procedure
-
Hi All, I have several stored procedures that need to change depending on what language is selected on the website. Rather then change my backend code, I would like to just dynamically alias the "Processes" column in my stored procedure. My table contains a "Processes", "ProcessesChina" and "ProcessesGerman" column with the appropriate translations. So for Chinese, German, and English I could just use the same stored procedure, but dynamically substitute in the "Processes" column into the select statement like so (this would be the chinese):
SELECT Processes.ProcessId, Processes.ProcessesChina AS Processes
FROM Processes
INNER JOIN Products
ON Processes.ProcessId = Products.ProcessId
GROUP BY Processes.ProcessId, Processes.ProcessesChina
GO
Rather then have multiple SP's what is the best way to do this? I looked into "sp_executesql", but I couldn't figure out if this was the right approach. If it is could someone provide an example using my SP above? If not, what
approach would work?Much thanks for any help. I'm trying to do this the right way to avoid duplication.
DP
-
CASE @RequestedLangage WHEN 'Eng' THEN Processes
WHEN 'Chi' THEN ProcessesChina
WHEN 'Ger' THEN ProcessesGerman
END AS Processes
You could also put that code into a function so that you could reuse it in all you stored procs without recoding the case all the time. Also if you ever need to have another column added to that list you only have one object to update.
-
Thanks for the reply. I assume that I can just embed the "Case" statement right into the SP select line?
If I was to place the code in a function, how would that function be emebbed into the SP select statement?
Thanks!
-
if you embed it into a function you'll just use it very much like a field name...such as:
[owner of function].[functionname(parameters)] as Alias
example might be:
dbo.fnProcess('Eng') as Process
If the phone doesn't ring...It's me.
-
Precisely... just have a look at BOLS for the exact syntax or if you need more help.
-
Hi guys, I'm trying to use the function, but when I embed the CASE statement the syntax fails. Could someone let me know what I'm formatting wrong? Thanks.
CREATE FUNCTION [MyFunction] (@RequestedLangage varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @MySql varchar(50)
CASE @RequestedLangage
WHEN 'En' THEN SET @MySql = 'Processes'
WHEN 'Ch' THEN SET @MySql = 'ProcessesCh'
WHEN 'De' THEN SET @MySql = 'ProcessesDe'
END
RETURN @MySql
END
-
The CASE statement is used with the SELECT command. Just add Select in front of the CASE and you'll be all set. Or rewrite it to use the IF command.
If the phone doesn't ring...It's me.
-
Thanks Journeyman, I replaced the case with an if statement and it worked. However, when I call it in my stored procedure it fails? Code below:
CREATE PROCEDURE [MySp]
@RequestedLangage varchar
AS
SELECT Processes.ProcessId, dbo.MyFunction.(@RequestedLangage) AS Processes
FROM Processes
-
dbo.MyFunction (@RequestedLangage)
Also you need to pass the other 3 columns. That way the FUNCTION will pick the right column to present and return it to the caller. That was the whole point of the function.
-
Hi, I apologize for not understanding, but why would I want to send all 3 columns? I just want to return the 1 column that is the language they are using. So here is my function:
CREATE FUNCTION [MyFunction] (@RequestedLangage varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @MySql varchar(50)
IF @RequestedLangage = 'En' SET @MySql = 'Processes'
IF @RequestedLangage = 'Ch' SET @MySql = 'ProcessesCh'
IF @RequestedLangage = 'De' SET @MySql = 'ProcessesDe'
RETURN @MySql
END
Now if they were in the Chinese site I would send my SP @RequestedLangage = 'Ch' from the application and then feed it to the function, like so:
SELECT Processes.ProcessId, dbo.MyFunction(@RequestedLangage) AS Processes
FROM Processes
INNER JOIN Products
ON Processes.ProcessId = Products.ProcessId
GROUP BY Processes.ProcessId, dbo.MyFunction(@RequestedLangage)
GO
Correct? Thanks for all the help.
-
Because that's not the way it works. All that'll do is return the string 'ProcessWhatever'
You want it to return the actual translation for EACH line. So you need to send all three versions to the function along with the language to return. That way the function picks the right column to display and you can use that function over and over again for any table/sp.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply