November 24, 2011 at 3:54 am
hi guys,
please help me with the stored procedure in sqlserver2008...(asp.net)
i have two tables
category and language...with the columns
cat_id(PK),cat_name,lang_id(FK) and lang_id(PK),lang_name...
i need to insert the lang_id to the category table which is a foriegn key....
suppose i have to select the language name from the dropdwn list ,the primary key of the selected language name shuld enter the category table.
whats the stored procedure for the above requirement?plz help me....
November 24, 2011 at 4:03 am
Please show us what you've tried so far and we'll see whether we can help with any specific problems you have. We're not here to do your job for you, though, so we'd like to see that you've made some effort to do this yourself.
John
November 25, 2011 at 4:31 pm
The stored proc code to insert a new category and the lang_id and cat_name is below.
CREATE PROCEDURE uspCategoryInsert (
@LanguageId AS INT
, @CategoryName AS VARCHAR(100)
)
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO category (cat_name, lang_id)
VALUES (@CategoryName, @LanguageId)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrNum
, ERROR_SEVERITY() AS ErrSev
, ERROR_STATE() AS ErrState
, ERROR_PROCEDURE() AS ErrProc
, ERROR_LINE() AS ErrLine
, ERROR_MESSAGE() AS ErrMsg;
ROLLBACK TRANSACTION;
END CATCH;
COMMIT TRANSACTION;
END
GO
November 26, 2011 at 3:11 am
Bill Hansen (11/25/2011)
The stored proc code to insert a new category and the lang_id and cat_name is below.
CREATE PROCEDURE uspCategoryInsert (@LanguageId AS INT
, @CategoryName AS VARCHAR(100)
)
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO category (cat_name, lang_id)
VALUES (@CategoryName, @LanguageId)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrNum
, ERROR_SEVERITY() AS ErrSev
, ERROR_STATE() AS ErrState
, ERROR_PROCEDURE() AS ErrProc
, ERROR_LINE() AS ErrLine
, ERROR_MESSAGE() AS ErrMsg;
ROLLBACK TRANSACTION;
END CATCH;
COMMIT TRANSACTION;
END
GO
the bracketing means that will result in
Msg 3902, Level 16, State 1, Line 9
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
after the error message is selected.
Fix by changing "COMMIT TRANSACTION" to "IF @@TRANCOUNT > 0 COMMIT TRANSACTION"
Tom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply