November 6, 2007 at 6:19 am
Dear All,
Please give the solution for the following Scenario
How to execute stored procedure which is having where condition as parameter.
Ex: exec spRPT_INV_StockMovement '3d8ab0b2-3f43-4bda-8e44-dd7f7af18a32','2007-04-01','2007-10-01','item_id = ''e19d5eec-f3fc-4bdb-9fda-624ab2d821b0'''
In the above example first parameter is branch id, second parameter is from date, third parameter is to date and forth parameter is where condition.
I used Dynamic SQL for resolving this but I thing stored procedure may lose the property of Pre-Compile if i use Dynamic SQL .
Rather than Dynamic SQL is there any method for executing those type of where conditions kindly give me the solution as early as possible.
November 6, 2007 at 6:28 am
As long as your where condition column is also dynamically changed you would probably be left with no choice than dynamic sql.
Prasad Bhogadi
www.inforaise.com
November 6, 2007 at 6:47 am
I dont like it much but you could try something like this :
create procedure spRPT_INV_StockMovement
@branch_id uniqueidentifier,
@from_date datetime,
@to_date datetime,
@column_name nvarchar(255),
@filter_value nvarchar(255)
as
begin
select t.* from
MyTable t
where
t.branch_id = @branch_id
and
t._from_date >= @from_date
and
t.to_date <= @to_date
and
Convert(bit,
case
when @column_name = 'item_id' then
case
when t.item_id = @filter_value then 1 /* depending on the column type you may need to do some casting here*/
else 0
end
when @column_name = 'some_other_column' then
case
when t.some_other_column = @filter_value then 1 /* depending on the column type you may need to do some casting here*/
else 0
end
else 0
end
) = 1
end
exec spRPT_INV_StockMovement @branch_id='3d8ab0b2-3f43-4bda-8e44-dd7f7af18a32', @from_date='2007-04-01', @to_date='2007-10-01', @column_name='item_id', @filter_value='e19d5eec-f3fc-4bdb-9fda-624ab2d821b0'
November 6, 2007 at 7:12 am
Thanks for reply
I may not use only one filter column and only one value. I may use multi filter columns and values
November 6, 2007 at 7:29 am
....then dynamic SQL is your friend in this case...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 7:34 am
Absolutely if you are building a Where condition using a string builder based on multiple column selected then I would say you would need to opt for Dynamic SQL inorder accomplish the required functionality.
Prasad Bhogadi
www.inforaise.com
November 6, 2007 at 1:27 pm
Instead of building different WHERE clauses as parameters, can't you rather have different stored procedures with fixed parameters for the different types of search methods?
November 6, 2007 at 1:36 pm
May not be practical in scenario's where you have features like advanced searched across say a combination 15 fields with AND, OR and braces. I would rather prefer building a string than doing the logic inside the code to call different procedures based on the conditions selected.
Prasad Bhogadi
www.inforaise.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply