Why Sql not bringing back results unless I set varchar size?
- by Tom
I've got an SQL script that fetches results based on the colour passed to it, but unless I set the size of the variable defined as a varchar to (50) no results are returned.
If I use: like ''+@Colour+'%' then it works but I don't really want to use it in case it brings back results I don't need or want.
The column FieldValue has a type of Varchar(Max) (which can't be changed as this field can store different things). It is part of aspdotnetstorefront package so I can't really change the tables or field types.
This doesn't work:
declare @Col VarChar
set @Col = 'blu'
select * from dbo.MetaData as MD where MD.FieldValue = @Colour
But this does work:
declare @Col VarChar (50)
set @Col = 'blu'
select * from dbo.MetaData as MD where MD.FieldValue = @Colour
The code is used in the following context, but should work either way
<query name="Products" rowElementName="Variant">
<sql>
<![CDATA[
select * from dbo.MetaData as MD where MD.Colour = @Colour
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="runtime" requestparamname="pID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" />
<queryparam paramname="@Colour" paramtype="runtime" requestparamname="pCol" sqlDataType="varchar" defvalue="" validationpattern=""/>
</query>
Any Ideas?
