November 4, 2009 at 3:24 am
hi
i have problem with the dynamic sql. i am getting null values after execution, i think this happens due to data values. because i removed input parameters of datatime values and also from where condition. it is working perfectly. but when using datatime values, i am getting null values and the print statement also didnt show any thing.
can any one guide me on this one.its very urgent
create proc test
(@empid varchar(30)
,@fromdate datetime
,@todate datetime
)
as
begin
declare @dept varchar(50),@sql nvarchar(max)
select @dept=deptname from emp_dept where empid= @empid;
set@sql=@sql+' select firstname,lastname,sal from emp where dept in ('+@dept+')
and convert(varchar,joindate,101) between'''+ cast(@fromdate as varchar)+''' and '''+cast(@todate as varchar) +'''';
print @sql
execute(@sql);
end
-- execute test '123','05/12/2008','08/20/2009'
November 4, 2009 at 3:48 am
you should handle the case when there is no dept for an employee.
Just to make this work, in your d-sql replace @dept with
ISNULL(@dept, '')
your print would work!
---------------------------------------------------------------------------------
November 4, 2009 at 4:17 am
It is unclear if you are getting results and the results include the null as the value or in your procedure the variable @sql is getting the null value. If @sql is getting the null value then it is because you concatenating 2 strings and at least one of them is null. When you concatenating any string with null, you’ll get null as the results (you can change this behavior with by modifying the session set option SET CONCAT_NULL_YIELDS_NULL or by modifying this on the database’s level, but it is recommended that you’ll leave it the way it is). In that case you should change the procedure’s logic as Nabha pointed out. If you are getting a null value in the results, then most chances that this is due to your data.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 4, 2009 at 5:03 am
rockingadmin - Pleas post your question only once instead of posting it few times in different forums.
For anyone that wants to help rockingadmin pleas use the thread at http://qa.sqlservercentral.com/Forums/Topic813032-145-1.aspx%5B/url%5D
Instead of this one.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply