"pisq***@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)
>
>

> pisq***@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
>
> "pisq***@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)
> >
> >

news:1148060604.049062.89220@j33g2000cwa.googlegroups.com...
> Thanks for the recommendation, can you outline exactly what you are
> proposing.
> Thanks.
>
> Alejandro Mesa wrote:
> > pisq***@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
> >
> > "pisq***@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)
> > >
> > >
>

"Jim Underwood" wrote:

> Actually, he posted usable code and a link to a comprehensive discussion on
> dynamic SQL.  Check out the link, then reread the post and try the code.
>
> <pisq***@hotmail.com> wrote in message
> news:1148060604.049062.89220@j33g2000cwa.googlegroups.com...
> > Thanks for the recommendation, can you outline exactly what you are
> > proposing.
> > Thanks.
> >
> > Alejandro Mesa wrote:
> > > pisq***@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
> > >
> > > "pisq***@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)
> > > >
> > > >
> >
>
>
>