Solved

Stored Procedure Problem

Posted on 2008-06-26
7
174 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 67
How to structure query with count aggregate 4 64
Union & Crosstab qrys 101! 6 73
T-SQL: Need training video on creating a Clustered Index 2 58
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

751 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