March 27, 2012 at 8:30 am
HI All ,
Below is my script:
USE [DevSalesSecondDrawer]
GO
/****** Object: StoredProcedure [Neg].[UsersCreateStaticsGet] Script Date: 03/27/2012 09:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [Neg].[UsersCreateStaticsGet]
(@UserID typeSalesID =null,
@StartDate date ,
@EndDate date
)
As
Begin
if LEN(@UserID) = 0
set @user-id = null
Select OwnerID,COUNT(*) as JobsCreated
into #jobsCount
from Neg.jobs
where (OwnerID = @user-id or @user-id is null ) and
(CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)
between CAST (CONVERT (CHAR(10),@StartDate,120)as Date) and CAST (CONVERT(CHAR(10),@EndDate,120)as Date))
group by OwnerID
select AlternateOwnerID as OwnerID, COUNT(*) as AlternatesCreated
into #AlternatesCount
From Neg.Alternates
where (AlternateOwnerID = @user-id or @user-id is null ) and
(CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)
between CAST (CONVERT(CHAR(10), @StartDate,120)as Date) and CAST( CONVERT (CHAR(10), @EndDate,120)as Date))
group by AlternateOwnerID
select CreatedByUser as OwnerID , COUNT(*) as ItemsCreated
into #ItemsCount
From neg.Items
where (CreatedByUser = @user-id or @user-id is null ) and
(CAST (CONVERT(CHAR(10),CreatedDate,120) as DateTime )
between CAST(CONVERT (CHAR(10),@Startdate,120) AS Date) and CAST(CONVERT (CHAR(10),@EndDate,120)as Date))
group by CreatedByUser
--Select * from #JobsCount
--Select * from #ItemsCount
--Select * from #alternatesCount
Select ISNUll (ISNULL(j.OwnerID, a.OwnerID), i.OwnerID )as OwnerID ,j.JobsCreated,a.AlternatesCreated,i.ItemsCreated
from #JobsCount J
full outer join #alternatesCount a
on j.OwnerID=a.OwnerID
full outer join #ItemsCount i
on a.OwnerID=i.OwnerID
--drop table #JobsCount
--drop table #Itemscount
--drop table #alternatesCount
End
the above stored procedure is running fine SSMS.But , When I am running from the reports. I am getting the error message.
Implicit conversion from data type sql_variant to date is not allowed. Use the CONVERT function to run this query. (.Net SqlClient Data Provider)
Please help with this.
March 27, 2012 at 8:56 am
You will need to post the DDL (CREATE TABLE statements) for the source tables used in your procedure. Also you will need to provice smaple data for those tables (as a series of INSERT INTO statements).
Also, provide the expected results based on the sample data.
Once we have all of that we should be able to assist you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply