Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
string,
> is
> > there still an advantage to using stored procedures (over just executing
> it
> > directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let the
SP
> > build the Sql string. Either way though, the Select statement will be a
> > variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of case?
> The
> > result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> >
> >
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
> > string, is there still an advantage to using stored procedures (over
> > just executing it directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let
> > the SP build the Sql string. Either way though, the Select statement
> > will be a variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of
> > case? The result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> John Smith wrote:
>> Hey folks,
>> I've got a select statement that gets built dynamically in code.
>> If what I pass to a stored procedure is nothing but a string that
>> is a select statement, and then have the stored procedure execute
>> that string, is there still an advantage to using stored procedures
>> (over just executing it directly from code).
>> The other possibility is to pass other parameters to the SP and let
>> the SP build the Sql string. Either way though, the Select
>> statement will be a variable string that gets executed.
>> Is there still a case for using stored procedures in this type of
>> case? The result set size will range from 1 record to 50,000
>> records.
>> Thanks!
>> Having the SP build the SQL is fine. Just use sp_executesql to
>> execute the call, and if the query has parameters, then use the
>> parameter feature of sp_executesql to define them and pass them to
>> the SQL statement. That way, if similar statements get executed, the
>> plan will already be in cache.
>> Performance will be fine... assuming your queries are tuned properly.
>>
>> --
>> David G.
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
--
David G.