Why I cannot execute a stored procedure when I have exec permission on it?
A terrible thing! I denied ‘select’ permission to all the tables & views and granted the ‘exec’ permission to stored procedures to read & write data in SQL server. But when I tried to execute some SPs, it reported that I have no permission to select the table. What? I implemented the access control by SPs. If SQL Server has this surprising behavior of who has ‘select’ permission on table who can read the data regardless by ‘select * from’ SQL or by SP, my whole access control framework will be re-design & re-implement! What a terrible thing!
OK. I must understand that SQL Server could not be able to has this strange behavior. I opened the code of one SP and found that this one uses ‘spexecutesql’ to execute a composed dynamic SQL string to imp a order function. Perhaps SP read data by ‘spexecutesql’ has something different from normal ‘select’ command. Modify the code & try again. The permission deny report disappeared. Hoho, ‘sp_executesql’ is the prime criminal!
I found the reason, but how can I re-write the order sql code without ‘spexecutesql’? En, I use ‘spexecutesql’ to compose a sql string for the dynamic order condition and direction because we could not set varibles after ‘order by’ keyword. Google it first! And then, I got the content beblow from this page and found the solution!
Something I must pay attention to: different data type should be set into different case group, imp ‘ASC’ and ‘DESC’ will make the sql more complicated but this is the only method now, and etc. :P
*How do I use a variable in an ORDER BY clause? *
Often, we want to allow users to determine how their data will be ordered. So, let’s say we have a table like this:
CREATE TABLE blat
(
blatID INT,
hits INT,
firstname VARCHAR(3),
email VARCHAR(9)
)
GO
SET NOCOUNT ON
INSERT blat VALUES(1, 12, ‘bob’, ‘bob@x.com’)
INSERT blat VALUES(2, 8, ‘sue’, ‘sue@x.com’)
INSERT blat VALUES(3, 17, ‘pat’, ‘pat@x.com’)
INSERT blat VALUES(4, 4, ‘pam’, ‘pam@x.com’)
INSERT blat VALUES(5, 1, ‘jen’, ‘jen@x.com’)
INSERT blat VALUES(6, 3, ‘rod’, ‘rod@x.com’)
INSERT blat VALUES(7, 5, ‘nat’, ‘nat@x.com’)
INSERT blat VALUES(8, 19, ‘rob’, ‘rob@x.com’)
INSERT blat VALUES(9, 24, ‘jan’, ‘jan@x.com’)
INSERT blat VALUES(10, 0, ‘meg’, ‘meg@x.com’)
GO
The first thing people tend to try in T-SQL is the following:
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
SELECT * FROM blat ORDER BY @col
GO
However, this returns:
Server: Msg 1008, Level 15, State 1, Line 4
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Clearly, SQL Server doesn’t like variables in the ORDER BY clause. Well, we can just use dynamic SQL, right?
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
EXEC(‘SELECT * FROM blat ORDER BY ‘+@col)
GO
But dynamic SQL is not a very desirable solution. (See Erland’s text for some of the problems.)
And then we could always construct an ad hoc query to send to the database from ASP. But we all know what a bad idea that is, right?
So, are there other ways to skin this cat? Of course. Some people try this:
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
SELECT * FROM blat ORDER BY
IF @col = ‘firstname’ THEN firstname
IF @col = ’email’ THEN email
GO
But this returns:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword ‘IF’.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword ‘THEN’.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword ‘THEN’.
This is because you can’t put an IF / THEN statement within a query expression—IF is used for program flow. And yes, you can construct an example like this:
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
IF @col = ‘firstname’
SELECT * FROM blat ORDER BY firstname
IF @col = ’email’
SELECT * FROM blat ORDER BY email
GO
However, this isn’t going to be very fun if the number of columns is quite large, and if the SELECT query is complex, you probably don’t want to repeat it for every possible column. And we haven’t even introduced making the order direction (ASC/DESC) a parameter yet. :-)
Another workaround is to use a CASE expression in the ORDER BY clause. There are a couple of "rules" that you must follow. The most important is that all possible results of a CASE expression must be of the same datatype (or must have the correct data precedence to implicitly convert). So let’s try a small example, where only email or firstname are allowed.
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
IF @col IN (‘firstname’, ’email’)
SELECT * FROM blat
ORDER BY CASE @col
WHEN ‘firstname’ THEN firstname
WHEN ’email’ THEN email
END
ELSE
SELECT * FROM blat
GO
A rule you can take advantage of, in order to allow columns of different datatypes to be ordered conditionally, is that an ORDER BY step containing NULL is ignored. So, you can do this to separate conditionals based on each data type, without worrying about order taking precedence:
DECLARE @col VARCHAR(9)
SET @col = ‘hits’
SELECT * FROM blat
ORDER BY
CASE @col
WHEN ‘firstname’ THEN firstname
WHEN ’email’ THEN email
END,
CASE @col
WHEN ‘blatID’ THEN blatID
WHEN ‘hits’ THEN hits
END
GO
Now, what if you want to pass the ascending/descending order a variable as well? It gets a little more complicated, since the parser for T-SQL is very fussy about where that DESC keyword goes. Here is the example I came up with:
DECLARE @col VARCHAR(9), @dir VARCHAR(4)
SET @col = ‘hits’
SET @dir = ‘desc’
SELECT * FROM blat
ORDER BY
CASE @dir
WHEN ‘desc’ THEN
CASE @col
WHEN ‘firstname’ THEN firstname
WHEN ’email’ THEN email
END
END
DESC,
CASE @dir
WHEN ‘desc’ THEN
CASE @col
WHEN ‘blatID’ THEN blatID
WHEN ‘hits’ THEN hits
END
END
DESC,
CASE @dir
WHEN ‘asc’ THEN
CASE @col
WHEN ‘firstname’ THEN firstname
WHEN ’email’ THEN email
END
END,