Correct the error - SQL Server Forums

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

Correct the error

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-05-29 : 08:36:20

Hi friends,
I've created one procedure.I'm trying to execute that i got the error message like 'Must declare the scalar variable @series'.
but i declared it already.Table name starts with SI,dont have the fields like series and hono.I dont know how to correct this error.Please help me out.Here is my procedure.

alter proc procinsertAllFields
as
begin
declare @series varchar(10)
declare @hono varchar(5)
declare @tabname varchar(8)
declare @sql nvarchar(500)
if exists (select * from sysobjects where name=ltrim(rtrim('ccno_dir1')))
drop table ccno_dir1
set @sql='create table ccno_dir1(cc_no varchar(20),series varchar(1),
hono varchar(10),denom_code varchar(10),i_date datetime,d_date datetime,
locked varchar(10),csd_no varchar(10),invoice_no int,invoice_date datetime)' --print @sql
exec sp_executesql @sql

declare c cursor
for
select series=substring(name,3,1),hono =substring(name,4,5),name from sysobjects where name like 'si[1-3]_____'
open c
fetch next from c into @series,@hono,@tabname
while @@fetch_status=0
begin
print 'begin'
fetch next from c into @series,@hono,@tabname
set @sql='insert into ccno_dir1(cc_no,series,hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,invoice_date)
select cc_no,series=@series,hono=@hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,
invoice_date from '+@tabname
print @sql
exec sp_executesql @sql

end
close c
deallocate c
end

Thanks in advance!

kiruthika
http://www.ictned.eu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-29 : 08:39:52

[code]set @sql='insert into ccno_dir1(cc_no,series,hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,invoice_date)
select cc_no,series=''' + @series + ''',hono=''' + @hono + ''',denom_code,i_date,d_date,locked,csd_no,invoice_no,
invoice_date from '+@tabname[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 08:39:52

You are using it inside @sql string which wil be executed only at runtime. as such it wont recognise the variable @series. so you need to move declaration statement inside the @sql string.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-05-29 : 08:49:38

Hi friends,
Thanks for your help.Thank you so much.

quote:

Originally posted by harsh_athalye

set @sql='insert into ccno_dir1(cc_no,series,hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,invoice_date) 
select cc_no,series=''' + @series + ''',hono=''' + @hono + ''',denom_code,i_date,d_date,locked,csd_no,invoice_no,
invoice_date from '+@tabname

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kiruthika
http://www.ictned.eu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources