Hi,
I don't know where to post this Question.
Let me begin by telling you what I have.
I have got three tables in one database in sql.Each table is displayed via a datagrid in a web application I am busy with.
I also created a fourth Table for the deleted Items.
I want to create a stored procedure with sql script.
I want the stored procedure to check the data that was deleted from each table and display that deleted data in a fourth data grid in my web application.
Here is the stored Procedure.
Hope someone can help me.
Thanks
CODE
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_DeletedItems')
BEGIN
DROP Procedure sp_DeletedItems
END
GO
CREATE Procedure sp_DeletedItems
@pint_ItemId int,
@pnv_Type nvarchar(12)
AS
IF(@pnv_Type = 'Category')
BEGIN
SELECT d.int_DeleteId, d.nv_CategoryName, d.nv_SubCategoryName, d.ntxt_Question, d.nv_UpdateUser, d.dt_LastUpdate, d.int_CategoryId, c.int_CategoryId, d.int_SubCategoryId, d.int_QuestionId
FROM tblDeletedItem d
INNER JOIN tblCategories c
ON d.int_CategoryId = c.int_CategoryId
ORDER BY d.dt_LastUpdate
END
IF(@pnv_Type = 'Subcategory')
BEGIN
SELECT d.int_DeleteId, d.nv_CategoryName, d.nv_SubCategoryName, d.ntxt_Question, d.nv_UpdateUser, d.dt_LastUpdate, d.int_CategoryId, s.int_SubCategoryId, d.int_SubCategoryId, d.int_QuestionId
FROM tblDeletedItem d
INNER JOIN tblSubCategories s
ON d.int_SubCategoryId = s.int_SubCategoryId
ORDER BY d.dt_LastUpdate
END
IF(@pnv_Type = 'Question')
BEGIN
SELECT d.int_DeleteId, d.nv_CategoryName, d.nv_SubCategoryName, d.ntxt_Question, d.nv_UpdateUser, d.dt_LastUpdate, d.int_CategoryId, q.int_QuestionId, d.int_SubCategoryId, d.int_QuestionId
FROM tblDeletedItem d
INNER JOIN tblAssessmentQuestions q
ON d.int_QuestionId = q.int_QuestionId
ORDER BY d.dt_LastUpdate
END
GO
GRANT EXEC ON sp_DeletedItems TO PUBLIC
GO
11 Dec, 2007 - 12:04 AM
| 