Solved

Stored Procedure Problem

Posted on 2008-06-26
7
180 Views
Last Modified: 2011-10-19
My table sturcuture is below...

the coloums names are:
CatID, LabgID, EnglishID, ParentCategoryID, CategoryName, Show, OrderBy, PopRating

I want to write a stored procedure to add cateogies if they are a different language...
The english information (catID: 22,108) is present for all the categories ( I don't list them here since there are too many...)

the parameters to input should be the englishID of the Category and the CategoryName.

I wrote a procedure but it doesn't seem to be working... (Posted under the table)

Any help would be great!
thanks!
318	FRE	1	0	Automobile	               0	1	NULL
327	FRE	2	318	Accessoires	               0	NULL	NULL
328	FRE	6	0	Éducation	                        0	NULL	NULL
329	FRE	3	0	Vêtements Et Accessoires         0	NULL	NULL
22	ENG	6	0	Education	                        0	5	NULL
108	ENG	69	22	Academic	                        0	1        NULL
 
 
ALTER PROCEDURE [dbo].[usp_EnterLangFree]
	-- Add the parameters for the stored procedure here
	@LangId char(3), 
	@EnglishID int,
	@CategoryName nvarchar(MAX)
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @ParentCategoyID int
 
	SELECT DISTINCT @ParentCategoyID=ParentCategoyID FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = @LangId
	
	IF (@ParentCategoyID = null) 
		BEGIN
		Return 1;
		DECLARE @HoldID1 int
		DECLARE @HoldID2 int
 
		SELECT DISTINCT @HoldID1=ParentCategoyID FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = 'ENG'
		
		SELECT DISTINCT @HoldID2=EnglishID FROM Categories WHERE CategoryID=@HoldID1 AND LanguageID = 'ENG'
		
		SELECT DISTINCT @ParentCategoyID=CategoryID FROM Categories WHERE EnglishID=@HoldID2 AND LanguageID = @LangId
	
 
	END
 
 
	IF EXISTS (
		SELECT 1
		FROM dbo.Categories
		WHERE EnglishID = @EnglishID
		AND LanguageID = @LangId
		)
		BEGIN
		--Category already addded. Do an update.
			UPDATE Categories SET CategoryName=@CategoryName WHERE EnglishID=@EnglishID AND LanguageID = @LangId
	END
	ELSE
		BEGIN
		-- Insert into Users Table
		INSERT INTO Categories ( 
			LanguageID, 
			EnglishID,
			ParentCategoyID, 
			CategoryName, 
			Show
		)
		VALUES (
			@LangId, 
			@EnglishID,
			@ParentCategoyID,	
			@CategoryName, 
			'0'
		)
    END
END

Open in new window

0
Comment
Question by:copyPasteGhost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21882994
You're not telling me in what way it's failing.  Still, here are a few pointers / improvements.  If these don't get it, please tell me a little bit more about where it's failing.
ALTER PROCEDURE [dbo].[usp_EnterLangFree]
        -- Add the parameters for the stored procedure here
        @LangId char(3), 
        @EnglishID int,
        @CategoryName nvarchar(MAX)
 
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @ParentCategoyID int
 
        SELECT @ParentCategoyID= Max(ParentCategoyID) FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = @LangId
--                            ^^^  I Changed Distinct to MAX b/c MAX is guaranteed to return a single value
        
        IF (@ParentCategoyID IS null) 
--                           ^^^ To compare to NULL, use IS, not =
                BEGIN
                Return 1;
                DECLARE @HoldID1 int
                DECLARE @HoldID2 int
 
                SELECT @HoldID1=MAX(ParentCategoyID) FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = 'ENG'
                
                SELECT @HoldID2=Max(EnglishID) FROM Categories WHERE CategoryID=@HoldID1 AND LanguageID = 'ENG'
                
                SELECT @ParentCategoyID=Max(CategoryID) FROM Categories WHERE EnglishID=@HoldID2 AND LanguageID = @LangId
        
 
        END
 
 
        IF EXISTS (
                SELECT 1
                FROM dbo.Categories
                WHERE EnglishID = @EnglishID
                AND LanguageID = @LangId
                )
                BEGIN
                --Category already addded. Do an update.
                        UPDATE Categories SET CategoryName=@CategoryName WHERE EnglishID=@EnglishID AND LanguageID = @LangId
        END
        ELSE
                BEGIN
                -- Insert into Users Table
                INSERT INTO Categories ( 
                        LanguageID, 
                        EnglishID,
                        ParentCategoyID, 
                        CategoryName, 
                        Show
                )
                VALUES (
                        @LangId, 
                        @EnglishID,
                        @ParentCategoyID,       
                        @CategoryName, 
                        '0'
                )
    END
END

Open in new window

0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21901402
Thank you,

I have tried your sugesstions and I am getting the same problem..

The problem was @ParentCategoyID was always ending up null..


I have now isolated the problem to the @langID parameter. for some reason it's empty, all the time.

Any ideas why? I'm passing it in my application, also if i run tests from within SQL management studio as well as visual studio I'm still getting the empty value.

Thanks,
Ghost
0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21901411
I have succeed in getting it working by doing this..


As you can see it's hardcoded to 'FRE'. I would like it to be dynamic.

Thanks
ALTER PROCEDURE [dbo].[usp_EnterLangFree]
	-- Add the parameters for the stored procedure here
	@LangId nvarchar(MAX), 
	@EnglishID int,
	@CategoryName nvarchar(MAX)
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @ParentCategoyID int;
	DECLARE @HoldID1 int;
    DECLARE @HoldID2 int;
	
	SELECT @HoldID1=MAX(ParentCategoyID) FROM Categories WHERE EnglishID=@EnglishID AND LanguageID = 'ENG'
	
	SELECT @HoldID2=Max(EnglishID) FROM Categories WHERE CategoryID=@HoldID1 AND LanguageID = 'ENG'
	
	SELECT @ParentCategoyID=Max(CategoryID) FROM Categories WHERE EnglishID=@HoldID2 AND LanguageID = 'FRE'
 
	IF EXISTS (
		SELECT 1
		FROM dbo.Categories
		WHERE EnglishID = @EnglishID
		AND LanguageID = 'FRE'
		)
		BEGIN
		--Category already addded. Do an update.
			UPDATE Categories SET CategoryName=@CategoryName WHERE EnglishID=@EnglishID AND LanguageID = 'FRE'
	END
	ELSE
		BEGIN
		-- Insert into Users Table
		INSERT INTO Categories ( 
			LanguageID, 
			EnglishID,
			ParentCategoyID, 
			CategoryName, 
			Show
		)
		VALUES (
			'FRE', 
			@EnglishID,
			@ParentCategoyID,	
			@CategoryName, 
			'0'
		)
    END
END

Open in new window

0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 21901479
>>if i run tests from within SQL management studio as well as visual studio I'm still getting the empty value.

So, if in Management Studio you say:

Exec usp_EnterLangFree 'Eng', 22, 108

you're getting behavior as though @LangID is NULL?  that should not be ...


>>I'm passing it in my application

Could you post that part of your app code?
0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21901772
Here you go.


As you can see the value is getting passed....but then just idissapears....

thanks.
bool passed = DBobj.runStoredProcInsert("usp_EnterLangFree", new string[,] { 
                { "@LangId", ddlLanguage.SelectedValue },
                { "@EnglishID", txtEnglishCode.Text },
                { "@CategoryName",txtLangFree.Text}});
 
 
public bool runStoredProcInsert(string procName, string[,] paramList) {
            try {
                openDB();
                SqlCommand cmd = new SqlCommand(procName, con);
 
                cmd.CommandType = CommandType.StoredProcedure;
 
                if (paramList != null) {
                    for (int i = 0; i < (paramList.Length / 2); i++) {
                        cmd.Parameters.Add(new SqlParameter(paramList[i, 0], paramList[i, 1]));
                    }
                }
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                closeDB();
            }
            catch (Exception ex) {
                throw new Exception(ex.ToString(), ex);
                return false;
            }
            return true;
        }

Open in new window

enterlangfree.JPG
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 21902047
that looks good ... though if we eliminate the DB-side problems I'll look harder at it.

What happens in management studio if you


Exec usp_EnterLangFree 'Eng', 22, 108

(with the right parameters put in instead of mine)
0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 21902143
wow...it works....

both in my application and in management studio.

Thanks so much!
0

Featured Post

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question