Stored Procedure with optional parameters
-
Hi,
I have a table
create table StudentBankDetails (AccountID nvarchar(10) not null , Name nvarchar(255) not null , [Address] nvarchar(255), CurrentBalance Money , BankStatus nvarchar(10))
and values are
insert into StudentBankDetails Values
(101,'John','NewJersey', 500.00,'NotActive'),
(103,'Sarah','Virginia', 22200.00,'Active'),
(104,'Mary','Columbus', 15000.00,'Active'),
(105,'Christian','Virginia', 10000.00,'Active'),
(106,'Heidi','Texas', 8000.00,'Active'),
(107,'Thomas','NewJersey', 20000.00,'Active'),
(108,'Janice','Arkansas', 18000.00,'Active'),
(109,'Mike','Atlanta', 24500.00,'Active'),
(110,'Zou','Iowa', 15000.00,'Active'),
(111,'Brandon','Minnesota', 30000.00,'Active')
Now I need to a stored procedure to do a search through a table. I need to pass two parameters @AccountID and @Name to stored procedure, which are optional.
ex- execute spGetDetails 101 -- It should get all the details where AccountID = 101
Execute spGetDetails 'John' -- It should gel all the detials where Name = 'John'
for this I need to create only one stored procedure with two parameters. If I pass 101 to the stored procedure then it should fetch all the columns where AccountID = 101 likewise if I pass 'John' to the stored procedure it should fetch all the fields from the table where name = 'john'.
Is there a way to create a stored procedure that will handle this? Please help me out.
I have created a procedure, Please correct my procedure
alter procedure spGetDetails
(
@AccountID nvarchar(10)= null ,
@Name nvarchar(20) = null
)
as
begin
select * from StudentBankDetails
where AccountID = @AccountID or Name = @Name
end
-----
execution part
execute spgetdetails '101' -- If I execute this, it is working and I can able to see the records
execute spgetdetails 'john' -- But If i Execute this it fails and couldn't get the records.
Thanks in advance
-
Try the below query , even if you skip one parameter it simply uses that columnName during comparison
alter procedure spGetDetails
(
@AccountID nvarchar(10)= null ,
@Name nvarchar(20) = null
)
as
begin
select * from StudentBankDetails
where ISNULL(AccountID,0) = ISNULL(@AccountID,ISNULL(AccountID,0))
AND ISNULL(Name,'') = ISNULL(@Name,ISNULL(Name,''))
end
In case your resultset is huge then you might run in performance issues. If that's the case then you can use dynamic SQL as below
alter procedure spGetDetails
(
@AccountID nvarchar(10)= null ,
@Name nvarchar(20) = null
)
as
begin
DECLARE @SqlStmt nvarchar(1000)
SELECT @SqlStmt= 'select * from StudentBankDetails ' + ISNULL (CASE WHEN @AccountID IS NOT NULL AND @Name IS NOT NULL THEN ' WHERE AccountID = @AccountID AND Name= @Name'
WHEN @AccountID IS NOT NULL AND @Name IS NULL THEN ' WHERE AccountID = @AccountID '
WHEN @AccountID IS NULL AND @Name IS NOT NULL THEN 'WHERE Name= @Name' END,'')
EXEC sp_executesql @SqlStmt
end
-
Have a look at the first section of https://www.simple-talk.com/content/article.aspx?article=2280
And when you call a procedure with optional parameters, you need to specify the parameter name.
EXEC GetDetails @AccountID = '101'EXEC GetDetails @Name = 'John'And don't prefix object names with sp or fn or tbl or such.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Please encapsulate your code in IFCode markup. It makes it so much easier to read as it preserves formatting.
I would also not suggest the ISNULL in your WHERE clause, but more this:
CREATE PROCEDURE GetDetails (@AccountID nvarchar(10)= NULL,@Name nvarchar(20) = NULL)ASSELECT *FROM StudentBankDetails SDBWHERE (SDB.AccountID = @AccountID OR @AccountID IS NULLOR (SDB.AccountID IS NULL AND (@AccountID = 0)))-- Now do NAMEAND (SDB.[Name] = @Name OR @Name IS NULLOR (SDB.[Name] IS NULL AND (@Name = '')));Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Can you try this?
CREATE PROCEDURE dbo.GetDetails ( @AccountID NVARCHAR(10) = '', @Name NVARCHAR(20) = '' )ASSELECT *FROM StudentBankDetails SDBWHERE( SDB.AccountID = @AccountID OR SDB.AccountID IS NULL )OR( SDB.[Name] = @Name OR SDB.[Name] IS NULL )GOEXEC dbo.GetDetails @AccountID = N'101'EXEC dbo.GetDetails @Name = N'Heidi' -
The answers in the last two posts will work fine, but they have an inherent performance problem. Have a look at the first section of https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (12/20/2016)
The answers in the last two posts will work fine, but they have an inherent performance problem. Have a look at the first section of https://www.simple-talk.com/content/article.aspx?article=2280Getting a 404 on that link?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thom A (12/20/2016)
GilaMonster (12/20/2016)
The answers in the last two posts will work fine, but they have an inherent performance problem. Have a look at the first section of https://www.simple-talk.com/content/article.aspx?article=2280Getting a 404 on that link?
Strange, works fine for me.
It's a redirect to https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (12/20/2016)
Thom A (12/20/2016)
GilaMonster (12/20/2016)
The answers in the last two posts will work fine, but they have an inherent performance problem. Have a look at the first section of https://www.simple-talk.com/content/article.aspx?article=2280Getting a 404 on that link?
Strange, works fine for me.
It's a redirect to https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
That works
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply