how can I solve that - 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 2000 Forums

SQL Server Development (2000)

how can I solve that

Author

Topic

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-01-27 : 08:34:17

I have a code when running it give this error messege (in sql server 2000)

Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'EXEC'.

this is a part of the code

[CODE]set nocount on
create table #DISTINCTC(
[name] nvarchar(120)
)
insert into #DISTINCTC SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'AGEN'
------------------------
select * from #DISTINCTC

declare Dist cursor for
select [name]from #DISTINCTC
open Dist
DECLARE @z int
declare @x nvarchar(120)
declare @y nvarchar(120)
fetch next from Dist into @x
while @@fetch_status=0
begin
set @y = @x;

set @z=EXEC('select count(distinct '+@y+') from AGEN')

if (@z <= 10)
begin
set nocount on
create table #DISTINCTC1(
DistinctVal nvarchar(4000)
)
insert into #DISTINCTC1 EXEC('select distinct '+@y+' from AGEN')
------------------------
declare Insertion cursor for
select * from #DISTINCTC1
open #DISTINCTC1
Declare @I nvarchar(4000)
FETCH NEXT FROM Insertion INTO @I
WHILE @@FETCH_STATUS = 0
begin
EXEC('insert into SATIS values("AGEN",'+@y+','+@I+')')
FETCH NEXT FROM Insertion INTO @I
end

end
fetch next from Dist into @x
end
close Insertion
Deallocate Insertion
close Dist
Deallocate Dist

drop table #DISTINCTC[/CODE]

what shall I do

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 08:38:55

you cant set the value of EXEC to variable. for that you need to use sp_executesql with an output parameter.

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-01-27 : 13:06:23

quote:
Originally posted by visakh16

you cant set the value of EXEC to variable. for that you need to use sp_executesql with an output parameter.

how can I use sp_executesql with an output parameter

I tried that but give me error
sp_executesql'select count(distinct '+@y+') from AGEN',@z output

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 13:09:59

see sample C in this link

http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources