I'm working on a web page that allows searching by date and by a specific parameter which also has an option to select whether to display records with complete data, with discrepancies, or those with no data but id.
I thought of having the conditions to be selected in the stored procedure based on the selected option passed as parameters. At first I thought of creating the query on code behind but I challenged myself to try it on stored procedures. A dynamic query is what I needed. I found a very useful information from this site with regards to creating a dynamic query.
The example is quite simple. It doesn't deal with queries containing quotes ('). I had an idea after reading this post.
As I try to save the stored procedure, I receive an error message that the select string I created cannot be converted into varchar. I had a hard time figuring out why I receive the error message until I finally figured out that i have to convert the parameter's result to varchar which is initialized as int. The solution is something like this: cast(@parameter as varchar(5)).