June 2, 2005 at 12:48 pm
Remi:
I agree that it would be nice if we could construct a static query and just pass in a delimited string to represent the list of keywords.
Unfortuncately, I still don't think your solution takes into account the case where there are also keywords for the proposal in the ProposalKeywords table other than the list of keywords that was passed in.
--Jeff
P.S. I've enjoyed our discussion, too. I have great respect for your skills.
June 2, 2005 at 1:22 pm
Let me make you a picture of the system tables :
SysObjects : keeps all tables, views, functions, constraints, defaults and so on.
SysColumns : keeps all the columns and parameter info for the tables, views, functions and procs.
So if I do a search for the columns names "name" and "id" I will get both the tables SysObjects and SysColumns (and obviously more) that have a combined count of 57 columns (read keywords here).
Where's the problem now?
June 2, 2005 at 5:35 pm
Remi:
I agree with you that there are many ways to "skin a cat". Here is another, related solution.
User Defined Function for Support
CREATE dbo.fnCountDelimiters(@sInput varchar(8000), @sDelimiter varchar(1))
RETURNS int
AS
BEGIN
DECLARE @iCount int, @sTempInput varchar(8000), @iFound int
SET @iCount = 0
SET @sTempInput = (SELECT LTRIM(RTRIM(@sInput)))
WHILE LEN(@sTempInput) > 0
BEGIN
SET @iFound = (SELECT CHARINDEX(@sDelimiter, @sTempInput))
IF @iFound > 0
BEGIN
SET @iCount = @iCount + 1
SET @sTempInput = (SELECT SUBSTRING(@sTempInput, @iFound + 1, LEN(@sTempInput) - @iFound))
END
ELSE
SET @sTempInput = ''
END
RETURN @iCount
END
GO
Main Routine
CREATE PROCEDURE PropsalsWithKeywords(@KeyWordIDs varchar(8000))
AS
BEGIN
CREATE TABLE #temp (ProposalID int, KeywordID int)
DECLARE @iCount int, @sSQL varchar(8000)
SET @iCount = (SELECT dbo.fnCountDelimiters(@KeyWordIDs, ','))
SET @sSQL = 'INSERT #temp SELECT ProposalID, KeywordID FROM ProposalKeywords WHERE KeywordID IN (' + @KeyWordIDs + ')'
EXEC(@sSQL)
SELECT * FROM Proposals
WHERE ProposalID IN (SELECT ProposalID FROM #temp GROUP BY ProposalID HAVING Count(*) = @iCount)
DROP TABLE #temp
END
GO
June 3, 2005 at 6:00 am
Dude you gotta STOP USING DYNAMIC SQL for such simple task. I strongly urge you to read this before you get hit by a sql injection attack (especially the first one) :
The Curse and Blessings of Dynamic SQL
Dynamic Search Conditions in T-SQL
Arrays and Lists in SQL Server
If you don't have time to read the whole thing, here's the resume : with dynamic sql,a user can TAKE CONTROL of the server and delete the other administrative logins, literally locking yourself out of the server, then if can literally take control of the rest of the compagny (db = most of the compagny). If he's not so smart then he can simply truncate all the tables.
If your resume up to date??????
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply