September 2, 2008 at 3:57 am
Hi,
We are all aware of dynamic queries. for executing dynamic queries we use EXECUTE / sp_ExecuteSQL. but if we use dynamic queries in SP and we have security contraints we need to specify the permission for table or for SP [using EXECUTE AS Clause]
Query :
I have 1 table Product( ProductID, ProductName, Description, Price ). I have created 1 SP of retrieving records from product tables with input parameters... ProductID, ProductName, Description, Price which are nullable. It means user can search on any search parameter in where clause. Now the Code look like this
create procedure dbo.uspProductSel
(@ProductID INT, @ProductName VARCHAR(100), Description VARCHAR(255), Price MONEY)
As
begin
set nocount on
--- either i can write the query in this way
select * from product where (@ProductID is null or ProductID = @ProductID ......)
-- OR in this way
declare @sql varchar(max)
if @ProductID is not null
set @sql = 'ProductID = ' + cast(@ProductID as varchar(10))
if @ProductName is not null
set @sql =@sql + 'ProductName Like ' + @ProductName + '%' ......
in the 1st way i can execute the procedure directly; but in 2nd way i have to execute it as dynamic query.
Suppose SP only accepts WhereClause send by Application.
create procedure dbo.uspProductSel
(@WhereClause varchar(max) = null)
WITH EXECUTE AS OWNER
as
begin
declare @sql varchar(8000)
set @sql = 'select * from product ' + case when @WhereClause is null then ''
else 'where' + @WhereClause end
execute sp_executeSQL @sql
end
in this case if i use the dynamic query then i have to use EXECUTE clause as specified. How i avoid using dynamic query in this case
September 2, 2008 at 2:36 pm
You can use CASE Statement itself.
create procedure dbo.uspProductSel
(@ProductID INT, @ProductName VARCHAR(100), Description VARCHAR(255), Price MONEY)
As
set nocount on
select Col1,Col2,Col3, from product
where ProductID = Case When len(@ProductID) =0
Then ProductID
else @ProductID end
and ProductName = Case when len(@Productname) = 0
Then ProductName
else @ProductName END
and [Description] = Case when len(@Description) = 0
Then Description
else @Description END
and [Price ] = Case when len(@Price ) = 0
Then Price
else @Price END
Check if this works. I did not create a table to test the SQL Out. But I think this should work.
-Roy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply