using stored procedures; namely enhanced security. There is a security
implication using dynamic T-SQL in a stored procedure. Explicit permissions
(GRANT SELECT) will need to be applied to the tables referenced in your
stored procedure, for the it to run.
This therefore defeats one of the primary objectives of using stored
procedures in that they allow a higher level of security to be applied to
the backbone of your web application. By granting only EXECUTE permissions
to the app's database login you are not allowing any direct table level
permissions i.e. (select, update, delete or insert), instantly offering
better data protection.
Stephen
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 13 September 2004 20:30
To: SQL
Subject: RE: SP IF help
This is the only method that will work for running dynamic queries, but of
course there is a downside... the server can no longer precompile the
statements in the stored procedure, so you will lose all the benefits of
using a stored procedure. In other words, this stored procedure will run
slower than the others.
_____________________
Eric
_____________________
"You can tell the ideals of a nation by its advertisements."
-- Norman Douglas
"Jacob Cameron" <[EMAIL PROTECTED]>
09/10/2004 08:03 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: RE: SP IF help
I always just assign to a nvarchar(), and then append the string with the
condition, then run the query at the end, at least that's how I do it:
DECLARE @selectString NVARCHAR(100)
set @selectString='select ID, name, orderNumb from '
if @tablename = 1
set @[EMAIL PROTECTED] + 'bob'
else
set @[EMAIL PROTECTED] + 'frank'
EXEC sp_executesql @[EMAIL PROTECTED]
Jacob
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
