November 3, 2009 at 9:41 am
hi
i have created a stored procedure in sqlserver 2008 that executes dynamic sql.
for ex:
create proc test
(@empid varchar(30)
,@fromdate date
,@todate date
)
as
begin
declare @dept varchar(50),@sql nvarchar(max)
select @dept=deptname from emp_dept where empid= @empid;
set@sql=' select firstname'+'lastname,sal from emp where dept in ('+@dept+')and convert(varchar,joindate,101) between'+ @fromdate+' and '+@todate;
execute(@sql);
end
i am getting errors related to date conversion
can any one help me on this
Thanks
Rock...
November 3, 2009 at 9:58 am
Hi,
Two steps required.
Firstly...
You need to cast the fromdate and todate parameters as varchar, using CAST(X AS VARCHAR) function. Reason being, when building dynamic code you are building up a string and appending parameters to it (the @sql variable) which you later execute, therefore whatever you append to the string also needs to be a string.
Secondly...
You need to add an extra quote mark in the dynamic sql so that the executable sql code contains a quote on either side of the "cast-ed" date values. You wouldn't need to do this if it were an integer but seeing as this is a string you will need to.
Please see my solution below... I re-arranged the code a bit to make it more readable for me, but it does the same thing.
Notice the extra quotation marks around the date parameters within the WHERE condition.
Matt is right, you should also include a PRINT statement so you can actually see the bad syntax.
declare @sql nvarchar(2000),
@dept varchar(200),
@fromdate datetime,
@todate datetime
select @fromdate = GETDATE(),
@todate = GETDATE(),
@dept = 'XYZ'
set @sql = ''
set @sql=@sql
+ 'select firstname, lastname, sal' + char(10)
+ 'from emp ' + char(10)
+ 'where dept in ('+@dept+')' + CHAR(10)
+ 'and convert(varchar,joindate,101) between '''+ cast(@fromdate as varchar)+''' and '''+ cast(@todate as varchar) +'''';
print @sql
THE OUTPUT OF WHICH IS....
select firstname, lastname, sal
from emp
where dept in (XYZ)
and convert(varchar,joindate,101) between 'Nov 3 2009 4:55PM' and 'Nov 3 2009 4:55PM'
Hope that helps.
:w00t:
November 3, 2009 at 12:47 pm
i have modified the proc as per Lewis Dowson
create proc test
(@empid varchar(30)
,@fromdate date
,@todate date
)
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'+char(30)
+'from emp'+char(10)
+' where dept in ('+@dept+')+char(30)
+'and convert(varchar,joindate,101) between'''+ cast(@fromdate as varchar)+''' and ''''+cast(@todate as varchar) +''';
print @sql
execute(@sql);
end
this works fine.why we need to append char(?) in the dynamic sql?
--------------------------
but y i am getting null values in the below sp's and print statement also not showing the query.
example2:
create proc test
(@empid varchar(30)
,@fromdate date
,@todate date
)
as
begin
declare @dept varchar(50),@sql nvarchar(max)
select @dept=deptname from emp_dept where empid= @empid;
set @sql='select name from t1 where name in ('+@dept+')
and convert(varchar,d,101) between '''+ cast(@fromdate as varchar)+''' and '''+ cast(@todate as varchar) +'''';
print @sql
execute(@sql);
i am getting null values
example3:
here i added OR condition also
ALTER proc [dbo].[test3]
(@id int
,@fromdt datetime
,@todt datetime
)
as
begin
--set @fromdt=GETDATE()
--set @todt=GETDATE()
declare @sql varchar(4000)
declare @var varchar(4000)
set @var=''
select @var=name from test
--print @var
set @sql=''
set @sql='select name from t1 where name in ('+@var+')
and convert(varchar,d,101) between '''+ cast(@fromdt as varchar)+''' and '''+ cast(@todt as varchar) +''' or id='+cast(@id as varchar(10));
PRINT @sql
execute(@sql)
end
i can not able to get values and also the print satement of that query.
what statements are wrong in both example2 and example3?
can any one guide me on this
thanks
ROCK..
November 4, 2009 at 2:44 am
Hi Rock,
CHAR(10) explanation
The CHAR(10) in the dynamic SQL is a carriage return. This creates a new line in the dynamic code so that when you print it, it is not all on one line. It's not important but helpful.
For example:
set @sql = 'select a,b,c' + char(10)
+'from letters'
WITH
select a,b,c (char 10 gives new line here)
from letters
WITHOUT CHAR(10) it would look like this....
select a,b,c from letters
---------------------------------------
NULL Values
The reason that you are returning NULL values and the PRINT statement is empty, is because one or more of the following parameters / variables is NULL.
@empid
@dept
@fromdate
@todate
When appending a variable or parameter to the dynamic code you must make sure that it is NOT NULL. If you append a NULL variable or parameter to the dynamic string, the whole dynamic string becomes NULL.
The code below shows this example.
select 'This will make the string ' + NULL + ' empty' as NullExample
Suggestion: Find the variable or parameter which is equal to NULL. Do this by putting the select statement outside of your dynamic code. Once you know which values are NULL you can fix the query by making sure they can not be null.
SELECT @empid, @dept, @fromdate, @todate
See example below.
create proc test
(@empid varchar(30)
,@fromdate date
,@todate date
)
as
begin
declare @dept varchar(50),@sql nvarchar(max)
select @dept=deptname from emp_dept where empid= @empid;
SELECT @empid, @dept, @fromdate, @todate
set @sql='select name from t1 where name in ('+@dept+')
and convert(varchar,d,101) between '''+ cast(@fromdate as varchar)+''' and '''+ cast(@todate as varchar) +'''';
print @sql
execute(@sql);
Try this and let me know if you still have any problems.
Lewis
November 4, 2009 at 4:31 am
hi Lewis Dowson
thank you very much.
this works fine. but i am getting inconsistent data like
execute test '5689','06/01/2008','10/10/2008'
but i am getting values from year 2004 to 2009. it is not according to the requirement.
I need to get values based on deptnames where joineddate between '06/01/2008' and '10/10/2008'. but i am getting inconsistent values
how can i resolve this one
Thanks
Rock...
November 4, 2009 at 4:48 am
Rock,
Ok, the problem is because the CAST function changes the format of the date from "01/01/2001" to "1 JAN 2001". Then when you try and use the value in the WHERE condition it is not compatible.
The solution is to change the CAST to a CONVERT for the dates. This way it becomes a varchar but keeps the same format for the WHERE condition.
Change the
Before: CAST(@fromdate as varchar)
After: CONVERT(VARCHAR, @fromdate, 101)
AND
Before: CAST(@todate as varchar)
After: CONVERT(VARCHAR, @todate, 101)
set @sql='select name from t1 where name in ('+@dept+')
and convert(varchar,d,101) between '''+ convert(varchar,@fromdate,101)+''' and '''+ convert(varchar,@todate,101) +'''';
Try this and let me know if it works correctly.
Lewis
November 4, 2009 at 6:39 am
Hi
i modified the query as you said
still data inconsistent. i think problem is, it has to chk for the deptname with that emp and joineddate between values.here deptname is a camma seperated value for each record.and may have multiple deptname seperated by camma in one reocrd and date comparision has to be done with every deptname in a record.
i think you got my point
Thanks
Rock
November 4, 2009 at 7:46 am
Hi,
If you want some more help then I will need you to create a example tables for the procedure.
I can not replicate your problem because I obviously don't have your data.
If you'd like more assistance then produce some table examples with some inserts so that I can replicate the exact behavior on my machine.
Lewis
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply