Error in Conditional Sql - SQL Server Forums

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)

Error in Conditional Sql

Author

Topic

Dajer
Starting Member

22 Posts

Posted - 2009-08-15 : 02:28:30

hi friends
I write a sp as follow.but when I execute it,has an error:
"Must declare the scalar variable "@Debtor"."
what's my problem?
plz.i'm in hurry.
my SP:


ALTER PROCEDURE sp_TarazeKol

@kolcd1 nvarchar(50),
@kolcd2 nvarchar(50),
@docnum1 nvarchar(50),
@docnum2 nvarchar(50),
@docdate1 nvarchar(13),
@docdate2 nvarchar(13)

AS
Declare @sql nvarchar(4000)
Declare @Creditor bigint,@Debtor bigint
		
SELECT @sql=
      'select Document.KolCode,Kol.KolName,@Debtor=sum(Document.Debtor),@Creditor=sum(Creditor)'
     if (@Debtor>@Creditor)
      begin
            SELECT @sql=@sql+',(@Debtor-@Creditor) as[DebtRemain]
      from Document d inner join Kol on d.KolCode=Kol.KolCode
      where d.KolCode between @kolcd1 and @kolcd2
      and DocNum between @docnum1 and @docnum2
      and DocDate between @docdate1 and @docdate2
      group by d.KolCode,KolName'
      end
     else if (@Debtor<@Creditor)
      begin
            SELECT @sql=@sql+',(@Debtor-@Creditor) as [CredRemain]
      from Document d inner join Kol on d.KolCode=Kol.KolCode 
      where d.KolCode between @kolcd1 and @kolcd2
      and DocNum between @docnum1 and @docnum2
      and DocDate between @docdate1 and @docdate2
      group by Document.KolCode,KolName'
      end

      Exec(@sql)

thx for ur Attention.

Sara Dajer

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-15 : 03:09:43

u cannot use the @Debtor,@Creditor variables in dynamic sql
use outside the sql statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:14:44

or use sp_executesql to execute dynamic sql where you can define variables

Dajer
Starting Member

22 Posts

Posted - 2009-08-15 : 03:17:27

could u explain it plz?where should I declare it?
what's its Sql Statement?
thx

Sara Dajer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:20:09

can i ask the need of dynamic sql here? wont this be enough?


ALTER PROCEDURE sp_TarazeKol

@kolcd1 nvarchar(50),
@kolcd2 nvarchar(50),
@docnum1 nvarchar(50),
@docnum2 nvarchar(50),
@docdate1 nvarchar(13),
@docdate2 nvarchar(13)

AS
		
select Document.KolCode,Kol.KolName,
case when sum(Document.Debtor)>sum(Creditor) then sum(Document.Debtor)-sum(Creditor) else null end as [DebtRemain],
case when sum(Document.Debtor)<sum(Creditor) then sum(Creditor)-sum(Document.Debtor) else null end as [CredRemain]
from Document d inner join Kol on d.KolCode=Kol.KolCode
where d.KolCode between @kolcd1 and @kolcd2
and DocNum between @docnum1 and @docnum2
and DocDate between @docdate1 and @docdate2
group by d.KolCode,KolName

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-15 : 03:25:42

[code]
try like this and check it once
SELECT @sql=
'select Document.KolCode,Kol.KolName,@Debtor=sum(Document.Debtor),@Creditor=sum(Creditor)'
if (@Debtor>@Creditor)
begin
SELECT @sql=@sql+','+(@Debtor-@Creditor)+' as[DebtRemain]
from Document d inner join Kol on d.KolCode=Kol.KolCode
where d.KolCode between '+@kolcd1+' and '+@kolcd2+'
and DocNum between '+@docnum1+' and '+@docnum2+'
and DocDate between '+@docdate1+' and '+@docdate2+'
group by d.KolCode,KolName'
end
else if (@Debtor<@Creditor)
begin
SELECT @sql=@sql+','+(@Debtor-@Creditor)+' as [CredRemain]
from Document d inner join Kol on d.KolCode=Kol.KolCode
where d.KolCode between '+@kolcd1+' and '+@kolcd2+'
and DocNum between '+@docnum1+' and '+@docnum2+'
and DocDate between '+@docdate1+' and '+@docdate2+'
group by Document.KolCode,KolName'
end

Exec(@sql)
[/code]

Dajer
Starting Member

22 Posts

Posted - 2009-08-15 : 03:28:15

thank you so much.
it worked.
I haven't seen this kind of Sql Statement ever and it was wonderful.

Sara Dajer

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-15 : 03:31:14

welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:35:31

quote:
Originally posted by Dajer

thank you so much.
it worked.
I haven't seen this kind of Sql Statement ever and it was wonderful.

Sara Dajer

did you still went for dynamic soln? i dont its necessary at all

Dajer
Starting Member

22 Posts

Posted - 2009-08-15 : 03:37:06

thx bklr.but it didn't worked again.
I really want to know where's the problem.
visakh16's statement worked.
thx for your attention.

Sara Dajer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:44:42

quote:
Originally posted by Dajer

thx bklr.but it didn't worked again.
I really want to know where's the problem.
visakh16's statement worked.
thx for your attention.

Sara Dajer

you cant pass local variables inside sql string as it will be out of scope at runtime. so either use sp_executesql which allows passing of variable to execute dynamic sql or dont use dynamic sql. i prefer latter as in your case there's no need of using dynamic sql at all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources