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

From: Danny (istdrs_at_flash.net)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 12:33:58 GMT

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

Relevant Pages

  • SQL print statements in a stored proc
    ... I am connecting to SQL Server using ODBC and executing a stored ... the odbc_err_handler to a custom error handler and the sql Print ... print 'Inside stored proc' ...
    (perl.dbi.users)
  • 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)