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

Posted on 2012-09-11
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
    LVL 15

    Accepted Solution

    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

    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

    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
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now