January 27, 2009 at 7:19 am
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
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
what shall I do
January 27, 2009 at 7:21 am
You can't build the string to be executed inside the Exec command. Build it as a variable first, then execute it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 7:31 am
GSquared (1/27/2009)
You can't build the string to be executed inside the Exec command. Build it as a variable first, then execute it.
the problem not in the string it work well while I execute it alone
January 27, 2009 at 7:48 am
problem might be that your @z is declared as an int yet you're trying to set it as a string. if @z is supposed to be a character string then changing the set to following might help.
set @z='EXEC(''select count(distinct '+@y+') from AGEN'')'
January 27, 2009 at 7:48 am
That's fine. You just can't do it that way in the Exec command, like you have in your proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 7:50 am
iainthepitman (1/27/2009)
problem might be that your @z is declared as an int yet you're trying to set it as a string. if @z is supposed to be a character string then changing the set to following might help.set @z='EXEC(''select count(distinct '+@y+') from AGEN'')'
No. He's doing that part correctly. Setting an integer variable equal to Exec assigns the error code from the Exec command to the variable. That's a pretty normal thing to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 8:17 am
GSquared (1/27/2009)
iainthepitman (1/27/2009)
problem might be that your @z is declared as an int yet you're trying to set it as a string. if @z is supposed to be a character string then changing the set to following might help.set @z='EXEC(''select count(distinct '+@y+') from AGEN'')'
No. He's doing that part correctly. Setting an integer variable equal to Exec assigns the error code from the Exec command to the variable. That's a pretty normal thing to do.
thanx for the help but I need to load @z with out put of
EXEC('select count(distinct '+@y+') from AGEN')
and that not work when applying the modification which is
set @z='EXEC(''select count(distinct '+@y+') from AGEN'')'
give me this erro
Syntax error converting the nvarchar value 'EXEC('select count(distinct INST_NO) from AGEN')' to a column of data type int.
January 27, 2009 at 11:39 am
To do that, you're going to need to do something like this:
create table #Rows (
TableName varchar(100),
ColumnName varchar(100),
Rows int)
declare @SQL varchar(1000), @Table varchar(100), @Column varchar(100)
select @Table = 'dbo.MyTableName', @Column = 'MyColumnName'
select @SQL = 'select ''' + @Table + ''',''' + @Column + ''', count(distinct ' + @Column + ') from ' + @Table
insert into #Rows (TableName, ColumnName, Rows)
exec (@SQL)
Set up a cursor or whatever to step through the tables you want to select from and the columns you want from them. Or hard-code the table if you're just checking one of them.
The temp table will then have the rows data in it and you can run other processes on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply