How do I write an stored procedure if the parameter is the whole SELECT statement

Posted on 2012-09-11
Medium Priority
Last Modified: 2012-09-19

I'm generating a SQL statement in ASP.Net (C#) but don't want to just execute that - for security, I still want to run a stored procedure.  I'm sure there's a way of using the select statement as a parameter and when called the sproc.  Please can someone give me an example of this?  It needs to be an sproc because my database doesn't allow access to database tables through my programs, only via sprocs.
Question by:Lorna70
  • 2
LVL 15

Accepted Solution

tim_cs earned 800 total points
ID: 38386990
From inside the proc you could just call EXEC @YourSql where @YourSql is the script being passed in.  Having a proc like this though is terrible for security.  You might as well just run it from your .NET application.

Author Comment

ID: 38389987
Thanks but what is the best way to do this then for security?  For example, a user completes a search form with lots of different criteria.  Therefore, I need to dynamically generate a SQL statement depending on what they have chosen from the form.  For example, if they have chosen to search on a professional skill, 'AND Skill_ID = 3' needs to be added to the SQL statement.  There could be many AND statements depending on what the user wants to search on.  Any advice on this would be greatly appreciated :-)
LVL 15

Assisted Solution

tim_cs earned 800 total points
ID: 38390401
For each search parameter you could do.

   (@yourparam1 IS NULL or fieldBlah1 = @yourparam1)
AND (@yourparam2 IS NULL or fieldBlah2 = @yourparam2)
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 200 total points
ID: 38391637
I set any possible optional parameters to NULL in top of the stored procedure and use something similar to what tim_cs gave you.
@param1 int = NULL
@param2 varchar(10) = null

Where field1 = ISNULL(@param1, field1)
AND field2 = ISNULL(@param2, field2)

This will compare the field to the parameter only when it contains a value and to itself when the parameter is null.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question