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]
