Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Setting up my first Loop in SQL 2005 1

Status
Not open for further replies.
vba317

vba317

Programmer
Mar 5, 2009
708
US
I am getting an error incorrect syntax near the keyword open. This is my first attempt at writing a loop in SQL 2005. I copied most of the code from another stored procedure on the database. What I am trying to do is to insert 3 values into a table called rptdata_monthly.dbo.rpt_dat_WMG_RevCtr.
The first variable is @rptpd the way the select is written I will only get one value from the select.
The second variable is @modalid I will get 57 differnt modalid's
The third variable is @modaldesc I will get 57 different modaldesc's
INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
VALUES(73,'WMG',@rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2)
I am not sure what I am doing wrong. Any help is appreciated

Tom

Code: 
set nocount on
DECLARE
@rptpd int
,@modalid int
,@modaldesc varchar(255)

declare bulk_insert cursor for

open bulk_insert
fetch next from bulk_insert into @modalid

while @@fetch_status = 0
begin

-- SET 3 variables @rptpd,modalid and modaldesc
SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1)

SET @modalid = (SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

SET @modaldesc = (SELECT modaldesc
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
VALUES(73,'WMG',@rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2)

fetch next from bulk_insert into @modalid -- Next Record
end

close bulk_insert
deallocate bulk_insert


 
Sort by date Sort by votes
I added another select query to get rid of my previous error. Now I am getting another error 'Subquery returned more than one value. This is not permitted when the subquery follows =, <,


Code: 
set nocount on
DECLARE
@rptpd int
,@modalid int
,@modaldesc varchar(255)

declare bulk_insert cursor for

SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1
open bulk_insert

fetch next from bulk_insert into @rptpd

while @@fetch_status = 0
begin

-- SET 3 variables @rptpd,modalid and modaldesc
SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1)

SET @modalid = (SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

SET @modaldesc = (SELECT modaldesc
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
VALUES(73,'WMG',@rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2)

fetch next from bulk_insert into @rptpd -- Next Record
end

close bulk_insert
deallocate bulk_insert
 
Upvote 0 Downvote
May be I missed something, but you declare cursor for
SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1

it is means you expect to get from this query not a single result, right?
after open cursor inside while loop you have

SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1)

so if in first case it is return not a single record, why you thing the same statement second time will give you single record?

And more them that why you think you need a cursor? Why not just

SQL: 
DECLARE
@rptpd int
,@modalid int
,@modaldesc varchar(255)

SET @modalid = (SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

SET @modaldesc = (SELECT modaldesc
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
select 73,'WMG', rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2
from rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1
? :)



 
Upvote 0 Downvote
gk53,
Thanks for your help.
In my queries the query SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1 will return one value the current reporting period.

The two other queries :
SET @modalid = (SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

SET @modaldesc = (SELECT modaldesc
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)

Will return 20 value
When I tried your code I got the error:
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
 
Upvote 0 Downvote
if SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73
return 20 values you can not have
SET @modalid = (SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)
and it is why you get errors.
if as you posted
SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff = 1
return single value, you no need for
SQL: 
declare bulk_insert cursor for
SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1
all what you need is

SQL: 
DECLARE @rptpd int

SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1)

INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
select 73,'WMG',@rptpd,3,'FARKAS, PAUL', modalid, modaldesc,31,0,0,2
	from rptdata_monthly.dbo.rpt_dic_Modal
		WHERE clntid =73
it is find @rptpd value
and insert from select...
 
Upvote 0 Downvote
If I use this last insert statement I will just get the 1 record with the current reporting period. How will I get the modalid for the 20 records?
 
Upvote 0 Downvote
if
SELECT modaldesc
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73
produce 20 records
INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
select 73,'WMG',@rptpd,3,'FARKAS, PAUL', modalid, modaldesc,31,0,0,2
from rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73
will insert 20 records...
and you do not need a loop

Look on code below
SQL: 
declare @rpt_dic_Modal as table
(
	clntid int,
	modaldesc varchar(50),
	modalid int
)

declare @rptpd int


insert into @rpt_dic_Modal
select 73, 'sometext 1', 1
insert into @rpt_dic_Modal
select 73, 'sometext 1', 11
insert into @rpt_dic_Modal
select 73, 'sometext 1', 111
insert into @rpt_dic_Modal
select 73, 'sometext 1', 122
insert into @rpt_dic_Modal
select 73, 'sometext 1', 123
insert into @rpt_dic_Modal
select 73, 'sometext 1', 1445
insert into @rpt_dic_Modal
select 73, 'sometext 1', 167
insert into @rpt_dic_Modal
select 73, 'sometext 1', 189
insert into @rpt_dic_Modal
select 73, 'sometext 1', 19798
insert into @rpt_dic_Modal
select 73, 'sometext 1', 1967
insert into @rpt_dic_Modal
select 73, 'sometext 1', 154
insert into @rpt_dic_Modal
select 73, 'sometext 1', 145

set @rptpd =555


declare @res as table
(
	clntid int,
	wmg varchar(3),
	rptpd int,
	x int,
	name varchar(30),
	modalid int,
	modaldesc varchar(50),
	a int,
	b int,
	c int,
	d int	
)

insert into @res
select 73,'WMG',@rptpd,3,'FARKAS, PAUL', modalid, modaldesc,31,0,0,2
	from @rpt_dic_Modal
		WHERE clntid =73 


select * from @res
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
270
bharons
bharons
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
373
pjw001
pjw001
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
286
SaltyTheFrog
SaltyTheFrog
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
383
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
246
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top