Query problem - 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)

Query problem

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-03-11 : 13:57:04

Gurus,

Here is My Proc

Create procedure dal
@batch varchar(5),@subcode varchar(10),@pass int out,@fail int out,@total int out

as
declare @marbat varchar(10)
declare @substat varchar(20)

set @marbat='mark'+@batch
set @substat=@subcode+'status'
execute('select @pass = count(*) from '+@marbat+' where '+@substat+'= '+''' Pass''')
execute('select @fail = count(*) from '+@marbat+' where '+@substat+'= '+''' Fail''')
set @total=@pass+@fail

This is My input list

declare @pass int
declare @fail int
declare @total int
exec dal '2004','IT1008',@pass out ,@fail out,@total out
select @pass
select @fail
select @total

I am getting the error

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@pass".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@fail".

I cant find out where is the error.. Am i wrong anywhere..?

Please help me
Thanks
Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 15:09:06

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-03-11 : 15:14:52

I have modified somehow. but i am not getting the result

Alter procedure dal
@batch varchar(5),@subcode varchar(10),@pass int out ,@fail int out,@total int out

as
declare @marbat varchar(10)
declare @substat varchar(20)
Declare @Sql nVarchar(1000),@result1 varchar(10)
Select @result1 = 'pass'
set @marbat='mark'+@batch
set @substat=@subcode+'status'

Declare @params nvarchar(4000), @params1 Varchar(1000)
Select @Sql = ' Select @out = Count(*) from '+ @marbat +' where '+@substat +' = '+''''+@result1+''''
Set @params = N'@result1 nVarchar(100) ,@out int'
Exec SP_ExecuteSQL @SQL, @params,@result1 = 'Pass',@out out
Select @Total = @Total+@pass

declare @pass int
declare @fail int
declare @total int
exec dal '2004','IT1008',@pass out ,@fail out,@total out
select @pass
select @fail
select @total

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-03-11 : 15:29:35

Problem resolved.

I got it.

Alter procedure dal
@batch varchar(5),@subcode varchar(10),@pass int out ,@fail int out,@total int out

as
declare @marbat varchar(10),
@substat varchar(20),
@passout Int,
@Failout Int,
@Sql nVarchar(1000),
@result1 varchar(10),
@result2 varchar(10),
@params nvarchar(4000)

Select @result1 = 'pass',@result2 = 'fail'
set @marbat='mark'+@batch
set @substat=@subcode+'status'

Select @Sql = ' Select @out = Count(*) from '+ @marbat +' where '+@substat +' = '+''''+@result1+''''
Set @params = N'@result1 nVarchar(100) ,@out int out '
Exec SP_ExecuteSQL @SQL, @params,@result1 = 'Pass',@out =@passout Output
Select @pass = @passout

Select @Sql = ' Select @out = Count(*) from '+ @marbat +' where '+@substat +' = '+''''+@result2+''''
Set @params = N'@result2 nVarchar(100) ,@out int out '
Exec SP_ExecuteSQL @SQL, @params,@result2 = 'Fail',@out =@failout Output
Select @Fail = @failout

Select @Total = @fail+@pass

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources