In this sp i assign variable with in the select q

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

In this sp i assign variable with in the select q

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-18 : 02:24:33

CREATE PROCEDURE CALTSearchLeadReport
@SDate DateTime,
@EDate DateTime,
@AgentID VarChar(50),
@SchoolID VarChar(50)
AS

DECLARE
@NODISPLAY decimal,
@LEADCOUNT decimal

SET @SDate = Convert(Varchar(15),@SDate,101)
SET @EDate = Convert(Varchar(15),@EDate,101)

create table #temp ( Sorty int, UserID varchar(50),
SchoolName varchar(50), NoDisplay decimal,LeadCount decimal,Convertion decimal(9,2))

IF(@AgentID='-1' AND @SchoolID='-1')

BEGIN

INSERT INTO #temp
SELECT
0,
LI.AgentID,
LI.SchoolName,
set @NODISPLAY = (select count(*) from LiveTransferHistoryLog LH where Convert(varchar(15),LH.CreatedDateTime,101) between @SDate and @EDate AND LH.AGENTID=LI.AGENTID),
set @LEADCOUNT =(select count(*) from LiveTransferLeadInfo LL where Convert(varchar(15),LL.CreatedDateTime,101) between @SDate and @EDate AND LL.AGENTID=LI.AGENTID),
Convertion =CASE @NODISPLAY WHEN 0 THEN 0 ELSE @LEADCOUNT*@NODISPLAY END
FROM LiveTransferHistoryLog LI
left join LiveTransferLeadInfo LT on LT.Campusid=LI.Campus_Id
inner join ASRMaster A on A.AgentID=LI.AgentID and A.isActive=1
inner join TimeFrame F on F.Campus_Id=LT.campusid and F.isDeleted=0
WHERE Convert(varchar(15),LI.CreatedDateTime,101) between @SDate and @EDate
GROUP BY LI.AgentID,LI.SchoolName
ORDER BY LI.AgentID,LI.SchoolName

SELECT * FROM #TEMP

END

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-18 : 02:25:58

In the above sp i assign variable with in the select query but it is throughing error.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 02:33:35

you cant use SET statement inside select.you need to do like below

CREATE PROCEDURE CALTSearchLeadReport 
@SDate DateTime, 
@EDate DateTime, 
@AgentID VarChar(50), 
@SchoolID VarChar(50) 
AS 

DECLARE 
@NODISPLAY decimal, 
@LEADCOUNT decimal 

SET @SDate = Convert(Varchar(15),@SDate,101) 
SET @EDate = Convert(Varchar(15),@EDate,101) 

create table #temp ( Sorty int, UserID varchar(50), 
SchoolName varchar(50), NoDisplay decimal(prec,scale),LeadCount decimal(prec,scale),Convertion decimal(9,2)) 

IF(@AgentID='-1' AND @SchoolID='-1') 

BEGIN 

INSERT INTO #temp 
SELECT 
0, 
LI.AgentID, 
LI.SchoolName, 
(select count(*) from LiveTransferHistoryLog LH where Convert(varchar(15),LH.CreatedDateTime,101) between @SDate and @EDate AND LH.AGENTID=LI.AGENTID), 
(select count(*) from LiveTransferLeadInfo LL where Convert(varchar(15),LL.CreatedDateTime,101) between @SDate and @EDate AND LL.AGENTID=LI.AGENTID), 
Convertion =CASE @NODISPLAY WHEN 0 THEN 0 ELSE @LEADCOUNT*@NODISPLAY END 
FROM LiveTransferHistoryLog LI 
left join LiveTransferLeadInfo LT on LT.Campusid=LI.Campus_Id 
inner join ASRMaster A on A.AgentID=LI.AgentID and A.isActive=1 
inner join TimeFrame F on F.Campus_Id=LT.campusid and F.isDeleted=0 
WHERE Convert(varchar(15),LI.CreatedDateTime,101) between @SDate and @EDate 
GROUP BY LI.AgentID,LI.SchoolName 
ORDER BY LI.AgentID,LI.SchoolName 

select @NODISPLAY =NoDisplay ,@LEADCOUNT =LeadCount 
FROM #TEMPSELECT * FROM #TEMP

END

i see couple of problems with above code
1. you've not specified precision and scale for decimal fields
2. You will be having more than 1 row in #TEMP. so it will obviously have more than 1 values for counts (Nodisplay & LeadCount). Assigning them to variable as in above causes them to store only a single value (random one based on order of retrieval from #TEMP) out of all values. is this really what you're looking for? Whereas if you've only a single value in #TEMP, then it will work as you wish.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources