Please help me out

I have written a sql procedure, i want to pass delared variable @.cnt in line131,char24
below is the PL Code:
CREATE PROCEDURE PL
@.dt varchar(10),
@.stock as bit --1/true for updated and 0/false for previous executed data
AS

set dateformat dmy
set nocount on

Declare @.sdate VARCHAR(10)
Declare @.clup as Decimal(18,2)
declare @.cnt as integer
DECLARE @.amt as decimal(18,2),@.gamt as decimal(18,2), @.damt as decimal(18,2), @.camt as decimal(18,2)

delete from temp_pl
delete from temp_bal

Select @.sdate = Convert(varchar(10),StartDate,103 ) From Param
exec show_trial @.dt
-- Now data is in temp_trial
if( @.Stock =1 )
begin
exec Closing @.sdate, @.dt
end

select party_nm as acc, dr_amt as Amtdr, cr_amt as Amtcr into #Tlist from temp_trial WHERE party_cd not in (150,152,151,38,36,33,32,35,34)
--select * from #tlist

SELECT Acc,AmtDr,AmtCr ,Type, odr into #tmpop FROM (
Select 'Opening Stock (UP)' as Acc ,'AmtDr'= case when oup >0 then oup else 0 end ,
'AmtCr'= case when oup <0 then abs(oup) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance
union
Select 'Opening Stock (Ex UP)' as Acc ,'AmtDr'= case when oex >0 then oex else 0 end ,
'AmtCr'= case when oex <0 then abs(oex) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance

union
Select 'Purchase (UP)' as Acc ,'AmtDr'= case when pup >0 then pup else 0 end ,
'AmtCr'= case when pup <0 then abs(pup) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Purchase (Ex UP)' as Acc ,'AmtDr'= case when pex >0 then pex else 0 end ,
'AmtCr'= case when pex <0 then abs(pex) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (UP)' as Acc , 'AmtDr' =case when sUp < 0 then abs(sup) else 0 END ,
'AmtCr' =case when sup > 0 then sup else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (Ex-UP)' as Acc , 'AmtDr' =case when sex < 0 then abs(sex) else 0 END ,
'AmtCr' =case when sex > 0 then sex else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance

union
Select 'Closing Stock(UP)' as Acc , 'AmtDr' =case when CUp < 0 then abs(cup) else 0 END ,
'AmtCr' =case when Cup > 0 then cup else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
union
Select 'Closing Stock(Ex-UP)' as Acc , 'AmtDr' =case when Cex < 0 then abs(cex) else 0 END ,
'AmtCr' =case when Cex > 0 then cex else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
)A

Select * into #tmpl from(
SELECT Acc, AmtDr, AmtCr ,Type ,odr
From (Select * from #tlist ) a INNER JOIN (Select party_nm,Ac_type as Type,odr From Party where Ac_type IN ('P','T') )
Acmast ON a.Acc = Acmast.Party_nm
UNION
select Acc,AmtDr,AmtCr ,Type , odr from #tmpop
) a

update #tmpl set Amtdr = abs(AmtCr) where AmtCr < 0
update #tmpl set AmtCr =abs( Amtdr) where AmtDr < 0
/*
--Gp
set @.clup = (Select 'GP' = case
when sum(AmtDr)>= sum(AmtCr) then (Sum(AmtDr) -Sum(AmtCr) )* -1

When sum(AmtDr) < Sum(AmtCr) then (Sum(AmtCr) -Sum(AmtDr) ) end From #tmpl where Type= 'T' )
*/
--TRADING A/C
SELECT Acc , AmtCr,type into #CT FROM #tmpL WHERE (AmtCr > 0 and Type ='T') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DT FROM #tmpL WHERE (AmtDr > 0 and Type ='T') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditT FROM #CT
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitT FROM #DT

Select IDENTITY(int, 1,1) AS sr_no, * INTO #TRADE1 from
(Select d.Acc as Accdr , d.AmtDr as AmtDr , C.acc as AccCr ,C.amtCr as AmtCr ,'T' as Type from
#debitT d Full outer join #creditT c on c.ids =d.ids
)A
insert into #trade1 values(null,0,null,0,'T') --Line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #trade1

select @.damt=sum(isnull(amtdr,0)) from #trade1
select @.camt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 values (null,@.damt,null,@.camt,'T')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 (accdr,amtdr,type) values('Gross Profit C/F to P&L',@.amt,'T')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
insert into #trade1 (acccr,amtcr,type) values('Gross Loss C/F to P&L',abs(@.amt),'T')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
end
insert into #trade1 values(null,0,null,0,'T') --Line after gross
insert into #trade1 values(null,@.gamt,null,@.gamt,'T')
insert into #trade1 values(null,0,null,0,'T') --Line after Trade gross
if @.amt>0
begin
insert into #trade1 (acccr,amtcr,type) values('Gross Profit B/F from P&L',@.amt,'P')
end
else if @.amt<0
begin
insert into #trade1 (accdr,amtdr,type) values('Gross Loss B/F from P&L',abs(@.amt),'P')
end
else
begin
insert into #trade1 values(null,null,null,null,'T')
end
Select * INTO #TRADE from #trade1 order by sr_no
------------------------------
--P/L A/C

