:) - good man Kevan!...........I remember the days of some serious dynamic SQL strings (along with serious headaches!) - youll be pleased to know they they are all coming round again...........
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 12:35 To: CF-Talk Subject: RE: sql Stored Proc Thanks stephen. But then as an ex-thoughtbubbler I would naturally be right. -----Original Message----- From: Stephen Galligan [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 10:08 To: CF-Talk Subject: RE: sql Stored Proc I agree Kevan. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 09:46 To: CF-Talk Subject: RE: sql Stored Proc I don't think you can use a variable to build dynamic sql like that. I think you have to build the sql statement in a string and then use execute(@yoursqlstring) to run it. -----Original Message----- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: 01 May 2002 17:44 To: CF-Talk Subject: RE: sql Stored Proc 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 ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
