Click here to Skip to main content
12,403,856 members (64,726 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL database Parameter
I want to list all student details from the student table, but there is a parameter @Name where we have to pass the name of the student. But i want all students , what i have to pass as the parameter to list all students? , here i cant change the stored procedure..

 create proc ListStudents @Name varchar(20)
 as
 begin
 
select * from StudentDet where Name=@Name
end
Posted 16-Jun-13 20:36pm
Updated 16-Jun-13 20:39pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try this:
CREATE PROC ListStudents
    @Name varchar(20)=''
AS
BEGIN
    SELECT * FROM StudentDet WHERE Name LIKE ''+@Name+'%'
END
When you need the details of particular student you can pass the name. If you'll not pass name then it'll list the details of all students.
EXEC ListStudents 'Amit'--Will list the details of Amit
EXEC ListStudents -- Will list the details of all students


--Amit
  Permalink  
Comments
Rinshad Hameed 17-Jun-13 3:16am
   
@_Amy I can't change the SP is there anyway ? i mean which i can do from the codebehind?
_Amy 17-Jun-13 3:18am
   
You cannot. You'll have to change the SP.
--Amit
_Amy 17-Jun-13 3:20am
   
No, you can't do it using c# also.
Rinshad Hameed 17-Jun-13 4:10am
   
are you sure?
_Amy 17-Jun-13 4:13am
   
Hmmm, it's a nice doubt. Yes, I am sure. :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

CREATE PROC ListStudents
    @Name varchar(20)=''
AS
BEGIN
    SELECT * FROM StudentDet WHERE Name = Case When @Name = '' then Name Else Name End
END
 

 

EXEC ListStudents 'Anudeep' -- Will Return Student Detail Name = Anudeep
EXEC ListStudents '' -- Will Return All Student Detail 'You Desired Output'
  Permalink  
v3
Comments
Rinshad Hameed 17-Jun-13 4:29am
   
you have to give @Name after "Else" intead of Name right?
Rinshad Hameed 17-Jun-13 4:31am
   
Its a good solution but this is not what i need. I can't change the stored procedure. Above solution says that nothing i can do in the codebehind.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 17 Jun 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100