need advice on this SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need advice on this SP

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-03-16 : 09:34:55

here is the scoop.

I have a table of projects. I have a second table of ProjectPage. The ProjectPage table is tied to the projects table by a project_id field. What I need to do is add records to the pages table. In that ProjectPage table there is a prioriry id field. So when I do an insert. I need to first see if there are any pages that are tied to the project_id.. If so I have to find the highest priority value for the project_id and add one and then insert the record into the pages table. My code is not working. The Null Value seems priority value seems to create issues too.

ALTER PROCEDURE dbo.InsertPage
(
@project_ID as int,
@page_title AS VARCHAR(100),
@page_template_ID int,
@content_area AS VARCHAR(2000),
@main_image AS VARCHAR(100),
@second_image AS VARCHAR(100),
@modified_by_ID int
)
AS
INSERT INTO ProjectPage
(priority, project_ID, page_title, date_modified, modified_by_ID, page_template_ID, content_area, main_image, second_image)
SELECT MAX(priority) + 1, @project_ID, @page_title, GETDATE(), @modified_by_ID, @page_template_ID, @content_area, @main_image, @second_image
FROM ProjectPage AS ProjectPage_1
GROUP BY project_ID
HAVING (project_ID = @project_ID)

ProjectPage Table
page_ID int Unchecked
project_ID int Unchecked
page_title nvarchar(50) Checked
date_modified smalldatetime Checked
modified_by_ID int Unchecked
priority int Checked
page_template_ID int Unchecked
content_area nvarchar(MAX) Checked
main_image nvarchar(50) Checked
second_image nvarchar(50) Checked

Project Table

project_ID int Unchecked
project_Desc nvarchar(100) Checked
project_priority int Unchecked
userid int Checked
lastModified datetime Checked

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 09:49:47

[code]
SELECT MAX(priority) + 1, @project_ID, @page_title, GETDATE(), @modified_by_ID, @page_template_ID, @content_area, @main_image, @second_image
FROM ProjectPage AS ProjectPage_1
GROUP BY project_ID
HAVING (project_ID = @project_ID)
where project_ID = @project_ID
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:55:16

[code]ALTER PROCEDURE dbo.InsertPage
(
@Project_ID INT,
@Page_Title VARCHAR(100),
@Page_Template_ID INT,
@Content_Area VARCHAR(2000),
@Main_Image VARCHAR(100),
@Second_Image VARCHAR(100),
@Modified_By_ID INT
)
AS

SET NOCOUNT ON

DECLARE @Priority INT

SELECT @Priority = MAX(Priority) + 1
FROM ProjectPage
WHERE Project_ID = @Project_ID

IF @Priority IS NULL
SET @Priority = 1 -- Change this to any value for lowest initial priority

INSERT ProjectPage
(
Priority,
Project_ID,
Page_Title,
Date_Modified,
Modified_By_ID,
Page_Template_ID,
Content_Area,
Main_Image,
Second_Image
)
VALUES (
@Priority,
@Project_ID,
@Page_title,
CURRENT_TIMESTAMP,
@Modified_By_ID,
@Page_Template_ID,
@Content_Area,
@Main_Image,
@Second_Image
)[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:55:52

khtan, what if this insert is a new project?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 09:59:05