June 9, 2009 at 7:09 pm
For the life of me I can't figure out why I have this error. here is my Stored Procedure.
USE [KraftInStoreAgenda]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:mc
-- Create date: 09 Jun 2009
-- Description:Allows a user to push an Agenda, AgendaActivities, and AgendaPPG from a template
-- =============================================
Create PROCEDURE [dbo].[spRAMPushTemplateToAgenda]
@AgendaID int = null,
@AgendaName nvarchar(50),
@AgendaStartWeek datetime,
@BannerNo int,
@BusinessUnitID int = null,
@UpdateLogin nvarchar(100),
@TemplateID int,
@AgendaActivityID int =null,
@ActivityID int,
@ThemeID int,
@DisplayTypeID int = null,
@AgendaActivityPPGID int,
@PPG nvarchar(255),
@SRP nvarchar(50) = null,
@PctProfit real = null,
@StartWeek datetime,
@EndWeek datetime,
@DisplayID nvarchar(50) = null,
@RepBuildsDisplay bit=0
AS
BEGIN
SET NOCOUNT ON;
if @BusinessUnitID is null set @BusinessUnitID = 0
DECLARE @AgendaStartWeekModified datetime
DECLARE @AgendaStartDay nvarchar(9)
DECLARE @NewAgendaID int
SET @AgendaStartDay = datename(dw, @AgendaStartWeek)
SELECT @AgendaStartWeekModified =
CASE @AgendaStartDay
WHEN 'Monday' THEN dateadd(day, -1, @AgendaStartWeek)
WHEN 'Tuesday' THEN dateadd(day, -2, @AgendaStartWeek)
WHEN 'Wednesday' THEN dateadd(day, -3, @AgendaStartWeek)
WHEN 'Thursday' THEN dateadd(day, -4, @AgendaStartWeek)
WHEN 'Friday' THEN dateadd(day, -5, @AgendaStartWeek)
WHEN 'Saturday' THEN dateadd(day, 1, @AgendaStartWeek)
ELSE @AgendaStartWeek
END
BEGIN
--sets tblTemplate fields to variables
SELECT @BusinessUnitID=BusinessUnitID
FROM tblRAMTemplates
WHERE (TemplateID = @TemplateID)
--Inserts variable into New Agenda
INSERT INTO tblRAMAgendas
(AgendaName, AgendaStartWeek, Banner_No, BusinessUnitID, UpdateLogin)
VALUES (@AgendaName,@AgendaStartWeekModified,@BannerNo,@BusinessUnitID,@UpdateLogin)
--Inserts into New Agenda Activities All Activities from Template
INSERT INTO tblRAMAgendaActivities
(AgendaID, ActivityID, Comments, UpdateLogin, ThemeID, DisplayTypeID)
VALUES SELECT @AgendaID AS ID, tblRAMTemplateActivities.ActivityID, tblRAMTemplateActivities.Comments, @UpdateLogin AS login,
tblRAMTemplateActivities.ThemeID, tblRAMTemplateActivities.DisplayTypeID
FROM tblRAMTemplates INNER JOIN
tblRAMTemplateActivities ON tblRAMTemplates.TemplateID = tblRAMTemplateActivities.TemplateID
WHERE (tblRAMTemplates.TemplateID = @TemplateID)
--Inserts Into New Agenda All PPGs From Template
INSERT INTO tblRAMAgendaActivityPPGs
(AgendaActivityID, PPG, SRP, PctProfit, StartWeek, EndWeek, LastUpdate, UpdateLogin, RepBuildsDisplay, Comments)
VALUES SELECT @activityID AS ID, tblRAMTemplateActivityPPGs.PPG, tblRAMTemplateActivityPPGs.SRP, tblRAMTemplateActivityPPGs.PctProfit,
tblRAMTemplateActivityPPGs.StartWeek, tblRAMTemplateActivityPPGs.EndWeek, tblRAMTemplateActivityPPGs.LastUpdate,@UpdateLogin,
@RepBuildsDisplay AS build, tblRAMTemplateActivityPPGs.Comments
FROM tblRAMTemplateActivityPPGs INNER JOIN
tblRAMTemplateActivities ON tblRAMTemplateActivityPPGs.TemplateActivityID = tblRAMTemplateActivities.TemplateActivityID
WHERE (tblRAMTemplateActivities.TemplateID = @TemplateID)
END
Here are my errors
Msg 156, Level 15, State 1, Procedure spRAMPushTemplateToAgenda, Line 67
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure spRAMPushTemplateToAgenda, Line 76
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure spRAMPushTemplateToAgenda, Line 83
Incorrect syntax near 'END'.
June 9, 2009 at 7:18 pm
VALUES SELECT
it's one or the other, never both...
you either insert into (columnlist) values(val1,val2,etc)
OR
insert into (columnlist) select val1,val2,etc from somesource.
an extra pair of eyes always helps in these situations..trees and forests and all that.
Lowell
June 9, 2009 at 7:59 pm
Wow that was a fast reply! You are up on these forums pretty late. So How you you go about Inserting A table of let's say five rows into a table using a select statement but you also want to insert a column that's set to a variable? Would you just use an update statement after your Insert Into?
June 9, 2009 at 8:24 pm
Not much choice... that's exactly what you need to do.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 9, 2009 at 9:37 pm
maybe something is missing in your code snippet, but you declare a variables like @activityID and @UpdateLogin, never assign them a value, but then use then to insert into a table.
i thought at first you just needed to remove the word VALUES in front of the SELECT, which would make it syntactically correct, but maybe you are missing an additional join, so you can get all 5 ID's and UpdateLogin values via the SQL instead of planning to assign it to a variable?
Lowell
June 10, 2009 at 7:06 am
I got that from another forum removing the "values" keyword it got rid of the errors. The variables are passed from an access database the calls the SP. I am going to test this now that's it's error free and see if I can get away with using variables within a insert into (select var,col, col) statement.
June 10, 2009 at 7:09 am
you won't have any problem with the SELECT @variable, 'Any constant',col1,col2 from SomeTable Format; it's very common to need to do that.
Lowell
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply