error showing in stored procedure

Posted by Klbaiju under Sql Server on 7/9/2014 | Points: 10 | Views : 373 | Status : [Member] | Replies : 2
Hi following my stored procedure showing error
create procedure Sp_addconductormaster
@conductor_id nvarchar(50),
@cname nvarchar(50),
@address nvarchar(50),
@place nvarchar(50),
@contactno nvarchar(50)
as
create table #temp(conductor_id nvarchar(50),cname nvarchar(50),address nvarchar(50),place nvarchar(50),contactno nvarchar(50)
insert into #temp(conductor_id,cname,address,place,contactno)values(300,'mohan','anchal','anchal',9878888564)
insert into #temp(conductor_id,cname,address,place,contactno)values(301,'arun','kollam','kollam',9874448564)
insert into #temp(conductor_id,cname,address,place,contactno)values(302,'jiku','punalur','punalur',9878333564)
declare @cid nvarchar(50)
select @cid= (MAX(a.conductor_id) conductor_id from
(select case when (conductor_id is null)then 300 else MAX(conductor_id)+1 end as conductor_id from #temp group by conductor_id ) a)
@conductor_id=@cid
insert into #temp(conductor_id,cname,address,place,contactno)values(conductor_id,cname,address,place,contactno)
drop table #temp

this is the error
Msg 156, Level 15, State 1, Procedure Sp_addconductormaster, Line 9
Incorrect syntax near the keyword 'insert'.
Msg 102, Level 15, State 1, Procedure Sp_addconductormaster, Line 13
Incorrect syntax near 'conductor_id'.
Msg 102, Level 15, State 1, Procedure Sp_addconductormaster, Line 14
Incorrect syntax near 'a'.

how to solve this

Regards

Baiju




Responses

Posted by: Vuyiswamb on: 7/9/2014 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Change it to this



create procedure Sp_addconductormaster

@conductor_id nvarchar(50),

@cname nvarchar(50),

@address nvarchar(50),

@place nvarchar(50),

@contactno nvarchar(50)

as

create table #temp(conductor_id nvarchar(50),cname nvarchar(50),address nvarchar(50),place nvarchar(50),contactno nvarchar(50))

insert into #temp(conductor_id,cname,address,place,contactno)values(300,'mohan','anchal','anchal',9878888564)

insert into #temp(conductor_id,cname,address,place,contactno)values(301,'arun','kollam','kollam',9874448564)

insert into #temp(conductor_id,cname,address,place,contactno)values(302,'jiku','punalur','punalur',9878333564)

declare @cid nvarchar(50)

select @cid= (
SELECT MAX(a.conductor_id) from
(
SELECT case when (conductor_id is null) then 300
ELSE
MAX(conductor_id)+1
end as conductor_id
from #temp group by conductor_id
) AS a
)

SET @conductor_id=@cid

insert into #temp(conductor_id,cname,address,place,contactno)values(@conductor_id,@cname,@address,@place,@contactno)

drop table #temp


Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/15/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
you can use the below one also....

i cannot get the logic which your are tring here, because your are inserting data to temp table and dropping temp table in the procedure...

Could you explain us the usage of below procedure ? I guess, you supposed to output something....

create procedure Sp_addconductormaster

@conductor_id nvarchar(50),

@cname nvarchar(50),

@address nvarchar(50),

@place nvarchar(50),

@contactno nvarchar(50)

as

create table #temp(conductor_id nvarchar(50),cname nvarchar(50),address nvarchar(50),place nvarchar(50),contactno nvarchar(50))

insert into #temp(conductor_id,cname,address,place,contactno)values(300,'mohan','anchal','anchal',9878888564)

insert into #temp(conductor_id,cname,address,place,contactno)values(301,'arun','kollam','kollam',9874448564)

insert into #temp(conductor_id,cname,address,place,contactno)values(302,'jiku','punalur','punalur',9878333564)

declare @cid nvarchar(50)

select @cid= MAX(a.conductor_id) from
(select case when (conductor_id is null)then 300 else MAX(conductor_id)+1 end as conductor_id from #temp group by conductor_id )a

SET @conductor_id=@cid

insert into #temp(conductor_id,cname,address,place,contactno)values(@conductor_id,@cname,@address,@place,@contactno)

drop table #temp


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response