Constraint of using Dynamic SQL In a Stored Procedure

Question

I read an article about Building Dynamic SQL In a Stored Procedure, it was really good.That article said:

A Dynamic SQL is needed when we need to retrieve a set of records based on different search parameters

So, i think we can use it in every project which we need to retrieve some records on different search and there is no constraint using Dynamic SQL. is It true?

There are some pros and cons of dynamic Sql, you should avoid using it whenever you can but at times dynamic sql is the only way to go, Read this acticle to learn more about dynamic sql sommarskog.se/dynamic_sql.html — M.Ali
– M.Ali, Commented Jan 22, 2014 at 11:32

user170442 · Accepted Answer · 2014-01-22 11:25:47Z

1

You should leave dynamic queries as last resort where you are not able to use parameters. This way you will protect yourself from SQL Injections.

You can always make some parameters optional and use those in WHERE clause:

...
WHERE (@Param1 IS NULL OR Field1=@Param1) AND ... etc
...

This way you if you set @Param1 to NULL it means do not use it.

Another option might be by using full text search, more details about this you might find here: http://technet.microsoft.com/en-us/library/ms142571.aspx

answered Jan 22, 2014 at 11:25

user170442

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Constraint of using Dynamic SQL In a Stored Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related