Error on Stored procedure / Variable declaration

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)

Error on Stored procedure / Variable declaration

Author

Topic

vgarzon
Starting Member

11 Posts

Posted - 2008-09-30 : 18:49:55

Hi, I'm creating a stored procedure and I'm having troubles with it's variables declaration or with it's general syntax itself.
I'm kind of new with creating stored procedures (have only created very simple ones so far) so any help will be welcome as I may have made many mistakes.

My procedure's purpose is to fill a table that is a kind of schedule for some agents (CIAs). It must be filled with the same information weekly.

Checking the syntax, I get the following error messages:

Msg 134, Level 15, State 1, Procedure fill_next_week_agenda, Line 23
The variable name '@cia_nt' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 156, Level 15, State 1, Procedure fill_next_week_agenda, Line 48
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure fill_next_week_agenda, Line 48
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@counter_week_days".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@cia_nt".
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@frame_number".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@counter_week_days".
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@cia_nt".

My code is:

USE DB_LA_CMD;
GO

CREATE PROCEDURE dbo.fill_next_week_agenda
AS
-- ********************************************************************
-- *** The fill_next_week_agenda procedure checks the Test_CIA_LIST ***
-- *** table and fills the Test_CIA_AVAILABILITY table with all the ***
-- *** active CIAs availability frames for the following week ***
-- ********************************************************************

-- ***** Variables declaration *****
DECLARE @cia_nt VARCHAR(30),
@starting_hour smalldatetime,
@finishing_hour smalldatetime,
@counter_week_days tinyint,
@counter_top_day tinyint,
@frame_number tinyint,
@calculated_start smalldatetime,
@calculated_end smalldatetime
-- ***** miscellaneous calculations *****

-- ***** Availability table filling *****
DECLARE CIADATA CURSOR
FOR
SELECT cia_nt, RIGHT(starting_hour,6) AS starts_at, RIGHT(finishing_hour,6) AS ends_at FROM dbo.Test_CIA_LIST WHERE status = 'Active'

