August 3, 2004 at 7:34 am
I would like to build a stored procedure that has three input parameters. These parameters would make up all the info I need to create a dynamic where statment for my query. Here is the concept anybody have any suggestions on how to do this. Thanks
CREATE PROCEDURE GetAssetsWithCriteria
@CriteriaColumn VarChar(15),
@CriteriaOperator VarChar(10),
@Criteria VarChar(255)
AS
SELECT
Assets.OrderId,
Assets.AssetId,
Assets.AssetStatus,
Products.Description,
Assets.SerialNumber,
Assets.WorkstationId,
Associates.LastName + ', ' + Associates.FirstName AS AssociateName,
Assets.InstallDate,
Assets.Comments
FROM
Assets
LEFT OUTER JOIN Associates ON Assets.AssociateId = Associates.AssociateId
LEFT OUTER JOIN Products ON Assets.ProductId = Products.ProductId
WHERE @CriteriaColumn @CriteriaOperator @Criteria
August 3, 2004 at 8:31 am
You *could* use a CASE statement if you have a finite number of columns that can be passed to the stored procedure, but really you are looking at using dynamic SQL here. The command you want is EXEC. You can't use the other dynamic SQL statement (sp_executesql) as you wish to dynamically choose the column to query against.
If I was you, I'd think about redesigning this to avoid the use of dynamic SQL - it introduces all kinds of problems when in actual fact I guess you are trying to simplify things by coming up with this stored procedure in the first place.
One problem is that you have to give the user select permissions against the actual table. This may not be a problem if this is the case anyway.
Another problem is that if any of the 3 parameters are entered by users, they could theoritically inject their own SQL into this code. Imagine @CriteriaColumn = '1=1;', now imagine if @Criteria = 'drop table Assets' or any other such random query and you should be able to see the problems you could have.
If you don't have to worry about any of this, then dynamic sql might be fine for you - check out EXECUTE (EXEC) in SQL books on line for more info.
Dave Hilditch.
August 3, 2004 at 9:14 am
i agree, if your security settings aren't 100% correct you could get in real trouble with this.
but in basic form your sp would have the following
declare @strsql nvarchar(1000)
set @strsql='select xyz from abc where'+@1+@2+''''+@3+''''
exec sp_executeSQL @strsql
MVDBA
August 5, 2004 at 5:55 am
Thanks all for the input, the security issue was resolved by using dropdownlists for the coulmn, operator, and value arguments so the user is limited to those lists to create the where statment. I ended up using the sp_executeSQL stored procedure but built the entire sqlStatmnet in my VB.Net app and passed the entire sql statment to my stored procedure instead of the three pieces of the where clause. Thanks again for you input!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply