error select top using stored procedure

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 2000 Forums

Transact-SQL (2000)

error select top using stored procedure

Author

Topic

Lumoz
Starting Member

2 Posts

Posted - 2005-01-08 : 01:26:39

could anyone help me with this problem, this stored procedure seems to work fine when i first compile it, but when i wanted to used it, it doesn't work. after i checked it, it seems that this proc cannot receive input data other than number! it even cannot identified "A" (from the sql syntax). i hope someone could help me with this, coz it really drives me confused... Thx in advance :)

CREATE procedure sp_akt_ambil_nimhs2
@Priod char(4),
@Kdsem char(1),
@Kdmtk char(5),
@Kelas char(5),
@a char(2),
@b char(2)
as
declare @vSQL varchar(2000)
set @vSQL = 'Select top ' + Cast(@a as varchar(2)) + ' Nimhs From [database_db].dbo.Transaksi_nilai_mahasiswa where Nimhs not in (Select top '
set @vSQL = @vSQL + Cast(@b as varchar(2)) + ' Nimhs From [database_db].dbo.Transaksi_nilai_mahasiswa where stsrc = "A" '
set @vSQL = @vSQL + 'and Priod = '+ Cast(@priod as varchar(4)) + ' '
set @vSQL = @vSQL + 'and kdsem = '+ Cast(@kdsem as varchar(1)) + ' '
set @vSQL = @vSQL + 'and kdmtk = '+ Cast(@kdmtk as varchar(5)) + ' '
set @vSQL = @vSQL + 'and kelas = '+ Cast(@kelas as varchar(5)) + ' Order by Nimhs) '
set @vSQL = @vSQL + 'and stsrc = "A" '
set @vSQL = @vSQL + 'and Priod = '+ Cast(@priod as varchar(4)) + ' '
set @vSQL = @vSQL + 'and kdsem = '+ Cast(@kdsem as varchar(1)) + ' '
set @vSQL = @vSQL + 'and kdmtk = '+ Cast(@kdmtk as varchar(5)) + ' '
set @vSQL = @vSQL + 'and kelas = '+ cast(@Kelas as varchar(5)) + ' Order by Nimhs'
execute (@vSQL)
go

the sample input:
exec sp_akt_ambil_nimhs2 '2004','1','ak424','03pwa','10','30'

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-08 : 02:28:32

e.g.,

set @vSQL = @vSQL + 'and kdmtk = '+ Cast(@kdmtk as varchar(5)) + ' '

should be

set @vSQL = @vSQL + 'and kdmtk = '''+ Cast(@kdmtk as varchar(5)) + ''' '

and so on...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-08 : 12:52:17

Use SET ROWCOUNT with a variable and a table variable. Get rid of the dynamic SQL. Then you won't have to worry about this whole crazy mess.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Lumoz
Starting Member

2 Posts

Posted - 2005-01-11 : 21:57:25

Thx for the advive, i really appreciate it :) but i think there is something funny about sql... it looked like, if you run this coding from query analyzer, this code wil not work, but if you compile it from enterprise manager, it work just fine...

but i guess that solve my problem, thx guys...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources