Howdy,

Is there a mailing list, or just this forum?

Below, some syntax I'm wrestling with. There's got to be a better way...?

''''''')' is really hard to debug, and then MS-SQL locks up when I do something wrong.

Is there a way to declare an SQL variable type or something so I don't have to try to stringify all the SQL before hand?

Thanks,

j



declare @BeginDate datetime
declare @EndDate datetime
declare @strsql varchar(max)
declare @OPENQUERY varchar(max)
declare @LinkedServer varchar(20)

set @LinkedServer = 'MS_OPERA'

set @BeginDate = convert(char(11),getdate() - 3,120)
set @EndDate = convert(char(11),getdate() - 3,120)

--pull primary reservation data from reservation_general view
set @strsql = 'SELECT RESV_NAME_ID, cast(GUEST_NAME_ID as varchar2(25)) as GUEST_NAME_ID, PHONE_ID, ADDRESS_ID, MEMBERSHIP_ID, GUEST_FIRST_NAME,
GUEST_MIDDLE_NAME, GUEST_NAME, GUEST_COUNTRY, GUEST_PHONE, RESERVATION_DATE, RESORT, CONFIRMATION_NO,
MARKET_CODE, MARKET_DESC, RATE_CODE, NIGHTS, SHARE_AMOUNT, EFFECTIVE_RATE_AMOUNT,MEMBERSHIP_TYPE,
RESV_STATUS, ROOM_CATEGORY_LABEL, ADULTS, CHILDREN, ARRIVAL, DEPARTURE, UPDATE_DATE, PROMOTIONS
FROM RESERVATION_GENERAL_VIEW WHERE RESV_STATUS = ''''CHECKED OUT'''' AND DEPARTURE BETWEEN ''''' + convert(char(11),@BeginDate,13) + '''''
and ''''' + convert(char(11),@EndDate,13) + ''''''')'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OPERA_RES_GEN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OPERA_RES_GEN]

set @OPENQUERY = 'SELECT *
into OPERA_RES_GEN from openquery(' + @LinkedServer + ','''
exec(@OPENQUERY + @strsql)

Hello,

If you are using SQL 2008, you can reduce set the values of the parameters in the same line in which you delcare them. IE:

declare @BeginDate datetime = convert(char(11),getdate() - 3,120)
declare @EndDate datetime = convert(char(11),getdate() - 3,120)
declare @LinkedServer varchar(20)= 'MS_OPERA'
declare @strsql varchar(max), @OPENQUERY varchar(max)

You can also declare multiple parameters on the same line. Just helps clean things up. If your not using SQL 2008, you'll have to keep it the way you have it.


--pull primary reservation data from reservation_general view
-- There were too many ''''''' at the end of the statement. I've reduced them to what they needed to be (I think)
set @strsql = 'SELECT RESV_NAME_ID, cast(GUEST_NAME_ID as varchar2(25)) as GUEST_NAME_ID, PHONE_ID, ADDRESS_ID, MEMBERSHIP_ID, GUEST_FIRST_NAME,
GUEST_MIDDLE_NAME, GUEST_NAME, GUEST_COUNTRY, GUEST_PHONE, RESERVATION_DATE, RESORT, CONFIRMATION_NO,
MARKET_CODE, MARKET_DESC, RATE_CODE, NIGHTS, SHARE_AMOUNT, EFFECTIVE_RATE_AMOUNT,MEMBERSHIP_TYPE,
RESV_STATUS, ROOM_CATEGORY_LABEL, ADULTS, CHILDREN, ARRIVAL, DEPARTURE, UPDATE_DATE, PROMOTIONS
FROM RESERVATION_GENERAL_VIEW WHERE RESV_STATUS = ''CHECKED OUT'' AND DEPARTURE BETWEEN ''' + convert(char(11),@BeginDate,13) + '''
and ''' + convert(char(11),@EndDate,13) + ''')'

-- I changed this from a select * to a select 1, which really is just true or false. This is just a coding preference in my opinion. I also wrapped the drop statement with a BEGIN/END
IF EXISTS (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[OPERA_RES_GEN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[OPERA_RES_GEN]
END

set @OPENQUERY = 'SELECT *
into OPERA_RES_GEN from openquery(' + @LinkedServer + ','''
--exec(@OPENQUERY + @strsql)

-- An easy way to debug these is to just print the statement out as I did below. Once you've validated that the code is producing the correct results, then you can remove the comment block from the EXEC statement and away you go.
PRINT (@OPENQUERY + @strsql)

-- Is SQL locking up or just running for a long time? I don't know what kind of data your pulling across, but if it's a large data set, it might take some time.


Hope this helps!!

John Morehouse
Omaha SQL/BI User Group Leader

Here's what I came up with:

First off, please understand that I can be anal when it comes to SQL optimization and formatting. Secondly, I am by no means a pro, and I don't play one on TV, but, I did stay at a Holiday Inn Express last night...

Here are a couple of notes regarding the way I addressed this SQL Server 2005 query in a short period of time (I would have done more, but I am somewhat limited on time):
1. Variable declarations can be combined into a single comma-separated statement.
2. Variables can be initialized in a single comma-separated statement.
3. The @BeginDate and @EndDate variables are converted twice, when, in this specific case, it can actually be done once.
4. Whenever possible, I prefer to use variables instead of inline calculations when creating sql statement strings.

Anyway, this is just my personal take on it. I hope this helps.

BTW, I used SQL Pretty Printer (www.wangz.net) for the code formatting, in case anyone was wondering.

Anytime!