Can anyone else give me any insight on this?
-----Original Message-----
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 12:08 PM
To: CF-Talk
Subject: RE: sql Stored Proc
I recieve an error incorrect synatx near the keyword "and".
declare @col as varchar(500)
if(@rpttype) = 'Missing'
begin
set @col = "([desc] Like '%Missing%' OR [DESC] IS NULL)"
end
set nocount on
if (@corp is not null and @reg_no is null and @dis_no is null and @date1 is
not null and @date2 is null)
BEGIN
SELECT m_id, y_id, reg_no,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST,
CONVERT(datetime, (cast(m_id as varchar)) + '/01/' + (cast(y_id as
varchar))) as dateinput
into #blahtable
FROM vw_CTMCORPAPPFULLVIEWprod
where @col and year = @date1
GROUP BY reg_NO, y_id, M_id
ORDER BY cast(y_id as int), cast(m_id as int)
-----Original Message-----
From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 12:00 PM
To: CF-Talk
Subject: RE: sql Stored Proc
You missed the word "AS" in the declare statement:
DECLARE @col AS nvarchar(30)
-----Original Message-----
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 9:00 AM
To: CF-Talk
Subject: RE: sql Stored Proc
If the colors didnt come across the declare and set all work fine the part
that is giving me a problem is the @col when I try to use it in the query.
Does anyone know how to make this work correctly?
Joshua TIpton
-----Original Message-----
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 11:35 AM
To: CF-Talk
Subject: sql Stored Proc
The red protion works and I can output the variable my problem is the blue
highlighted part. Is it possible to create that part of the query from what
is in a variable?
CREATE PROCEDURE sp_ctmdatacorp
@corp char(4) = null,
@reg_no char(4) =null,
@dis_no char(4) =null,
@date1 varchar(4),
@date2 varchar(4) = null,
@rpttype char(10)
AS
/*SELECT @corp, @reg_no, @dis_no, @date1,@date2, @rpttype*/ declare @col
varchar(30)
if(@rpttype) = 'Missing'
begin
set @col = "[desc] Like '%Missing%' OR [DESC] IS NULL"
end
set nocount on
if (@corp is not null and @reg_no is null and @dis_no is null and @date1 is
not null and @date2 is null)
BEGIN
SELECT m_id, y_id, reg_no,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST,
CONVERT(datetime, (cast(m_id as varchar)) + '/01/' + (cast(y_id as
varchar))) as dateinput
into #blahtable
FROM vw_CTMCORPAPPFULLVIEWprod
WHERE (@col) and year = @date1
GROUP BY reg_NO, y_id, M_id
ORDER BY cast(y_id as int), cast(m_id as int)
insert into #blahtable
SELECT DISTINCT
dbo.tbl_CTMCORPMONTHYEARRDD.m_id,
dbo.tbl_CTMCORPMONTHYEARRDD.y_id,
dbo.tbl_CTMCORPMONTHYEARRDD.reg_no,
0 as intcost, 0 as extcost,
CONVERT(datetime, (cast(tbl_CTMCORPMONTHYEARRDD.m_id as varchar)) +
'/01/' + (cast(tbl_CTMCORPMONTHYEARRDD.y_id as varchar))) as dateinput FROM
dbo.tbl_CTMCORPMONTHYEARRDD LEFT OUTER JOIN #blahtable ON
dbo.tbl_CTMCORPMONTHYEARRDD.y_id = #blahtable.y_id AND
dbo.tbl_CTMCORPMONTHYEARRDD.m_id = #blahtable.m_id AND
dbo.tbl_CTMCORPMONTHYEARRDD.reg_no = #blahtable.reg_no WHERE
(#blahtable.m_id IS NULL) AND (#blahtable.y_id IS NULL) AND
(#blahtable.reg_no IS NULL) and dbo.tbl_CTMCORPMONTHYEARRDD.y_id =
right(@date1,1)
select * from #blahtable
drop table #blahtable
END
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
