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]