SELECT Acc , AmtCr,type into #CP FROM #tmpL WHERE (AmtCr > 0 and Type ='P') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DP FROM #tmpL WHERE (AmtDr > 0 and Type ='P') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditP FROM #CP
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitP FROM #DP

select @.cnt=count(SR_NO)+1 from #trade

select IDENTITY (int,@.cnt,1) AS sr_no,accdr,amtdr,acccr,amtcr,type INTO #PL_TEMP1 from #trade t WHERE t.type='P'

insert into #pl_temp1 (accdr,amtdr,acccr,amtcr,type)
Select d.Acc ,d.AmtDr, C.acc ,C.amtCr as AmtCr ,'P' as Type from
#debitP d Full outer join #creditP c on c.ids =d.ids

delete from #trade WHERE type='P'
select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1

insert into #PL_TEMP1 values(null,0,null,0,'P') --line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1
insert into temp_bal values(@.amt)

select @.damt=sum(isnull(amtdr,0)) from #PL_TEMP1
select @.camt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 values (null,@.damt,null,@.camt,'P')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (accdr,amtdr,type) values('Net Profit C/F to Balance Sheet',@.amt,'P')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (acccr,amtcr,type) values('Net Loss C/F to Balance Sheet',abs(@.amt),'P')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
end
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after NET
insert into #PL_TEMP1 values(null,@.gamt,null,@.gamt,'P')
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after PL NET

Select * INTO #PL_TEMP from #PL_TEMP1 order by sr_no

INSERT INTO temp_PL
SELECT * FROM
(select * from #PL_TEMP
UNION
SELECT * FROM #TRADE
) a order by sr_no
GO

please help meDo not post your procedure. Just explain your problem in detail and post code in the pirticular error area.

please check this SP syntax - need more eyes!

when I try to create this SP I get: "incorrect syntax near @.MyResult"
I have tried INT and different variable names, but get same error.

CREATE PROCEDURE sp_IsValidLogon
@.UserName varchar(16),
@.Password varchar(16) ,
@.MyResult varchar(3) OUTPUT
As
if exists(Select * From User_Table
Where UserName = @.UserName
And
Password = @.Password)
begin
@.MyResult = 1
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@.UserName, @.Password)

declare @.totalFails int
Select @.totalFails = Count(*) From FailedLogons
Where UserName = @.UserName
And dtFailed > GetDate()-1

if (@.totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @.UserName

@.MyResult = 0
endThe answer is

SET @.MyResult = ...|||Thanks George,
That's sorted it.
I seem to have two threads in this forum now tho' :o sorry guys.|||No worries - I will remove the dupe :)

Place count in variable

Would like to know if this is even possible to do - using SQL Server 2000.
I am creating a procedure that truncates a table then inserts one row of dat
a.
In the third field I need to insert the count of another table.
I thought that this would work:
@.DetCount int = SELECT Count(*) FROM TableName
My question is can I assign a count(*) to a variable in a stored procedure?
I would then use the variable in the values clause of the insert statment.Hi Robert,
Yes - you can do that. You just need to wrap your Select Statement in
brackets.
e.g
Declare @.Count Int
Set @.Count = (Select count(*) From TableA)
Insert Into TableB (Col1)
Values (@.Count)
Select * From TableB
HTH
Barry|||Works perfect! Thanks a million.
"Barry" wrote:

> Hi Robert,
> Yes - you can do that. You just need to wrap your Select Statement in
> brackets.
> e.g
> Declare @.Count Int
> Set @.Count = (Select count(*) From TableA)
> Insert Into TableB (Col1)
> Values (@.Count)
>
> Select * From TableB
>
> HTH
> Barry
>|||The other option is to use the SELECT statement to assign values to a
variable:
SELECT @.Count = COUNT(*) FROM TableA
This is useful when setting the values of multiple variables at once,
e.g.:
SELECT @.Count = COUNT(*),
@.Avg = AVG(someColumn)
FROM TableA
HTH,
Stu

pivot table

I'm trying to extract some data from an ssas 2005 cube to build a report with ssrs 2005.

The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...

So I would group the column for the fiscal month and the row for the measure name or measure caption ... and put the measure value inside the matrix.

Like the following

month 1 month 2 measure 1 xxx xxx measure 2 xxx xxx measure 3 xxx xxx

To do that I should run a query to extract data in the following form ...

fiscal month mesaure name measure value month 1 measure 1 xxx month 1 measure 2 xxx month 1 measure 3 xxx month 2 measure 1 xxx month 2 measure 2 xxx month 2 measure 3 xxx

The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...

so any idea on how can I extract data from ssas in that form ?

Cosimo

Can you place an upper bound on the number of months you need to report? Also, how do you want your months to display from left to right -- most recent to least recent?|||

thank

I solved ... using a static group and a matrix control

Cosimo

PIVOT operator for variable number of transformations

Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:

SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt

this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.