Re: how to pass an 'sort by' parameter to a stored proc

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Nancy Drew (genpub5_at_hotmail.com)
Date: 11/24/04

  • Next message: jvb: "MSDE ACCESS UPSIZE" 
    Date: Wed, 24 Nov 2004 09:21:23 -0500

    thanks danny - the () was the problem. thanks also for the security
    heads-up.

    "Danny" <istdrs@flash.net> wrote in message
    news:Wc%od.23750$Rf1.23699@newssvr19.news.prodigy.com...
    > When executing a string the ( ) are not optional. Try
    >
    > exec (@SQL)
    >
    > Also if security is of any concern, this proc is very exposed to SQL
    > injection. Either validate the @SortBy variable in the begining of the
    proc
    > and drop out with an error if the validation fails or change the logic of
    > the proc to accept a sort flag and use an if or case statement to build
    the
    > SQL statement from commands you have pretyped in the proc.
    >
    > Danny
    > "Nancy Drew" <genpub5@hotmail.com> wrote in message
    > news:7I2dnfYFDt-DGj7cRVn-rw@rogers.com...
    > >
    > > "Brian Bunin" <bb_43@hotmail.com> wrote in message
    > > news:Dy4nd.45883$QJ3.13664@newssvr21.news.prodigy.com...
    > >> In article <TYKdnVlJhr4LWQHcRVn-sA@rogers.com>, "Nancy Drew"
    > > <genpub5@hotmail.com> wrote:
    > >> >hi all
    > >> >
    > >> >i'm getting an error when trying to pass in a 'sort by' parameter:
    > >> >
    > >> >CREATE PROCEDURE spListUsers
    > >> > @sSortBy varChar(30)
    > >> >AS
    > >> > SELECT dbo.users.firstName, dbo.users.lastName FROM users
    > >> > order by @sSortBy
    > >> >GO
    > >> >
    > >> >anybody know a fix?
    > >> >
    > >> >
    > >>
    > >> You have to build the SQL Statement with the variable and then exec it.
    > >> IE;
    > >> CREATE PROCEDURE spListUsers
    > >> @sSortBy varChar(30)
    > >> AS
    > >> Select @SQL='SELECT dbo.users.firstName, dbo.users.lastName FROM users
    > > order
    > >> by ' + @sSortby
    > >>
    > >> exec @SQL
    > >> GO
    > >
    > > i'm getting an error when trying to run the stored proc when set up as
    you
    > > suggest. here's my stored proc:
    > >
    > > CREATE PROCEDURE spListCompanies
    > > @iRecordID INT,
    > > @iShowAllRecords INT,
    > > @sSortBy varChar(30)
    > > AS
    > > declare @SQL varChar(500)
    > > begin
    > > SELECT @SQL = ' select name from company order by ' + @sSortBy
    > > exec @SQL
    > > end
    > >
    > > GO
    > >
    > >
    > > my asp page keeps retuning this error:
    > > could not find stored procedure 'select name from company order by name'
    > >
    > > any ideas why?
    > >
    > >>
    > >> I am not sure what this does in terms of procedure cache and
    performance,
    > > but
    > >> it's not as good as knowing what the statement should be ahead of time.
    > >> What you COULD do is create the procedure with two seperate statements,
    > > one of
    > >> wich is actualy executed, based on a bit that is passed in.
    > >> IE;
    > >>
    > >> CREATE PROCEDURE spListUsers
    > >> @sSortBy bit
    > >> AS
    > >> IF @sSortby=1
    > >> BEGIN
    > >> SELECT dbo.users.firstName, dbo.users.lastName FROM users order by
    > >> dbo.users.firstName
    > >> END
    > >> IF @sSortby=0
    > >> BEGIN
    > >> SELECT dbo.users.firstName, dbo.users.lastName FROM users order by
    > >> dbo.users.lastName
    > >> END
    > >> GO
    > >
    > >
    >
    >

  • Next message: jvb: "MSDE ACCESS UPSIZE"

    Relevant Pages

    • Re: Global Temp Table & Multiple Users
      ... design and I'm not sure if performance would be any better. ... about serialization is that it limits the dbms pounding that these reports ... what is happening is this a dynamic SQL ... > The inner proc generates a portion of the SQL, ...
      (microsoft.public.sqlserver.server)
    • Re: Urgent: Permissions Problem with Dynamic SQL
      ... > having problems with permissions when using dynamic sql. ... > CREATE PROC a2 AS ... > GRANT EXECUTE ON a2 TO user1 ...
      (microsoft.public.sqlserver.security)
    • Re: Variable server names
      ... My initial desire was to do this in a single stored proc. ... Then I realized that mixing dynamic SQL and a table variable was going ... executing the remote from the local. ... but I understood it as management wanted to connect to a server ...
      (microsoft.public.sqlserver.programming)
    • Re: Views vs Stored Procedures, whats the difference?
      ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
      (comp.databases.ms-sqlserver)
    • Re: how to pass an sort by parameter to a stored proc
      ... When executing a string the are not optional. ... Also if security is of any concern, this proc is very exposed to SQL ... >> CREATE PROCEDURE spListUsers ...
      (microsoft.public.sqlserver)