Re: Passing a variable column name to a stored procedure

From: Steve B. (SteveB_at_discussions.microsoft.com)
Date: 12/20/04 

Date: Mon, 20 Dec 2004 13:25:02 -0800

Many thanks and happy holidays!

Steve B.

"Adam Machanic" wrote:

> http://www.sommarskog.se/dyn-search.html
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "Steve B." <SteveB@discussions.microsoft.com> wrote in message
> news:DA49E65E-5170-4B2F-B6CA-C98DEBFACC81@microsoft.com...
> > For security purposes, I make a practice of using stored procedures rather
> > than SQL statements in my VBScript code. However, I cannot find a way to
> pass
> > a column name as a stored-procedure variable. I need to do something like
> the
> > following.
> >
> > CREATE PROCEDURE SearchTest
> > @SearchBy varchar(20) = Null,
> > @SearchString varchar(30) = Null,
> > AS
> >
> > Select ClaimNumber
> > From ClaimsHeader
> > Where CASE @SearchBy
> > WHEN 'Manufacturer' THEN MgfrName
> > WHEN 'StoreNumber' THEN Store#
> > ELSE ClaimNumber
> > END
> > = @SearchString
> >
> > This, of course, won't even pass the syntax check.
> >
> > I am allowing the user to both search and sort on any of seven displayed
> > columns, so writing all the SELECT statements for all the possible
> > combinations would make the stored procedure a monster. It is easy to
> build
> > the SQL statement needed in VBScript, but then SQL injection becomes an
> > issue. Any suggestions?
> >
> > TIA...
>
>
>

Relevant Pages

  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: iSeries DB2 Stored Procedures...
    ... If you're going to write SQL procedures that just execute a single SQL statement, performance will probably not be what you want. ... My suggestion would be to execute the SQL statements directly. ... I am looking at writing some stored procedures for database access on ... the entire result of the SELECT without using cursors. ...
    (comp.sys.ibm.as400.misc)
  • Re: Help with Stored Procedure
    ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
    (microsoft.public.sqlserver.programming)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
    (comp.lang.java.databases)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
    (comp.lang.java.programmer)