November 5, 2007 at 9:33 am
Hi experts,
What is wrong with this piece of code. Parsing says incorrect syntax but it looks right to me.
declare @db_currentasvarchar(12)
declare @db_counterasint
SET @db_counter = 1
WHILE @db_counter < 5
BEGIN
CASE @db_counter
WHEN 1 THEN SET @db_current = 'summit032007'
WHEN 2 THEN SET @db_current = 'summit042007'
WHEN 3 THEN SET @db_current = 'summit152007'
WHEN 4 THEN SET @db_current = 'summit252007'
ELSE BREAK
END
SET @db_counter = @db_counter + 1
...more stuff
The @db_current is going to be used in a statement as follows:
DECLARE CURSOR_SELECT CURSOR FOR
SELECT
...stuff...
FROM openquery(@db_current, 'SELECT * FROM cl_master') cl_master
Parsing doesn't like something near the @db_current in the openquery() either.
Thanks for your help.
Hope
November 5, 2007 at 10:31 am
It looks to me that you're not reading up on what you can or cannot do in T-SQL. You're mixing procedural syntax into TransactSQL, which is getting you messed up.
First:
CASE isn't a flow control mechanism in TSQL. It's used solely for conditional return of values.
So - this would be a valid use:
....
WHILE @db_counter < 5
BEGIN
select @db_current= CASE @db_counter
WHEN 1 THEN 'summit032007'
WHEN 2 THEN 'summit042007'
WHEN 3 THEN 'summit152007'
WHEN 4 THEN 'summit252007'
ELSE 'invalid'
END
if @db_current='invalid' then BREAK
.....
END
Second - you need to read up on dynamic SQL. The use of variables in SQL statements like you want to do with the OPENQUERY(...) stuff requires dynamic SQL.
If I may ask - what are you trying to accomplish? You're heading into some treacherous waters, with some constructs you usually would be told to avoid. SQL is not like a procedural language, and shouldn't be treated as a procedural language in most cases (it's a set processor by nature, so it often requires a different mindset). Just trying to make sure you have a paddle AND that the boat is pointing forward when you so venture....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2007 at 12:27 pm
Matt, before I can finish reading all that relates to your reply I want to thank you. Thank you. Your comments about T-SQL and dynamic SQL shed more light on my limited reading and made the code parse successfully.
I do understand what you wrote but not yet why it needed to be written that way. I do not yet know much about SQL, procedural or otherwise. I placed three book orders this past weekend, one is a WROX book called Transact-SQL the other two are Beginning SQL Server and Professional SQL Server. With a few well written responses like yours I hope to understand what I read. The situation in my company is accepting of an open book at my elbow (or an open forum question) for now.
The desire to accomplish the original snippet came from wanting to change a stored procedure that connects to one database, summit032007, and making it connect to each of four databases. I then have to make a job to run the stored procedure on a schedule. I am combining an identical table in each of four different Pervasive databases into one table in a single SQL Server database. This will keep the table in SQL Server in synch with application tables in the Pervasive database. My company has two main applications, one uses Pervasive and the other uses SQL Server 2000 and management desires to create all new reports out of SQL Server for two main reasons, 1) new report turn around time and 2) better BI reports if information from the two systems is strategically combined.
Sorry for the long winded thank you. Any relevant reading suggestions or advice is always well received. I have a lot to learn.
Thank you.
Warm regards,
Hope
November 5, 2007 at 2:34 pm
Hi Matt,
Below is the concept I have, but still not yet working. But I have hope. No, that's not right, I am Hope. Thanks for today.
Warm regards,
Hope
CREATE PROCEDURE sync_pr_wmast AS
BEGIN
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @db_currentasvarchar(12)
declare @db_counterasint
declare @p_Code aschar(5)
declare @p_Descrip aschar(35)
declare @p_Short_Desc aschar(10)
SET @db_counter = 1
WHILE @db_counter < 5
BEGIN
SELECT @db_current = CASE @db_counter
WHEN 1 THEN 'summit032007'
WHEN 2 THEN 'summit042007'
WHEN 3 THEN 'summit152007'
WHEN 4 THEN 'summit252007'
ELSE 'invalid'
END
if @db_current = 'invalid' then BREAK
SET @db_counter = @db_counter + 1
DECLARE CURSOR_SELECT CURSOR FOR
SELECT
cast(Code as char(5))as Code ,
cast(Descrip as char(35)) as Descrip ,
cast(Short_Desc as char(10)) as Short_Desc
FROM openquery(@db_current, 'SELECT * FROM cl_master) cl_master
open CURSOR_SELECT
fetch next from CURSOR_SELECT into
@p_Code ,
@p_Descrip ,
@p_Short_Desc
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (SELECT 1 FROM cl_master WHERE code=@p_code AND OpCo=3)
UPDATE cl_master SET
OpCo =3 ,
Code =@p_Code ,
Descrip =@p_Descrip ,
Short_Desc =@p_Short_Desc
WHERE code=@p_code AND OpCo=3
else
INSERT INTO cl_master
(OpCo ,
Code ,
Descrip ,
Short_Desc
)
VALUES(
3 ,
@p_Code ,
@p_Descrip ,
@p_Short_Desc
)
fetch next from CURSOR_SELECT into
@p_Code ,
@p_Descrip ,
@p_Short_Desc
END
close CURSOR_SELECT
deallocate CURSOR_SELECT
END
END
November 5, 2007 at 3:30 pm
continuing...
The line with the BREAK in it does not need a 'then'
The openquery() doesn't take variables. Possibly I can EXEC another sproc that will create a temporary linked server as per the BOL OPENQUERY example.
Warm regards to anyone following my education.
Hope
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply