Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1266
Home > Forums > Sql Server >

error showing in stored procedure

Posted by Klbaiju under Sql Server on 7/9/2014 | Points: 10 | Views : 1890 | Status : [Member] | Replies : 2
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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



Reply | Reply with Attachment
Alert Moderator 

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

Latest Posts