Solved

Stored Procedure Problem

Posted on 2008-06-26
7
152 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Salary Amount Format 13 56
Select distinct 25 60
SQL for monthly balance change 15 61
Oracle - Query Insert and Update multiple tables 5 41
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now