Click here to Skip to main content
15,889,767 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I created that stored procedure and it works well when i passes thoes parameters ,Except @Temp_Emp_Name parameter when i passes it it doesn't work and no data pops up

Any Help Pls ,

Code:

SQL
ALTER proc [dbo].[SP_Rprt_TempEmp]
@Temp_File_No int,
@Qualification int,
@Temp_Emp_Name nvarchar(200),
@Temp_BirthDate_From  date,
@Temp_BirthDate_To date,
@Temp_Dist int,
@Temp_Disabled  bit
as
begin


SELECT    dbo.Qualifications.Qualification, dbo.Temp_Emp.Temp_Emp_Name, dbo.Temp_Emp.Temp_File_No, dbo.Temp_Emp.Temp_BirthDate, 
                      dbo.Temp_Emp.Disabled, dbo.Temp_Emp.Temp_Job, dbo.Districts.District
FROM         dbo.Qualifications INNER JOIN
                      dbo.Temp_Emp ON dbo.Qualifications.Qualification_ID = dbo.Temp_Emp.Qualification_ID INNER JOIN
                      dbo.Districts ON dbo.Temp_Emp.District_ID = dbo.Districts.District_ID  

WHERE
( Temp_Emp.Temp_File_No=@Temp_File_No  or  @Temp_File_No=0 ) and
( Temp_Emp.Qualification_ID=@Qualification or @Qualification=0 ) and
( Temp_Emp.Temp_Emp_Name like N'%@Temp_Emp_Name%'  or @Temp_Emp_Name='null'  ) and

(( Temp_Emp.Temp_BirthDate>=@Temp_BirthDate_From and Temp_Emp.Temp_BirthDate<=@Temp_BirthDate_To )
OR (@Temp_BirthDate_From='1000/01/01' and @Temp_BirthDate_To='1000/01/01') )  and

( Temp_Emp.District_ID=@Temp_Dist or @Temp_Dist=0 ) and 
( Temp_Emp.[Disabled]=@Temp_Disabled )   


end
Posted
Updated 5-Nov-12 22:50pm
v2

:sigh:
N'%@Temp_Emp_Name%' is a string.
Perhaps if you used N'%' + @Temp_Emp_Name + N'%' instead?
 
Share this answer
 
Comments
Reem Moh 6-Nov-12 5:00am    
Also doesn't work !!?
OriginalGriff 6-Nov-12 6:01am    
In what way? "It doesn't work" isn't very descriptive...
Reem Moh 6-Nov-12 6:55am    
wt u mean !!?
OriginalGriff 6-Nov-12 6:59am    
I mean that as an error description it is pretty meaningless. Perhaps if you tell us what inputs you have, what outputs you get, and what output you expect it might help. As would any error messages you get, or even the absence of error messages.

If you took your car to the garage and said "It don't work" you would have to expect it would take a lot longer to fix and cost a lot more, than if you had said "It is swerving to the left when I brake hard with the hazard warning lights on".
Reem Moh 6-Nov-12 6:59am    
N'%' + @Temp_Emp_Name + N'%' I tried that ..But also when i pass value to parameter no data pops up !!?
Please include following code after "begin" keyword.

Code:
SQL
If @Temp_Emp_Name='' Set @Temp_Emp_Name=null


Also please remove single quote from following statement
SQL
@Temp_Emp_Name='null'

Because when your use single quote in any sql statement then sql read it as a string. null and 'null' both not same.


i think it will be worked well.
 
Share this answer
 
Comments
Reem Moh 6-Nov-12 5:46am    
Also doesn't work , I donow where is the prob !!?
csharpbd 6-Nov-12 5:58am    
try using both solution OriginalGriff's (Solution-1) solution and my solution. if still doesn't work please provide your execution statement with value.
Reem Moh 6-Nov-12 6:51am    
I added single qoutes to 'null' ,Cuz i pass it as a string nd i know that .
the prob here that it cannot read the passed parameter ,any suggestions !!?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900