February 10, 2009 at 10:02 am
Good day all.
I have been using Access for quite some time now and upsize
my tables to SQL Server. I have my tables linked into Access and use Access update querys to populate some of the null fields in the table on a monthly basis. I'd like this task to be handled in SQL using a Stored Procedure and went about creating an sP to run a couple of update querys, however, after checking syntax, saving then reopening the SP only one update query remained, the other had disappeared. So below is the code I had written:-
CREATE procedure UpdateData AS
update tblTest
SET Field01='Value01', Field02='Value02', Field03='Value03'
WHERE (Field04 = 1) AND (Field05 = 2) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
GO
update tblTIPTest
SET Field01='Value04', Field02 ='Value05', Field03 ='Value06'
WHERE (Field04 = 1) AND (Field05 = 1) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
GO
update tblTIPTest
SET Field01 ='Value07', Field02 ='Value08', Field03 ='Value09'
WHERE (Field04 = 2) AND (Field05 = N'MBL') AND (Field06 <> N'0' OR
Field07 = N'T') AND (Field08 <> 1405) AND (Field08 <> 1323)
GO
But once I open it all I see is the following:-
CREATE procedure UpdateData AS
update tblTest
SET Field01='Value01', Field02='Value02', Field03='Value03'
WHERE (Field04 = 1) AND (Field05 = 2) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
GO
Can anyone please advise on the syntax used to string two or more querys together so only one SP can do the job of more than one query?
Thanks in advance,
Mitch....
February 10, 2009 at 10:16 am
Hi,
You have used two many "GO"
In sql server, when you key in a code for sproc and put "go" at some place, it would feel as if the sproc has finished at that line.
so just have one "GO" at the end. and remove the ones in the middle.
Regards,
Sriram
CREATE procedure UpdateData AS
update tblTest
SET Field01='Value01', Field02='Value02', Field03='Value03'
WHERE (Field04 = 1) AND (Field05 = 2) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
-- remove GO
update tblTIPTest
SET Field01='Value04', Field02 ='Value05', Field03 ='Value06'
WHERE (Field04 = 1) AND (Field05 = 1) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
-- remove GO
update tblTIPTest
SET Field01 ='Value07', Field02 ='Value08', Field03 ='Value09'
WHERE (Field04 = 2) AND (Field05 = N'MBL') AND (Field06 <> N'0' OR
Field07 = N'T') AND (Field08 <> 1405) AND (Field08 <> 1323)
GO
Sriram
February 10, 2009 at 10:16 am
GO ends the stored procedure and batch. Use a return so you know where the proc ends.
CREATE procedure UpdateData AS
update tblTest
SET Field01='Value01', Field02='Value02', Field03='Value03'
WHERE (Field04 = 1) AND (Field05 = 2) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
update tblTIPTest
SET Field01='Value04', Field02 ='Value05', Field03 ='Value06'
WHERE (Field04 = 1) AND (Field05 = 1) AND (Field06 = N'MBL') AND (Field07 > N'0' OR
Field08 = N'T')
update tblTIPTest
SET Field01 ='Value07', Field02 ='Value08', Field03 ='Value09'
WHERE (Field04 = 2) AND (Field05 = N'MBL') AND (Field06 N'0' OR
Field07 = N'T') AND (Field08 1405) AND (Field08 1323)
RETURN
February 10, 2009 at 10:34 am
Thats great stuff guys.
Works like a charm, I really appreciate your help.
Thanks,
Mitch....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply