Building A Sql Statement In A Stored Proc

Hi All,

What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign.

I've tried various ways to create this query and it always truncated around the point of the % sign.

Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings!

Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc.

Thanks for any help on this!

regrads

davej

@var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '
@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE'

@var3 = ''%london%' OR item_Text LIKE '%solicitor%''

@var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'

@varfull = @var1+@var2+@var3+@var4