Solved

Stored Procedure Problem

Posted on 2008-06-26
7
165 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
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

840 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