"plan9" <pl***@discussions.microsoft.com> wrote in message
news:F0C331BD-C02B-4A66-AA60-A817D2B7CACB@microsoft.com...
> Hello
>
>
> I'm trying to use a cursor and some dynamic sql to updtate a table, but
I'm
> having a error
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'periodo5'.
>
>
> the code....
>
>
> --declare @jp_temp2 as  int
> declare @sub_linhas as int
> declare @jp_colA as varchar(50)
> declare @jp_count as int
> declare @teste_soma_column as varchar(200)
> declare @teste_soma_where as varchar(50)
> declare @jperiodo as decimal(23,3)
> set @jperiodo= 0
> set @teste_soma_where= 'T. Juro Periodo'
> --set @jp_temp2 = 1
>
>
> print 'testing......'
>
> DECLARE teste_soma_temp CURSOR FOR
> SELECT column_NAME FROM INFORMATION_SCHEMA.COLUMNS where
> table_name='CONTENC' and SUBSTRING(column_NAME, 1, 7) = 'periodo'
>
> OPEN teste_soma_temp --abrir cursor
>
> FETCH NEXT FROM teste_soma_temp  INTO @teste_soma_column
>
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> print 'testing2......'
>
>
>
> declare @q nvarchar(200)
> set @q='set @jperiodo = (select sum(' +@teste_soma_column+ ') from
> CONTENC where contracto = ''' + @cont_descCursor + ''')'
> exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
> output
> --select @jperiodo
>
> print
>
'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
ssssssssssssssssssss'
> print '@teste_soma_column'
> print @teste_soma_column
> print
>
'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
ssssssssssssssssssss'
>
>
> exec('update CONTENC set '''  + @teste_soma_column + '''  = ' +
> @jperiodo +'  where contracto =  ''' + @cont_descCursor + ''' and tipo_doc
=
> ' + @teste_soma_where )
>
>
>
>
> FETCH NEXT FROM teste_soma_temp INTO  @teste_soma_column
> END
>
>
> CLOSE teste_soma_temp
> DEALLOCATE teste_soma_temp
>
>

"Jim Underwood" wrote:

> Out of curiosity, why are you using dynamic SQL for this anyway?  why don't
> you use straight SQL and just specify the column names?  It would greatly
> simplify your code and eliminate most of these errors.
>
> Also, it looks like you have a SET clause without an UPDATE clause, but the
> dynamic SQL makes it hard to tell exactly what the code is..
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:F0C331BD-C02B-4A66-AA60-A817D2B7CACB@microsoft.com...
> > Hello
> >
> >
> > I'm trying to use a cursor and some dynamic sql to updtate a table, but
> I'm
> > having a error
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'periodo5'.
> >
> >
> > the code....
> >
> >
> > --declare @jp_temp2 as  int
> > declare @sub_linhas as int
> > declare @jp_colA as varchar(50)
> > declare @jp_count as int
> > declare @teste_soma_column as varchar(200)
> > declare @teste_soma_where as varchar(50)
> > declare @jperiodo as decimal(23,3)
> > set @jperiodo= 0
> > set @teste_soma_where= 'T. Juro Periodo'
> > --set @jp_temp2 = 1
> >
> >
> > print 'testing......'
> >
> > DECLARE teste_soma_temp CURSOR FOR
> > SELECT column_NAME FROM INFORMATION_SCHEMA.COLUMNS where
> > table_name='CONTENC' and SUBSTRING(column_NAME, 1, 7) = 'periodo'
> >
> > OPEN teste_soma_temp --abrir cursor
> >
> > FETCH NEXT FROM teste_soma_temp  INTO @teste_soma_column
> >
> >
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> > print 'testing2......'
> >
> >
> >
> > declare @q nvarchar(200)
> > set @q='set @jperiodo = (select sum(' +@teste_soma_column+ ') from
> > CONTENC where contracto = ''' + @cont_descCursor + ''')'
> > exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
> > output
> > --select @jperiodo
> >
> > print
> >
> 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
> ssssssssssssssssssss'
> > print '@teste_soma_column'
> > print @teste_soma_column
> > print
> >
> 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
> ssssssssssssssssssss'
> >
> >
> > exec('update CONTENC set '''  + @teste_soma_column + '''  = ' +
> > @jperiodo +'  where contracto =  ''' + @cont_descCursor + ''' and tipo_doc
> =
> > ' + @teste_soma_where )
> >
> >
> >
> >
> > FETCH NEXT FROM teste_soma_temp INTO  @teste_soma_column
> > END
> >
> >
> > CLOSE teste_soma_temp
> > DEALLOCATE teste_soma_temp
> >
> >
>
>
>

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:3C8B582F-4D35-41EB-A323-A8F588896D90@microsoft.com...
> Hello again
>
> it's a report the number of periods or columns of this report depends on a
> few options that users choose influencing directly the number of columns,
> this is the reason why the columns are dynamic. I have a table that has
same
> date periods and for each date period a percentage. the periods are user
> inputed.
>
> Each period are organized as interests family with a user entered name.
>
> What is done is to calculate the interests for some invoices in the
periods
> of time entered by the user.
>
> So I have no way to statically create a report table with the calculations
> because I have no way to predict wich family will be choose, and because
each
> number of periods in each family vary I need to recur to dynamic sql.

>
>

"Jim Underwood" wrote:

>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:3C8B582F-4D35-41EB-A323-A8F588896D90@microsoft.com...
> > Hello again
> >
> > it's a report the number of periods or columns of this report depends on a
> > few options that users choose influencing directly the number of columns,
> > this is the reason why the columns are dynamic. I have a table that has
> same
> > date periods and for each date period a percentage. the periods are user
> > inputed.
> >
> > Each period are organized as interests family with a user entered name.
> >
> > What is done is to calculate the interests for some invoices in the
> periods
> > of time entered by the user.
> >
> > So I have no way to statically create a report table with the calculations
> > because I have no way to predict wich family will be choose, and because
> each
> > number of periods in each family vary I need to recur to dynamic sql.
>
> This sounds very complicated, and I have to admit that I really don't
> understand it.  Could you give us DDL and some sample data, along with
> desired results?
>
> See this link for details...
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> >
> > I didn't understod your last paragraph, the update is the last instruct.
>
> Ahhh...  I missed what you were doing there.  I was actually looking at the
> dynamic SQL precedding the update where you set the value of @jperiodo.
> >
> >
>
>
>

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:F0C331BD-C02B-4A66-AA60-A817D2B7CACB@microsoft.com...
> Hello
>
>
> I'm trying to use a cursor and some dynamic sql to updtate a table, but
> I'm
> having a error
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'periodo5'.
>
>
> the code....
>
>
> --declare @jp_temp2 as  int
> declare @sub_linhas as int
> declare @jp_colA as varchar(50)
> declare @jp_count as int
> declare @teste_soma_column as varchar(200)
> declare @teste_soma_where as varchar(50)
> declare @jperiodo as decimal(23,3)
> set @jperiodo= 0
> set @teste_soma_where= 'T. Juro Periodo'
> --set @jp_temp2 = 1
>
>
> print 'testing......'
>
> DECLARE teste_soma_temp CURSOR FOR
> SELECT column_NAME FROM INFORMATION_SCHEMA.COLUMNS where
> table_name='CONTENC' and SUBSTRING(column_NAME, 1, 7) = 'periodo'
>
> OPEN teste_soma_temp --abrir cursor
>
> FETCH NEXT FROM teste_soma_temp  INTO @teste_soma_column
>
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> print 'testing2......'
>
>
>
> declare @q nvarchar(200)
> set @q='set @jperiodo = (select sum(' +@teste_soma_column+ ') from
> CONTENC where contracto = ''' + @cont_descCursor + ''')'
> exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
> output
> --select @jperiodo

"Dean" wrote:

> There is something very wrong with your approach. But based on that little
> information you're giving to us, the best I can do is help you fix the code.
>
> See inline..
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:F0C331BD-C02B-4A66-AA60-A817D2B7CACB@microsoft.com...
> > Hello
> >
> >
> > I'm trying to use a cursor and some dynamic sql to updtate a table, but
> > I'm
> > having a error
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'periodo5'.
> >
> >
> > the code....
> >
> >
> > --declare @jp_temp2 as  int
> > declare @sub_linhas as int
> > declare @jp_colA as varchar(50)
> > declare @jp_count as int
> > declare @teste_soma_column as varchar(200)
> > declare @teste_soma_where as varchar(50)
> > declare @jperiodo as decimal(23,3)
> > set @jperiodo= 0
> > set @teste_soma_where= 'T. Juro Periodo'
> > --set @jp_temp2 = 1
> >
> >
> > print 'testing......'
> >
> > DECLARE teste_soma_temp CURSOR FOR
> > SELECT column_NAME FROM INFORMATION_SCHEMA.COLUMNS where
> > table_name='CONTENC' and SUBSTRING(column_NAME, 1, 7) = 'periodo'
> >
> > OPEN teste_soma_temp --abrir cursor
> >
> > FETCH NEXT FROM teste_soma_temp  INTO @teste_soma_column
> >
> >
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> > print 'testing2......'
> >
> >
> >
> > declare @q nvarchar(200)
> > set @q='set @jperiodo = (select sum(' +@teste_soma_column+ ') from
> > CONTENC where contracto = ''' + @cont_descCursor + ''')'
> > exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
> > output
> > --select @jperiodo
>
> Does this bit work OK now?
>
> > print
> > 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'
> > print '@teste_soma_column'
> > print @teste_soma_column
> > print
> > 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'
> >
> >
> > exec('update CONTENC set '''  + @teste_soma_column + '''  = ' +
> > @jperiodo +'  where contracto =  ''' + @cont_descCursor + ''' and tipo_doc
> > =
> > ' + @teste_soma_where )
>
>
> Seems like you have several problems here. There's no need for the extra
> quotes around @teste_soma_column - it's a column name, right? @jperiodo is
> decimal, and it needs to get converted to varchar before concating.
> @teste_soma_where is a varchar, and it needs quotes. I can't tell about the
> @cont_descCursor variable.
>
> But as a general rule, avoid using EXEC, and use sp_executesql instead.
> Something like this:
>
> declare @q nvarchar(200)
> set @q='update contec set '+@teste_soma_column+'=@jperiodo where
> contracto=@cont_descCursor and tipo_doc=@teste_soma_where'
> exec sp_executesql @q, N'@jperiodo decimal(23,3), @cont_descCursor
> varchar(50), @teste_soma_where varchar(50)', @jperiodo, @cont_descCursor,
> @teste_some_where
>
>
> Make sure you read the Erland's articles on dynamic SQL, as suggested by
> others.
>
> Dean
>
>
>

"plan9" wrote:

> Hello Dean,
>
> I don't want to abuse your insight help, but I have done your code and it
> doesn't do the update, neither any error and i've made prints of the
> variables and apparently they are correct..
>
>
> you are probably right, I probably I'am doing many things right, and I would
> appreciated an insight expertise like your posts, what information do you
> need, so that you can better judge and avaliate what is wrong.
>
> 
>
> "Dean" wrote:
>
> > There is something very wrong with your approach. But based on that little
> > information you're giving to us, the best I can do is help you fix the code.
> >
> > See inline..
> >
> > "plan9" <pl***@discussions.microsoft.com> wrote in message
> > news:F0C331BD-C02B-4A66-AA60-A817D2B7CACB@microsoft.com...
> > > Hello
> > >
> > >
> > > I'm trying to use a cursor and some dynamic sql to updtate a table, but
> > > I'm
> > > having a error
> > >
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near 'periodo5'.
> > >
> > >
> > > the code....
> > >
> > >
> > > --declare @jp_temp2 as  int
> > > declare @sub_linhas as int
> > > declare @jp_colA as varchar(50)
> > > declare @jp_count as int
> > > declare @teste_soma_column as varchar(200)
> > > declare @teste_soma_where as varchar(50)
> > > declare @jperiodo as decimal(23,3)
> > > set @jperiodo= 0
> > > set @teste_soma_where= 'T. Juro Periodo'
> > > --set @jp_temp2 = 1
> > >
> > >
> > > print 'testing......'
> > >
> > > DECLARE teste_soma_temp CURSOR FOR
> > > SELECT column_NAME FROM INFORMATION_SCHEMA.COLUMNS where
> > > table_name='CONTENC' and SUBSTRING(column_NAME, 1, 7) = 'periodo'
> > >
> > > OPEN teste_soma_temp --abrir cursor
> > >
> > > FETCH NEXT FROM teste_soma_temp  INTO @teste_soma_column
> > >
> > >
> > > WHILE @@FETCH_STATUS = 0
> > > BEGIN
> > > print 'testing2......'
> > >
> > >
> > >
> > > declare @q nvarchar(200)
> > > set @q='set @jperiodo = (select sum(' +@teste_soma_column+ ') from
> > > CONTENC where contracto = ''' + @cont_descCursor + ''')'
> > > exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
> > > output
> > > --select @jperiodo
> >
> > Does this bit work OK now?
> >
> > > print
> > > 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'
> > > print '@teste_soma_column'
> > > print @teste_soma_column
> > > print
> > > 'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'
> > >
> > >
> > > exec('update CONTENC set '''  + @teste_soma_column + '''  = ' +
> > > @jperiodo +'  where contracto =  ''' + @cont_descCursor + ''' and tipo_doc
> > > =
> > > ' + @teste_soma_where )
> >
> >
> > Seems like you have several problems here. There's no need for the extra
> > quotes around @teste_soma_column - it's a column name, right? @jperiodo is
> > decimal, and it needs to get converted to varchar before concating.
> > @teste_soma_where is a varchar, and it needs quotes. I can't tell about the
> > @cont_descCursor variable.
> >
> > But as a general rule, avoid using EXEC, and use sp_executesql instead.
> > Something like this:
> >
> > declare @q nvarchar(200)
> > set @q='update contec set '+@teste_soma_column+'=@jperiodo where
> > contracto=@cont_descCursor and tipo_doc=@teste_soma_where'
> > exec sp_executesql @q, N'@jperiodo decimal(23,3), @cont_descCursor
> > varchar(50), @teste_soma_where varchar(50)', @jperiodo, @cont_descCursor,
> > @teste_some_where
> >
> >
> > Make sure you read the Erland's articles on dynamic SQL, as suggested by
> > others.
> >
> > Dean
> >
> >
> >