Ok with that I have another problem kind of the same thing, but I ma now building a date sting to filter but I keep getting an error: Syntax error converting datetime from character string.
Here is the code:
CREATE PROCEDURE GetFirstRenewalActual AS
DECLARE
@rptMONTH int,
@rptmonth_v varchar(2),
@AdvMONTH varchar(2),
@people int,
@company int,
@rptYEAR varchar(4),
@did int,
@rentable varchar(10),
@total int,
@chkdate datetime
IF object_id('#FirstRunTotals') is not null
BEGIN
DROP TABLE #FirstRunTotals;
END
ELSE
BEGIN
CREATE TABLE #FirstRunTotals
(
rptrenewal varchar(7) NULL,
people int,
company int,
total int
);
END
DECLARE didCursor cursor local static
FOR
SELECT rptid
FROM RenewalMonths
WHERE completed = 1
ORDER BY rptyear,rptmonth
open didCursor
fetch didCursor into @did
WHILE @@fetch_status=0
BEGIN
SELECT @rptyear=rptyear,@rptmonth=rptmonth,@rptmonth_v = rptmonth
FROM RenewalMonths
WHERE rptid = @did
SELECT @AdvMonth = @rptmonth +1
SELECT @rentable = @[EMAIL PROTECTED]'_ren'
SELECT @chkdate = CAST(@AdvMonth+'/1/'[EMAIL PROTECTED] as datetime)
--- DIES RIGHT HERE !!!
SELECT
(
SELECT count(*)
FROM persondem(nolock)
WHERE expdate > @chkdate
AND CAST(eaanum as integer) IN (
SELECT CAST(EAANUM as integer)
[EMAIL PROTECTED])
)+(
SELECT count(*)
FROM companydem(nolock)
WHERE expdate > @chkdate
AND CAST(eaanum as integer) IN (
SELECT CAST(EAANUM as integer)
[EMAIL PROTECTED])
)
INSERT INTO #FirstRunTotals
(rptrenewal,people,company,total)
VALUES
(@rptyear+'-'[EMAIL PROTECTED],@people,@company,@total)
fetch next FROM didCursor into @did
END
close didCursor
deallocate didCursor
SELECT * FROM #FirstDayTotals
GO
-----Original Message-----
From: Ali Awan [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 23, 2004 11:21 AM
To: CF-Talk
Subject: Re: SQL: Passing vars to a LIKE statement
Eric,
Can you try just doing this first?
AND demographicvaluedesc LIKE @month+'%'
And do you still get the error?
Then also try this, don't know if it will solve the problem, but it looks like SQL is actually trying to do an arithmetic add on the values, rather than concatenating the strings in the searchstring.
Try using a different variable for year and declare it as a varchar.
@year_temp varchar(4). Put spaces before and after the '+' sign, don't know if it'll help.
and then in the searchstring do this again.
AND demographicvaluedesc LIKE @month + '%' + @year_temp + '%'
Hope this works,
Ali
>Pass the percent signs as part of your var... Don't know why it works
>but it does. There is a way to do it in a stored proc but I don't
>remember off the top of my head.
>
>
>
> _____
>
>From: Eric Creese [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, September 22, 2004 4:15 PM
>To: CF-Talk
>Subject: RE: SQL: Passing vars to a LIKE statement
>
>
>
>thanks but it is not helping, here is my stored proc and the error msg I
>receive
>
>
>CREATE PROCEDURE GetMonthlyJoins AS
>
>DECLARE
>@MONTH varchar(3),
>@MONTHNBR int,
>@YEAR int,
>@did datetime,
>@count int,
>@likevar varchar(25)
>
>
>IF object_id('#MonthlyJoins') is not null
>BEGIN
>TRUNCATE TABLE #MonthyJoins;
>END
>ELSE
>BEGIN
> CREATE TABLE #MonthyJoins
> (
> rptmonth varchar(3),
> rptmonthnbr int,
> rptyear int,
> total int
> );
>END
>
>DECLARE didCursor cursor local static
>
>
>FOR
>SELECT DISTINCT rptdate
>FROM ALL_DAILY_TOTALS
>WHERE convert(varchar(10),rptdate,101) like '%/01/%'
>ORDER BY rptdate
>
>open didCursor
>
>
>fetch didCursor into @did
>
>
>WHILE @@fetch_status=0
>BEGIN
>
>SET @year = (SELECT DATEPART(year, @did))
>SET @month =(SELECT UPPER(LEFT(DATENAME(month, @did),3)))
>SET @monthnbr =(SELECT MONTH(@did))
>
>SELECT @count=COUNT(*)
>FROM avectraprod.dbo.persondemographic(nolock)
>WHERE demographicgroupid =4
>AND demographicitemid =7
>AND demographicvaluedesc LIKE @month+'%'[EMAIL PROTECTED]'%'
>
>INSERT INTO #MonthyJoins
>( rptmonth,rptmonthnbr,rptyear, total )
>VALUES
>(@month,@monthnbr,@year,@count)
>
>
>fetch next FROM didCursor into @did
>
>
>END
>
>
>close didCursor
>
>
>deallocate didCursor
>
>
>SELECT * FROM #MonthyJoins
>
>
>GO
>
>
>Error Message:
>Server: Msg 245, Level 16, State 1, Procedure GetMonthlyJoins, Line 51
>Syntax error converting the varchar value 'FEB%' to a column of data
>type int.
>
>
>-----Original Message-----
>From: Ali Awan [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, September 22, 2004 1:21 PM
>To: CF-Talk
>Subject: Re: SQL: Passing vars to a LIKE statement
>
>> I need to pass some varibles into a LIKE clause in a SELECT statement.
>
>> I am having trouble with the format.
>>
>> AND valuedesc LIKE '[EMAIL PROTECTED]@year%' is what I was doing and know this
>> is wrong cause it see that garb as a literal string. How do I pass
>> this so the vars
>show?
>
>Eric,
>
>I recently ran into this same problem recently. The correct way to do
>this is:
>AND valuedesc LIKE '%'[EMAIL PROTECTED]'%'[EMAIL PROTECTED]'%'
>
>You need to put the "+" signs in and remember to put the single quotes
>properly. Whenever you use variables, you have to build the statement
>as a string.
>
>Hope this helps,
>Ali
> _____
>
> _____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