OPEN CIADATA
FETCH CIADATA INTO @cia_nt, @starting_hour, @finishing_hour
WHILE (@@FETCH_STATUS = 0)
-- CIA_NT WHILE LOOP BEGINS
BEGIN
-- Setting when to begin with the CIA_AVAILABILITY table filling
SET @counter_week_days = 0 -- 0 if the scheduling starts the same day
-- 7 if the scheduling starts the next week ... and so on
-- It defines the day to start= today (or esecution day) + @couter_week_days
-- Setting the number of days that the procedure will fill
SET @counter_top_day = counter_week_days + 5 -- 5 if it starts on a monday and want to fill untill friday
-- It defines the number of days that the procedure will fill
WHILE (@counter_week_days < @counter_top_day)-- From monday to friday
-- WEEKDAYS LOOP BEGINS
BEGIN
SET @frame_number=0
WHILE ((DATEADD(mi, (20*@frame_number), @starting_hour)) < @finishing_hour)
-- FRAMES LOOP BEGINS
BEGIN
-- Calculates the start of frame
SET @calculated_start = SELECT CONVERT (smalldatetime,(CONVERT(varchar,DATEPART(yyyy, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(mm, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(dd, DATEADD(dd,@counter_week_days, GETDATE())))+' '+CONVERT(varchar,RIGHT(DATEADD(mi,(20*@frame_number),starting_hour),6)))) FROM dbo.Test_CIA_LIST WHERE CIA_NT=@cia_nt
GO
-- Calculates the end of frame
SET @calculated_end = SELECT CONVERT (smalldatetime,(CONVERT(varchar,DATEPART(yyyy, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(mm, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(dd, DATEADD(dd,@counter_week_days, GETDATE())))+' '+CONVERT(varchar,RIGHT(DATEADD(mi,((20*@frame_number)+20),starting_hour),6)))) FROM dbo.Test_CIA_LIST WHERE CIA_NT=@cia_nt
GO
-- Inserts an available frame for the specified CIA, starting and ending on the calculated dates
INSERT INTO dbo.Test_CIA_AVAILABILITY (CIA_NT, FrameStart, FrameEnd, Available, SetupFactor, ChangeFactor) VALUES (@cia_nt, @calculated_start, @calculated_end,'Y', 0, 0)

SET @frame_number=@frame_number+1
END
-- FRAMES LOOP ENDS
SET @counter_week_days=@counter_week_days+1
END
-- WEEKDAYS LOOP ENDS
FETCH CIADATA INTO @cia_nt, @starting_hour, @finishing_hour
END
-- CIA_NT SETUPS WHILE LOOP ENDS
CLOSE CIADATA
DEALLOCATE CIADATA
GO

Thanks in advance.

Cheers,
Victor

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-30 : 19:47:00

Because you are using GO in between which terminates the scope of variables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 19:47:50

Never use GO inside the body of a stored procedure, except perhaps inside dynamic SQL (but I still say never).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vgarzon
Starting Member

11 Posts

Posted - 2008-09-30 : 21:07:33

Thanks guys but I removed the GO and still got some errors regarding the select clauses...

I tested them separately and they work so I don't know what's wrong

Please help me.

Thanks in advance,

Victor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 23:56:19

quote:
Originally posted by vgarzon

Thanks guys but I removed the GO and still got some errors regarding the select clauses...

I tested them separately and they work so I don't know what's wrong

Please help me.

Thanks in advance,

Victor

what are errors? same as ones posted initially?

vgarzon
Starting Member

11 Posts

Posted - 2008-10-01 : 09:27:59

The errors I'm still getting are :

Msg 156, Level 15, State 1, Procedure fill_next_week_agenda, Line 47
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure fill_next_week_agenda, Line 50
Incorrect syntax near the keyword 'SELECT'.

But I can't find any mistakes in the SELECT clauses...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-01 : 09:33:21

it's where you're setting the variables...

SET @calculated_start = SELECT....

either do

set @variable = 'X'

or

select @variable = 'X'

or

set @variable = (select 'X'...)

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:35:43

This is wrong

SET @VAR = SELECT CONVERT(smalldatetime, ...

This is right

SELECT @Var = CONVERT(smalldatetime, ...

E 12°55'05.63"
N 56°04'39.26"

CVDpr
Starting Member

41 Posts

Posted - 2008-10-01 : 09:37:54

quote:
Originally posted by vgarzon

The errors I'm still getting are :

Msg 156, Level 15, State 1, Procedure fill_next_week_agenda, Line 47
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure fill_next_week_agenda, Line 50
Incorrect syntax near the keyword 'SELECT'.

But I can't find any mistakes in the SELECT clauses...

Put a ( before and when finishing the select.
= (select........)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:44:47

Why are you using a CURSOR?
I think you can write something set-based like similar to this suggestion

INSERT		dbo.Test_CIA_AVAILABILITY
		(
			CIA_NT,
			FrameStart,
			FrameEnd,
			Available,
			SetupFactor,
			ChangeFactor
		)
SELECT		@cia_nt,
		DATEADD(MINUTE, 1440 * d.theDay + 20 * v.Number, @starting_hour),
		DATEADD(MINUTE, 1440 * d.theDay + 20 * v.Number + 20, @finishing_hour),
		'Y',
		0,
		0
FROM		(
			SELECT	cia_nt,
				RIGHT(starting_hour, 6) AS starts_at,
				RIGHT(finishing_hour, 6) AS ends_at
			FROM	dbo.Test_CIA_LIST
			WHERE	Status = 'Active'
		) AS s
CROSS JOIN	(
			SELECT	0 AS theDay UNION ALL
			SELECT	1 UNION ALL
			SELECT	2 UNION ALL
			SELECT	3 UNION ALL
			SELECT	4 UNION ALL
		) AS d
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		20 * v.Number < DATEDIFF(MINUTE, @starting_hour, @finishing_hour)

E 12°55'05.63"
N 56°04'39.26"

vgarzon
Starting Member

11 Posts

Posted - 2008-10-01 : 09:55:27

I can't believe the mistake I was making...

The solution was to change those 2 lines that contained the select clauses for:

-- Calculates the start of frame
SET @calculated_start = (SELECT CONVERT (smalldatetime,(CONVERT(varchar,DATEPART(yyyy, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(mm, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(dd, DATEADD(dd,@counter_week_days, GETDATE())))+' '+CONVERT(varchar,RIGHT(DATEADD(mi,(20*@frame_number),starting_hour),6)))) FROM dbo.Test_CIA_LIST WHERE CIA_NT=@cia_nt)

-- Calculates the end of frameSET @calculated_end = (SELECT CONVERT (smalldatetime,(CONVERT(varchar,DATEPART(yyyy, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(mm, DATEADD(dd,@counter_week_days, GETDATE())))+'-'+CONVERT(varchar,DATEPART(dd, DATEADD(dd,@counter_week_days, GETDATE())))+' '+CONVERT(varchar,RIGHT(DATEADD(mi,((20*@frame_number)+20),finishing_hour),6)))) FROM dbo.Test_CIA_LIST WHERE CIA_NT=@cia_nt)

and that was it... justa adding the initial "(" and final ")" Problem solved

Thanks elancaster and CVDpr!!!. Be happy,

Victor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:59:27

Did you pay any attention to the set-based suggestion?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources