two create statements within a stored procedure

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

General SQL Server Forums

New to SQL Server Programming

two create statements within a stored procedure

Author

Topic

ariez88
Starting Member

4 Posts

Posted - 2011-10-31 : 11:52:47

hii m writing a stored procedure tht uses a temporary table, a cursor for processing rows of temp table. now within this stored procedure, i have written a code for creating a temptable but SQL says tht we can not use miltiple create statements in one batch.. Sql also highlights the @category variable under if statements and says 'incorrect syntax near '@category''...please rectify if i have made any mistakes in writing this code....any help would be appreciated. here is the code i hv written
create table #temp
{ stud_id varchar(50), stud_fname varchar(50), student_age varchar(50),agecategory varchar(50)}

CREATE PROCEDURE usp_agecategories
@Stud_Id varchar(50),
@Stud_fname varchar(50),
@Stud_lname varchar(50),
@Age varchar(50),
@Category varchar(20)

AS
declare crsr cursor for
select * from Students
open crsr
fetch next from crsr into @Stud_Id,@Stud_fname,@Stud_lname,@Age,@Category
while (@@FETCH_STATUS <>-1)
begin
if (@Age between 13 and 19)
@category = 'teenager'
else if (@Age between 20 and 25)
@category = 'early 20s'
else if (@Age between 26 and 30)
@category = 'late 20s'
else if (@Age between 31 and 35)
@category = 'early 30s'
else if (@Age between 36 and 40)
@category = 'late 30s'
if (@Age between 41 and 45)
@category = 'early 40s'
insert into temp values (@Stud_id,@Stud_fname,@Age,@category)
fetch next from crsr into @Stud_Id, @Stud_fname,@Age,@category
end
select * from temp

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 11:59:06

You need a go before the create procedure - and the temp table probably won't be tyhere when it is run. How about

CREATE PROCEDURE usp_agecategories
@Stud_Id varchar(50),
@Stud_fname varchar(50),
@Stud_lname varchar(50),
@Age varchar(50),
@Category varchar(20)

AS
create table #temp
( stud_id varchar(50), stud_fname varchar(50), student_age varchar(50),agecategory varchar(50))

declare crsr cursor for
select * from Students
open crsr
fetch next from crsr into @Stud_Id,@Stud_fname,@Stud_lname,@Age,@Category
while (@@FETCH_STATUS <>-1)
begin
if (@Age between 13 and 19)
@category = 'teenager'
else if (@Age between 20 and 25)
@category = 'early 20s'
else if (@Age between 26 and 30)
@category = 'late 20s'
else if (@Age between 31 and 35)
@category = 'early 30s'
else if (@Age between 36 and 40)
@category = 'late 30s'
if (@Age between 41 and 45)
@category = 'early 40s'
insert into #temp values (@Stud_id,@Stud_fname,@Age,@category)
fetch next from crsr into @Stud_Id, @Stud_fname,@Age,@category
end
select * from #temp

but why not
select Stud_Id, Stud_fname, Stud_lname, Age,
Category = case when Age between 20 and 25 then 'teenager'
when Age between 20 and 25 then 'early 20s'
....
end
from Students

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 12:06:13

why you need a cursor? wont below suffice?


CREATE PROCEDURE usp_agecategories
@Stud_Id varchar(50),
@Stud_fname varchar(50),
@Stud_lname varchar(50),
@Age varchar(50),
@Category varchar(20)

AS
insert into temp 
select Stud_Id,Stud_fname,Age,
case when Age between 13 and 19 then 'teenager'
when Age between 20 and 25 then 'early 20s'
when Age between 26 and 30 then 'late 20s'
....
end
from Students
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 12:07:18

ah...got a call in b/w and i didnt refresh

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ariez88
Starting Member

4 Posts

Posted - 2011-10-31 : 12:21:29

i hv to do it through cursors.. tht is the requirement...any suggestion ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 12:52:00

>>tht is the requirement...any suggestion
Leave. Better than working for stupid people

Did you read my post?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:13:33

quote:
Originally posted by ariez88

i hv to do it through cursors.. tht is the requirement...any suggestion ?

wow... is it production code? or are you doing sql course?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ariez88
Starting Member

4 Posts

Posted - 2011-10-31 : 13:17:47

m learning sql man and m new to this..

ariez88
Starting Member

4 Posts

Posted - 2011-10-31 : 13:20:49

quote:
Originally posted by nigelrivett

>>tht is the requirement...any suggestion
Leave. Better than working for stupid people

Did you read my post?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yeah I did. using case is not an issue..using cursor is...how wud I learn if I wont use it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:26:23

quote:
Originally posted by ariez88

quote:
Originally posted by nigelrivett

>>tht is the requirement...any suggestion
Leave. Better than working for stupid people

Did you read my post?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yeah I did. using case is not an issue..using cursor is...how wud I learn if I wont use it

whats the problem with cursor? even after putting # table inside its not working?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 13:46:18

quote:
Originally posted by ariez88

quote:
Originally posted by nigelrivett

>>tht is the requirement...any suggestion
Leave. Better than working for stupid people

Did you read my post?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yeah I did. using case is not an issue..using cursor is...how wud I learn if I wont use it

Did you try it - the sp with the cursor shuold work.
As to learning - I've never written a cursor in sql server and don't think it's a useful thing to learn (apart from answering questions here).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources