November 30, 2005 at 2:55 pm
Hi,
I'm new to SQL Server. Is it possible to pass a filter into stored procedure for query? I have a stored procedure as below
where I sent in these params as follow:
@filter= AND B.AuthCode=400070
@merchantID=941000099998
@sort = order by B.TransactionID
CREATE PROCEDURE proc_GetTxnForSearch
@filter varchar(100)=null, @merchantID varchar(15)=null, @sort varchar(100)=null
AS
declare @custID varchar(50)
SET NOCOUNT ON
set @custID = (select max (CustomerID) from Transactions where MerchantID=@merchantID + @filter)
if (@custID != 'VTEXEMPT')
begin
select A.*, B.* from customers A, transactions B where A.CustomerID=B.CustomerID and B.MerchantID=@merchantID + @filter + @sort
end
else
begin
select A.*, B.*
from customers A, transactions B
where B.TransactionID = A.RefTxnID and A.CustomerID=B.CustomerID and B.MerchantID=@merchantID + @filter + @sort
end
RETURN @@RowCount
GO
many thanks,
November 30, 2005 at 3:19 pm
Only by doing a dynamic SQL query using EXEC or EXEC sp_executesql. But then the query runs in another batch process, meaning it's the same as if you had issued a SELECT statement directly, than through a stored procedure. In other words, you lose the advantage of ownership chains and have to set permissions on the base tables explicitly.
K. Brian Kelley
@kbriankelley
December 22, 2005 at 11:34 am
There is another option. Here is how I like to write stored procs for pages such as searches that have optional fields. It is best shown with an example
Let's say you can search for a person by first name, last name, or title. All of the fields are in the person table
create procedure FindPeople
@Firstname varchar(30),
@LastName varchar(30),
@Title varchar(50)
AS
Select *
from person
where ((@FirstName is null) or (first_name = @FirstName))
and ((@LastName is null) or (last_name = @LastName))
and ((@Title is null) or (title = @Title))
You get the benefit or a stored execution plan. Plus if the query is actually complex you can change the sp to do the join slightly different if certain columns are null. For instance if they don't search for one of the columns you ay instead run a query that leaves out the largest table in the join.
I hope this helps and makes sense.
December 23, 2005 at 1:39 am
Here's another way
create procedure FindPeople
@Firstname varchar(30) = '',
@LastName varchar(30) = '',
@Title varchar(5) = ''
AS
Select title, first_name, surname
from person
where first_name LIKE ISNULL(@FirstName,'') + '%'
and last_name LIKE ISNULL(@LastName,'') + '%'
and title LIKE ISNULL(@Title,'') + '%'
Since the wildcard is at the end, the conditions are sargable.
Chris, beware of ORs in the where clause. If both sides aren't indexed (which is impossible in the case of a variable) then SQL will do a table/clustered index scan to satisfy the query. I ran yours and mine against the person table on my dev database (12000 records), searching for first name = 'Gail' and surname blank (returns 8 records)
Yours: 1074 reads, 18 ms (clustered index scan)
Mine: 26 reads, 2 ms (NC index seek, bookmark lookup)
Comparing the two using the execution plan, yours was 99.4% of the entire batch, mine 0.6%
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2005 at 1:45 am
And for dynamic sorts...
CREATE PROCEDURE SortedPerson
@SortField VARCHAR(30) = 'Surname'
AS
SELECT title, first_name, surname
FROM Person
ORDER BY CASE @SortField WHEN 'First_Name' THEN first_name
WHEN 'Title' THEN title
ELSE Surname
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply