Dynamic SQL Query

Thanks for the recommendation, can you outline exactly what you are
proposing.
Thanks.
Alejandro Mesa wrote:
> pisquem@.hotmail.com,
> Are you sure you want to concatenate the logical expressions below, based
on
> @.categoryid?
>
>
> They way you are doing it is not a good practice. Try using parameters wit
h
> 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:
>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.
<pisquem@.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:
based on
''''
''''
with
injection,
@.companyname
varchar(1)',
where
''''
''''
>|||Jim,
Thanks for jumping in.
AMB
"Jim Underwood" wrote:

> Actually, he posted usable code and a link to a comprehensive discussion o
n
> dynamic SQL. Check out the link, then reread the post and try the code.
> <pisquem@.hotmail.com> wrote in message
> news:1148060604.049062.89220@.j33g2000cwa.googlegroups.com...
> based on
> ''''
> ''''
> with
> injection,
> @.companyname
> varchar(1)',
> where
> ''''
> ''''
>
>|||(pisquem@.hotmail.com) writes:
> Thanks for the recommendation, can you outline exactly what you are
> proposing.
The gist is that you should not interpolate parametet values into
the SQL string, but you should use parameterised statments.
And for this particular problem:
http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx