SQL Server Templates - How Do I Escape The Less Than Character?

Question

I like to use SQL Server 2005 templates to run frequently used queries. You can include parameters in your templates using this syntax:

<LastName, varchar, 'Bob'>

I have a query that needs the less than or equals to operator <= but unfortunately the SQL Server 2005 template interprets that as the start of a parameter. I have been unable to find a way to use the < (less than character) as a literal.

Answer 1

when I Specify Values for Template Parameters, this runs fine for me:

select * from <xyz, varchar,YourTable> WHERE ID<=1000 AND ID>=20000

perhaps you do not have every parameter's "<" and ">" paired properly

EDIT I see the problem now:

SELECT * FROM <xyz, varchar,YourTable> WHERE ID<=1000 AND ID>=20000 AND <xyz2,varchar,YourColumn> IS NOT NULL

results in:

SELECT * FROM YourTable WHERE IDYourColumn IS NOT NULL

try making the "<" character into a parameter, like this:

SELECT * FROM <xyz, varchar,YourTable> WHERE ID<lessthan,char,<>=1000
AND ID>=20000 AND <<xyz2,varchar,YourColumn> IS NOT NULL

it results in:

SELECT * FROM YourTable WHERE ID<=1000
AND ID>=20000 AND YourColumn IS NOT NULL

OR split the lines, line breaks seem to make a difference:

SELECT * FROM <xyz, varchar,YourTable> WHERE ID<=1000 AND ID>=20000 
AND <xyz2,varchar,YourColumn> IS NOT NULL

results in:

SELECT * FROM YourTable WHERE ID<=1000 AND ID>=20000 
AND YourColumn IS NOT NULL

Answer 2

With template

select top 10 * from syscolumns
where <xtype, varchar(128), xtype <= 60>

If you select (menu) Query > Specify Values for template parameters, the default value for replacement is "xtype <= 60", which is correct, and upon substitution, the resulting query text is

select top 10 * from syscolumns
where xtype <= 60

which is exactly what one would expect. In other words, it does not appear to be the case that the "<" symbol needs to be escaped. However, ">" is more problematic:

select top 10 * from syscolumns
where <xtype, varchar(128), xtype >= 60>

This will fail when opening the "specify values" dialog. However, in this instance, it is fine to specify

select top 10 * from syscolumns
where <xtype, varchar(128), value>

and enter

xtype >= 60

in the "value" field for replacement. This produces

select top 10 * from syscolumns
where xtype >= 60

which is again as one would expect. So it would seem that the default value for replacement may not contain a ">".