Dynamic SQL Query

I am having trouble getting this dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.
I want to build this query with only values that have values that
are passed, if they are null, no need to be included in query. This
almost seems to work
except I have issues with getting the query to work when the field is
an int.
I get this error
Syntax error converting the varchar value 'select * from company where
categoryid = '' to a column of data type int.
Assistance would be appreciated.
_________________________________
CREATE PROCEDURE stp_Search
@.categoryid int = NULL,
@.commodityid int = NULL,
@.companyname varchar(25) = NULL,
@.streetname varchar(25) = NULL,
@.communityid int = NULL,
@.status varchar(1) = NULL
AS
DECLARE @.sql varchar(4000)
SELECT @.sql = 'select * from company '
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where categoryid = ''' + @.categoryid + ''''
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
IF @.companyname IS NOT NULL
SELECT @.sql = @.sql + ' and companyname LIKE ''' + @.companyname +
''''
IF @.status IS NOT NULL
SELECT @.sql = @.sql + ' AND status LIKE ''' + @.status + ''''
EXEc(@.sql)pisquem@.hotmail.com,
Are you sure you want to concatenate the logical expressions below, based on
@.categoryid?

> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
They way you are doing it is not a good practice. Try using parameters with
sp_executesql and you will not have to worry about casting, sql injection,
etc.
SELECT @.sql = 'select c1, c2, ..., cn from dbo.company where 1 = 1'
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' and categoryid = @.categoryid'
IF @.commodityid IS NOT NULL
SELECT @.sql = @.sql + ' and commodityid = @.commodityid'
IF communityid IS NOT NULL
SELECT @.sql = @.sql + ' where communityid = @.communityid'
IF @.companyname IS NOT NULL
SELECT @.sql = @.sql + ' and companyname = @.companyname'
IF @.status IS NOT NULL
SELECT @.sql = @.sql + ' and status = @.status'
exec sp_executesql @.sql, N'@.categoryid int, @.commodityid int, @.companyname
varchar(25), @.streetname varchar(25), @.communityid int, @.status varchar(1)',
@.categoryid, @.commodityid, @.communityid, @.companyname, @.status
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"pisquem@.hotmail.com" wrote:

> I am having trouble getting this dynamic query in a sp working and the
> code is below so can someone please help me? It would be very much
> appreciated.
> I want to build this query with only values that have values that
> are passed, if they are null, no need to be included in query. This
> almost seems to work
> except I have issues with getting the query to work when the field is
> an int.
> I get this error
> Syntax error converting the varchar value 'select * from company where
> categoryid = '' to a column of data type int.
>
> Assistance would be appreciated.
> _________________________________
> CREATE PROCEDURE stp_Search
> @.categoryid int = NULL,
> @.commodityid int = NULL,
> @.companyname varchar(25) = NULL,
> @.streetname varchar(25) = NULL,
> @.communityid int = NULL,
> @.status varchar(1) = NULL
> AS
> DECLARE @.sql varchar(4000)
> SELECT @.sql = 'select * from company '
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where categoryid = ''' + @.categoryid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
> IF @.companyname IS NOT NULL
> SELECT @.sql = @.sql + ' and companyname LIKE ''' + @.companyname +
> ''''
> IF @.status IS NOT NULL
> SELECT @.sql = @.sql + ' AND status LIKE ''' + @.status + ''''
> EXEc(@.sql)
>