SQL Stored Procedures and append queries

artsapimp posted this at 16:16 — 11th September 2003.

Joined: Apr 2000

I am attempting to create the following stored procedure. I need to submit to the database more fields than there are in the embedded SELECT statement.

I need to insert AgentID, Agent, avgScore, CategoryID, Month, Year.

CategoryID, Month, and Year are all variables declared above. Where would I place them to insert the variable values into the database while inserting the remaining fields from the SELECT statement?

CREATE PROCEDURE [dbo].[sp_Testing]
	@From		datetime,
	@To		datetime,
	@CategoryID	int,
	@Month	varchar(50),
	@Year		int
AS

INSERT INTO at_Test
	(AgentID, Agent, avgScore, CategoryID, Month, Year)
		(
		SELECT DISTINCT TOP 100 PERCENT AgentID, Agent, AVG(Score) AS avgScore
		FROM         dbo.vw_SearchResults
		WHERE     (RecordTime BETWEEN @From AND @To)
			AND (DepartmentID = 2)
			AND ( DisabledDate IS NULL)
			AND (CategoryID = @CategoryID)
		GROUP BY AgentID, Agent
		)
GO

Thanks for any help.

Free Math Test
Fun Math Games

m3rajk posted this at 18:46 — 16th September 2003.

They have: 461 posts

Joined: Jul 2003

are you INSERTING into a new table? or searching on two tables? i'm not quite clear on that.

if you're trying to sleect from two tables, have you thought about using join?

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

Mark Hensler posted this at 01:05 — 18th September 2003.

He has: 4,048 posts

Joined: Aug 2000

You can use static values in SELECT queries. I've used this method in MySQL when moving data from one table to another.

SELECT field1, field2, 'contant value' FROM table1

Now when it comes to stored procs, I have no experience there. But I would assume the theory would be the same...

SELECT DISTINCT TOP 100 PERCENT AgentID, Agent, AVG(Score) AS avgScore, CategoryID, @Month, @Year

PS - CategoryID is also a field (not just a @Var), so you can use that.

hmm... perhaps: MONTH(RecordTime), YEAR(RecordTime) ??

Mark Hensler
If there is no answer on Google, then there is no question.

artsapimp posted this at 02:07 — 18th September 2003.

They have: 330 posts

Joined: Apr 2000

Thank you.

I did find the answer and Mark you are very close to it. I originally did exactly what you suggested but still generated errors about names not being specified. I was forced to give each field a name where it would usually default to Expr1 like @Month AS myMonth.

Thanks again for your help.

Free Math Test
Fun Math Games