|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
June 4th, 2004, 04:35 AM
|
|||
|
|||
Optional parameters in stored procedure
Hi,
I have created a stored proc with optional parameter as CREATE PROCEDURE SelectFromEmp_Test @EmpNo int = '%', @EmpName char(15) ='%', @DateOfBirth datetime = '%', @Salary int = '%' AS select EmpNumber,EmpName,DateOfBirth,Salary from EmpMaster where EmpNumber Like @EmpNo And EmpName Like @EmpName And DateOfBirth Like @DateOfBirth And Salary Like @Salary GO Now if I execute it on query analyaser as execute selectfromEmp_test 1000,'Name','6/17/1982',15000 then it runs but if i want to use some wildcard character, how to use them? and how to pass only 1st and 3rd parameter?
|
|
#2
June 4th, 2004, 09:24 AM
|
||||
|
||||
|
Example:
This will work if you allow NULL values in the fields. Code:
CREATE PROCEDURE TestProc ( @Param1 varchar(50) = NULL, @Param2 varchar(50) = NULL, @Param3 varchar(50) = NULL ) AS SELECT * FROM TestTable WHERE col1 = ISNull( @Param1,col1 ) AND col2 = ISNull( @Param2,col2 ) AND col3 = ISNull( @Param3,col3 )
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Optional parameters in stored procedure |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
