Stored procedure for lang

Question

I want to get data for selected langugage but this query gets all data...

ALTER PROCEDURE [dbo].[sp_GetAbout]
AS
    SELECT 
        About.[AboutID],
        Languages.Name as LangCode,
        [About].[AboutUs], [About].[AboutUs], 
        [About].[Mission], [About].[Vision], [About].[AboutPhoto],
        CASE 
           WHEN About.IsActive = 'True' 
              THEN 'Active' 
              ELSE 'Passive'
        END AS Statu
    FROM 
        [dbo].[About]
    INNER JOIN 
        Languages on About.LangCode = Languages.Code
    WHERE
        About.Statu = 'True' 
    ORDER BY
        Statu, Languages.Name

@sr28 he wants to filter by `LangCode`. Most likely passing the language code via a parameter to the stored procedure and then adjusting the `WHERE` clause — Jonathon Ogden, Aug 18 '16 at 09:36
in about table i have 2 langugaes data. my web multilanguage so when i selected english, it should have to get only english data. but i think this query is not true? — Shqiptar, Aug 18 '16 at 09:36
@Shqiptar you need to filter by `LangCode`, so look at adding a parameter to your stored procedure so that when you call it, you can specify the language code as mentioned earlier. — Jonathon Ogden, Aug 18 '16 at 09:38
yes im trying `About.AboutID = About.LangCode` but it was error.. pls help me. — Shqiptar, Aug 18 '16 at 09:39
@Shqiptar - the point of adding the parameter is to allow you to filter as per your request. That means at the point of calling the stored procedure you actually need to supply something for that parameter. If you don't, which you're currently not, you will get an error saying as much. So presumably in your c# code somewhere you are calling this. You now need to alter that to supply a value for your new parameter. — sr28, Aug 18 '16 at 09:48
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 18 '16 at 10:01
Hi Shqiptar; if you have extra information to add, for example in response to a comment, you are encouraged to [edit] your question to include the new information; that makes it easiest to read, and most useful to other users of the site. — Vince Bowdren, Aug 22 '16 at 10:13

Ara Asryan · Answer 1 · 2016-08-18T09:49:44.377

You should pass input parameter for Language name. For example

ALTER Proc [dbo].[sp_GetAbout]
   @LangName nvarchar(3) 
    as
    SELECT About.[AboutID],

         Languages.Name as LangCode

          ,[About].[AboutUs],[About].[AboutUs],[About].[Mission],[About].[Vision],[About].[AboutPhoto],


          case when About.IsActive='True' then 'Active' else 'Passive'end as Statu


      FROM [dbo].[About]
      INNER JOIN Languages on About.LangCode = Languages.Code
      Where About.Statu='True' and LangCode = @LangName Order by Statu,Languages.Name

Then call using

EXEC [dbo].[sp_GetAbout] @LangName = 'ENG'

Also note to change the parameter type for your case. Because You didn't provided how you store languages.

Konstantin Ershov · Answer 2 · 2016-08-18T10:16:13.583

0

You have to add parameter to your procedure.

ALTER Proc [dbo].[sp_GetAbout] @Lang nvarchar(30) AS
SELECT About.[AboutID],
       Languages.Name AS LangCode ,
       [About].[AboutUs],
       [About].[AboutUs],
       [About].[Mission],
       [About].[Vision],
       [About].[AboutPhoto],
       CASE
           WHEN About.IsActive='True' THEN 'Active'
           ELSE 'Passive'
       END AS Statu
FROM [dbo].[About]
INNER JOIN Languages ON About.LangCode = Languages.Code
WHERE About.Statu='True'
  AND LangCode = @Lang
ORDER BY Statu,
         Languages.Name

Then you should call for it like

EXEC [dbo].[sp_GetAbout] @Lang = 'en-GB'

And for your C# function it's

public static DataTable GetAbout()
        {
            Fonksiyon f = new Fonksiyon();
            SqlConnection mycon = f.Baglanti;
            SqlDataAdapter da = new SqlDataAdapter("sp_GetAbout", mycon);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.Add("@Lang", SqlDbType.NVarChar).Value = "en-GB";
            DataTable dt = new DataTable();
            da.Fill(dt);
            da.Dispose();
            KayitSayisi = dt.Rows.Count;
            return dt;
        }

edited Aug 18 '16 at 10:16

answered Aug 18 '16 at 09:39

Konstantin Ershov

730
4
13

Error: `Procedure or function 'sp_GetAbout' expects parameter '@Lang', which was not supplied.` – Shqiptar Aug 18 '16 at 09:41
1

cmon, you should pass parameter as error message says. added example to answer – Konstantin Ershov Aug 18 '16 at 09:43
because you are trying to to execute the procedure without passing a parameter value. – Konstantin Ershov Aug 18 '16 at 09:51
add it after da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("@Lang", SqlDbType.VarChar).Value = "English"; – Konstantin Ershov Aug 18 '16 at 09:53
@Shqiptar just add the parameter to your command as shown above – Konstantin Ershov Aug 18 '16 at 10:01
http://stackoverflow.com/questions/3528305/how-to-use-a-dataadapter-with-stored-procedure-and-parameter second answer – Konstantin Ershov Aug 18 '16 at 10:08

Stored procedure for lang

2 Answers2