problem with select

microsoft.public.sqlserver.programming

30 Aug 2006 6:21 PM

plan9

Hello

I'm trying to do a select and I'm having a problem with it (code below)

               declare @teste_varchar2 as varchar(20)
               declare @teste_varchar as varchar(500)

    set @teste_varchar2 = "valor_fact"
    exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
@cont_descCursor)

What is odd with the above code is that if I use a similar code but not
dynamic sql it works.

select valor_fact from ##CONTENC where contracto = @cont_descCursor

30 Aug 2006 6:23 PM

Arnie Rowland

You can't use variables for object names in a SELECT.

You need to look at using Dynamic SQL for your task.

See:

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

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:8317700F-266C-4744-BD33-369814599AED@microsoft.com...
> Hello
>
> I'm trying to do a select and I'm having a problem with it (code below)
>
>
> declare @teste_varchar2 as varchar(20)
> declare @teste_varchar as varchar(500)
>
> set @teste_varchar2 = "valor_fact"
> exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> @cont_descCursor)
>
>
>
> What is odd with the above code is that if I use a similar code but not
> dynamic sql it works.
>
>
> select valor_fact from ##CONTENC where contracto = @cont_descCursor

30 Aug 2006 6:36 PM

plan9

is there any workaround? how can I do it with dynamic sql, or how should I
search for what terms, i'm a little lost,

Show quote

"Arnie Rowland" wrote:

> You can't use variables for object names in a SELECT.
>
> You need to look at using Dynamic SQL for your task.
>
> See:
>
> http://www.sommarskog.se/dynamic_sql.html
>
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:8317700F-266C-4744-BD33-369814599AED@microsoft.com...
> > Hello
> >
> > I'm trying to do a select and I'm having a problem with it (code below)
> >
> >
> > declare @teste_varchar2 as varchar(20)
> > declare @teste_varchar as varchar(500)
> >
> > set @teste_varchar2 = "valor_fact"
> > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> > @cont_descCursor)
> >
> >
> >
> > What is odd with the above code is that if I use a similar code but not
> > dynamic sql it works.
> >
> >
> > select valor_fact from ##CONTENC where contracto = @cont_descCursor
>
>
>

30 Aug 2006 6:41 PM

Arnie Rowland

The easiest and most robust option is to read through Elrond's article
(below) and use dynamic SQL, passing in the parameters.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:E1F613DA-B7FD-4C45-9544-D1CF71DB13DC@microsoft.com...
> is there any workaround? how can I do it with dynamic sql, or how should I
> search for what terms, i'm a little lost,
>
> "Arnie Rowland" wrote:
>
>> You can't use variables for object names in a SELECT.
>>
>> You need to look at using Dynamic SQL for your task.
>>
>> See:
>>
>> http://www.sommarskog.se/dynamic_sql.html
>>
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>>
>> "plan9" <pl***@discussions.microsoft.com> wrote in message
>> news:8317700F-266C-4744-BD33-369814599AED@microsoft.com...
>> > Hello
>> >
>> > I'm trying to do a select and I'm having a problem with it (code below)
>> >
>> >
>> > declare @teste_varchar2 as varchar(20)
>> > declare @teste_varchar as varchar(500)
>> >
>> > set @teste_varchar2 = "valor_fact"
>> > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = '
>> > +
>> > @cont_descCursor)
>> >
>> >
>> >
>> > What is odd with the above code is that if I use a similar code but not
>> > dynamic sql it works.
>> >
>> >
>> > select valor_fact from ##CONTENC where contracto = @cont_descCursor
>>
>>
>>

30 Aug 2006 6:54 PM

plan9

Hello Arnie

I began using Sql server a few months and had the need for dynamic sql since
the very start, I know that article, but because I'm a little novice with
sqlserver, i'm not sure where to look, I gave a look in the article and
unfortunatly didn't found any mention to how to use dynamic sql or variables
to indicate the column

Show quote

"Arnie Rowland" wrote:

> The easiest and most robust option is to read through Elrond's article
> (below) and use dynamic SQL, passing in the parameters.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:E1F613DA-B7FD-4C45-9544-D1CF71DB13DC@microsoft.com...
> > is there any workaround? how can I do it with dynamic sql, or how should I
> > search for what terms, i'm a little lost,
> >
> > "Arnie Rowland" wrote:
> >
> >> You can't use variables for object names in a SELECT.
> >>
> >> You need to look at using Dynamic SQL for your task.
> >>
> >> See:
> >>
> >> http://www.sommarskog.se/dynamic_sql.html
> >>
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "plan9" <pl***@discussions.microsoft.com> wrote in message
> >> news:8317700F-266C-4744-BD33-369814599AED@microsoft.com...
> >> > Hello
> >> >
> >> > I'm trying to do a select and I'm having a problem with it (code below)
> >> >
> >> >
> >> > declare @teste_varchar2 as varchar(20)
> >> > declare @teste_varchar as varchar(500)
> >> >
> >> > set @teste_varchar2 = "valor_fact"
> >> > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = '
> >> > +
> >> > @cont_descCursor)
> >> >
> >> >
> >> >
> >> > What is odd with the above code is that if I use a similar code but not
> >> > dynamic sql it works.
> >> >
> >> >
> >> > select valor_fact from ##CONTENC where contracto = @cont_descCursor
> >>
> >>
> >>
>
>
>

30 Aug 2006 6:59 PM

plan9

I've read Erland article again is the quotename() the right aprouch?

Show quote

30 Aug 2006 6:26 PM

plan9

I forgot to tell the error....sorry

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'e'.

Show quote

"plan9" wrote:

> Hello
>
> I'm trying to do a select and I'm having a problem with it (code below)
>
>
>                declare @teste_varchar2 as varchar(20)
>                declare @teste_varchar as varchar(500)
>
>     set @teste_varchar2 = "valor_fact"
>     exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> @cont_descCursor)
>
>
>
> What is odd with the above code is that if I use a similar code but not
> dynamic sql it works.
>
>
> select valor_fact from ##CONTENC where contracto = @cont_descCursor

30 Aug 2006 6:28 PM

Jim Underwood

try placing @cont_descCursor inside the quotes?

exec ('select ' +@teste_varchar2+ ' from ##CONTENC
where contracto = @cont_descCursor'

Also check out this link on dynamic SQL...
http://www.sommarskog.se/dynamic_sql.html

Show quote

30 Aug 2006 6:28 PM

Roy Harvey

To diagnose these sorts of problems, be sure to display the string you
are executing. So for:

> exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
>@cont_descCursor)

SELECT ('select ' + @teste_varchar2 +
' from ##CONTENC where contracto = ' + @cont_descCursor)

I think the result from that will make the problem obvious, but I
think it comes down to @cont_descCursor.

If @cont_descCursor is a number you need to convert it to a string:
CONVERT(varchar(12),@cont_descCursor)

If @cont_descCursor is a string you need to put quotes around it.
'''' + @cont_descCursor + ''''

Roy Harvey
Beacon Falls, CT

On Wed, 30 Aug 2006 11:21:02 -0700, plan9
<pl***@discussions.microsoft.com> wrote:

Show quote

>Hello
>
>I'm trying to do a select and I'm having a problem with it (code below)
>
>
>               declare @teste_varchar2 as varchar(20)
>               declare @teste_varchar as varchar(500)
>
>    set @teste_varchar2 = "valor_fact"
>    exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
>@cont_descCursor)
>
>
>
>What is odd with the above code is that if I use a similar code but not
>dynamic sql it works.
>
>
>select valor_fact from ##CONTENC where contracto = @cont_descCursor

30 Aug 2006 6:42 PM

plan9

The problem with cont_descCursor was one of the firsts debugs I made for that
I used those instructions:

select valor_fact from ##CONTENC where contracto = @cont_descCursor
(it works)

select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
(it works)

select valor_fact from ##CONTENC where contracto = "Limpeza X2"
(it works)

Show quote

"Roy Harvey" wrote:

> To diagnose these sorts of problems, be sure to display the string you
> are executing. So for:
>
> >    exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> >@cont_descCursor)
>
> SELECT ('select ' + @teste_varchar2 +
>         ' from ##CONTENC where contracto = ' + @cont_descCursor)
>
> I think the result from that will make the problem obvious, but I
> think it comes down to @cont_descCursor.
>
> If @cont_descCursor is a number you need to convert it to a string:
> CONVERT(varchar(12),@cont_descCursor)
>
> If @cont_descCursor is a string you need to put quotes around it.
> '''' + @cont_descCursor + ''''
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 30 Aug 2006 11:21:02 -0700, plan9
> <pl***@discussions.microsoft.com> wrote:
>
> >Hello
> >
> >I'm trying to do a select and I'm having a problem with it (code below)
> >
> >
> >               declare @teste_varchar2 as varchar(20)
> >               declare @teste_varchar as varchar(500)
> >
> >    set @teste_varchar2 = "valor_fact"
> >    exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> >@cont_descCursor)
> >
> >
> >
> >What is odd with the above code is that if I use a similar code but not
> >dynamic sql it works.
> >
> >
> >select valor_fact from ##CONTENC where contracto = @cont_descCursor
>

30 Aug 2006 6:50 PM

Jim Underwood

Ahhh... then in this particular case you might use:

exec ('select ' +@teste_varchar2+ ' from ##CONTENC
where contracto = ''' + @cont_descCursor + ''''

The two single quotes embed a single quote in your string, and enclose the
value of @cont_descCursor within quotes.

However, you really need to read Erland's article that Arnie and I have
linked. It explains a lot of things that you will need to know to use
dynamic SQL proficiently.

Show quote

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
> The problem with cont_descCursor was one of the firsts debugs I made for
that
> I used those instructions:
>
> select valor_fact from ##CONTENC where contracto = @cont_descCursor
> (it works)
>
> select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
> (it works)
>
>
> select valor_fact from ##CONTENC where contracto = "Limpeza X2"
> (it works)
>
>
> "Roy Harvey" wrote:
>
> > To diagnose these sorts of problems, be sure to display the string you
> > are executing. So for:
> >
> > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = '
+
> > >@cont_descCursor)
> >
> > SELECT ('select ' + @teste_varchar2 +
> >         ' from ##CONTENC where contracto = ' + @cont_descCursor)
> >
> > I think the result from that will make the problem obvious, but I
> > think it comes down to @cont_descCursor.
> >
> > If @cont_descCursor is a number you need to convert it to a string:
> > CONVERT(varchar(12),@cont_descCursor)
> >
> > If @cont_descCursor is a string you need to put quotes around it.
> > '''' + @cont_descCursor + ''''
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
> > <pl***@discussions.microsoft.com> wrote:
> >
> > >Hello
> > >
> > >I'm trying to do a select and I'm having a problem with it (code below)
> > >
> > >
> > >               declare @teste_varchar2 as varchar(20)
> > >               declare @teste_varchar as varchar(500)
> > >
> > > set @teste_varchar2 = "valor_fact"
> > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = '
+
> > >@cont_descCursor)
> > >
> > >
> > >
> > >What is odd with the above code is that if I use a similar code but not
> > >dynamic sql it works.
> > >
> > >
> > >select valor_fact from ##CONTENC where contracto = @cont_descCursor
> >

30 Aug 2006 7:42 PM

plan9

I have made a few changes to the select ....can you help me is this correct?

exec('select '+@jperiodo + '=select sum(' +@teste_varchar2+ ') from
##CONTENC where contracto = ''' + @cont_descCursor + '''')

Show quote

"Jim Underwood" wrote:

> Ahhh... then in this particular case you might use:
>
> exec ('select ' +@teste_varchar2+ ' from ##CONTENC
>           where contracto = ''' + @cont_descCursor + ''''
>
> The two single quotes embed a single quote in your string, and enclose the
> value of @cont_descCursor within quotes.
>
> However, you really need to read Erland's article that Arnie and I have
> linked. It explains a lot of things that you will need to know to use
> dynamic SQL proficiently.
>
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
> > The problem with cont_descCursor was one of the firsts debugs I made for
> that
> > I used those instructions:
> >
> > select valor_fact from ##CONTENC where contracto = @cont_descCursor
> > (it works)
> >
> > select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
> > (it works)
> >
> >
> > select valor_fact from ##CONTENC where contracto = "Limpeza X2"
> > (it works)
> >
> >
> > "Roy Harvey" wrote:
> >
> > > To diagnose these sorts of problems, be sure to display the string you
> > > are executing. So for:
> > >
> > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = '
> +
> > > >@cont_descCursor)
> > >
> > > SELECT ('select ' + @teste_varchar2 +
> > >         ' from ##CONTENC where contracto = ' + @cont_descCursor)
> > >
> > > I think the result from that will make the problem obvious, but I
> > > think it comes down to @cont_descCursor.
> > >
> > > If @cont_descCursor is a number you need to convert it to a string:
> > > CONVERT(varchar(12),@cont_descCursor)
> > >
> > > If @cont_descCursor is a string you need to put quotes around it.
> > > '''' + @cont_descCursor + ''''
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
> > > <pl***@discussions.microsoft.com> wrote:
> > >
> > > >Hello
> > > >
> > > >I'm trying to do a select and I'm having a problem with it (code below)
> > > >
> > > >
> > > >               declare @teste_varchar2 as varchar(20)
> > > >               declare @teste_varchar as varchar(500)
> > > >
> > > > set @teste_varchar2 = "valor_fact"
> > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = '
> +
> > > >@cont_descCursor)
> > > >
> > > >
> > > >
> > > >What is odd with the above code is that if I use a similar code but not
> > > >dynamic sql it works.
> > > >
> > > >
> > > >select valor_fact from ##CONTENC where contracto = @cont_descCursor
> > >
>
>
>

30 Aug 2006 7:48 PM

Jim Underwood

I am not sure, but I think you need @jperiodo inside the quotes.

Read up on Erland's dynamic SQL Article. He shows you some tips for
debugging your code, the most valuable of which is displaying your final SQL
Statment before executing it, so you can see the errors.

He also explains why you should be using SP_ExecuteSQL instead of Exec.

Show quote

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:0FC8C0AA-EBA5-4A87-871D-C6AEF2ADDB91@microsoft.com...
> I have made a few changes to the select ....can you help me is this
correct?
>
>
> exec('select '+@jperiodo + '=select sum(' +@teste_varchar2+ ') from
> ##CONTENC where contracto = ''' + @cont_descCursor + '''')
>
> "Jim Underwood" wrote:
>
> > Ahhh... then in this particular case you might use:
> >
> > exec ('select ' +@teste_varchar2+ ' from ##CONTENC
> >           where contracto = ''' + @cont_descCursor + ''''
> >
> > The two single quotes embed a single quote in your string, and enclose
the
> > value of @cont_descCursor within quotes.
> >
> > However, you really need to read Erland's article that Arnie and I have
> > linked. It explains a lot of things that you will need to know to use
> > dynamic SQL proficiently.
> >
> >
> > "plan9" <pl***@discussions.microsoft.com> wrote in message
> > news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
> > > The problem with cont_descCursor was one of the firsts debugs I made
for
> > that
> > > I used those instructions:
> > >
> > > select valor_fact from ##CONTENC where contracto = @cont_descCursor
> > > (it works)
> > >
> > > select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
> > > (it works)
> > >
> > >
> > > select valor_fact from ##CONTENC where contracto = "Limpeza X2"
> > > (it works)
> > >
> > >
> > > "Roy Harvey" wrote:
> > >
> > > > To diagnose these sorts of problems, be sure to display the string
you
> > > > are executing. So for:
> > > >
> > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto
= '
> > +
> > > > >@cont_descCursor)
> > > >
> > > > SELECT ('select ' + @teste_varchar2 +
> > > >         ' from ##CONTENC where contracto = ' + @cont_descCursor)
> > > >
> > > > I think the result from that will make the problem obvious, but I
> > > > think it comes down to @cont_descCursor.
> > > >
> > > > If @cont_descCursor is a number you need to convert it to a string:
> > > > CONVERT(varchar(12),@cont_descCursor)
> > > >
> > > > If @cont_descCursor is a string you need to put quotes around it.
> > > > '''' + @cont_descCursor + ''''
> > > >
> > > > Roy Harvey
> > > > Beacon Falls, CT
> > > >
> > > > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
> > > > <pl***@discussions.microsoft.com> wrote:
> > > >
> > > > >Hello
> > > > >
> > > > >I'm trying to do a select and I'm having a problem with it (code
below)
> > > > >
> > > > >
> > > > >               declare @teste_varchar2 as varchar(20)
> > > > >               declare @teste_varchar as varchar(500)
> > > > >
> > > > > set @teste_varchar2 = "valor_fact"
> > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto
= '
> > +
> > > > >@cont_descCursor)
> > > > >
> > > > >
> > > > >
> > > > >What is odd with the above code is that if I use a similar code but
not
> > > > >dynamic sql it works.
> > > > >
> > > > >
> > > > >select valor_fact from ##CONTENC where contracto = @cont_descCursor
> > > >
> >
> >
> >

30 Aug 2006 8:25 PM

plan9

I have tried both situations and had diferent errors

with
exec('select '''+@jperiodo + '''=select sum(' +@teste_varchar2+ ') from
##CONTENC where contracto = ''' + @cont_descCursor + '''')
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.

with
Server: Msg 8115, Level 16, State 1, Procedure SP_CALC_JUROS_SUM10, Line 1182
Arithmetic overflow error converting varchar to data type numeric.
select @jperiodo ='select sum(' +@teste_varchar2+ ') from ##CONTENC where
contracto = ''' + @cont_descCursor + ''''
exec (@jperiodo)

Show quote

"Jim Underwood" wrote:

> I am not sure, but I think you need @jperiodo inside the quotes.
>
> Read up on Erland's dynamic SQL Article. He shows you some tips for
> debugging your code, the most valuable of which is displaying your final SQL
> Statment before executing it, so you can see the errors.
>
> He also explains why you should be using SP_ExecuteSQL instead of Exec.
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:0FC8C0AA-EBA5-4A87-871D-C6AEF2ADDB91@microsoft.com...
> > I have made a few changes to the select ....can you help me is this
> correct?
> >
> >
> > exec('select '+@jperiodo + '=select sum(' +@teste_varchar2+ ') from
> > ##CONTENC where contracto = ''' + @cont_descCursor + '''')
> >
> > "Jim Underwood" wrote:
> >
> > > Ahhh... then in this particular case you might use:
> > >
> > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC
> > >           where contracto = ''' + @cont_descCursor + ''''
> > >
> > > The two single quotes embed a single quote in your string, and enclose
> the
> > > value of @cont_descCursor within quotes.
> > >
> > > However, you really need to read Erland's article that Arnie and I have
> > > linked. It explains a lot of things that you will need to know to use
> > > dynamic SQL proficiently.
> > >
> > >
> > > "plan9" <pl***@discussions.microsoft.com> wrote in message
> > > news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
> > > > The problem with cont_descCursor was one of the firsts debugs I made
> for
> > > that
> > > > I used those instructions:
> > > >
> > > > select valor_fact from ##CONTENC where contracto = @cont_descCursor
> > > > (it works)
> > > >
> > > > select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
> > > > (it works)
> > > >
> > > >
> > > > select valor_fact from ##CONTENC where contracto = "Limpeza X2"
> > > > (it works)
> > > >
> > > >
> > > > "Roy Harvey" wrote:
> > > >
> > > > > To diagnose these sorts of problems, be sure to display the string
> you
> > > > > are executing. So for:
> > > > >
> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto
> = '
> > > +
> > > > > >@cont_descCursor)
> > > > >
> > > > > SELECT ('select ' + @teste_varchar2 +
> > > > >         ' from ##CONTENC where contracto = ' + @cont_descCursor)
> > > > >
> > > > > I think the result from that will make the problem obvious, but I
> > > > > think it comes down to @cont_descCursor.
> > > > >
> > > > > If @cont_descCursor is a number you need to convert it to a string:
> > > > > CONVERT(varchar(12),@cont_descCursor)
> > > > >
> > > > > If @cont_descCursor is a string you need to put quotes around it.
> > > > > '''' + @cont_descCursor + ''''
> > > > >
> > > > > Roy Harvey
> > > > > Beacon Falls, CT
> > > > >
> > > > > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
> > > > > <pl***@discussions.microsoft.com> wrote:
> > > > >
> > > > > >Hello
> > > > > >
> > > > > >I'm trying to do a select and I'm having a problem with it (code
> below)
> > > > > >
> > > > > >
> > > > > >               declare @teste_varchar2 as varchar(20)
> > > > > >               declare @teste_varchar as varchar(500)
> > > > > >
> > > > > > set @teste_varchar2 = "valor_fact"
> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto
> = '
> > > +
> > > > > >@cont_descCursor)
> > > > > >
> > > > > >
> > > > > >
> > > > > >What is odd with the above code is that if I use a similar code but
> not
> > > > > >dynamic sql it works.
> > > > > >
> > > > > >
> > > > > >select valor_fact from ##CONTENC where contracto = @cont_descCursor
> > > > >
> > >
> > >
> > >
>
>
>

30 Aug 2006 9:09 PM

Dean

It looks like you want to select result of the sum over the column denoted
with @teste_varchar2 into a local variable, right? Try sp_executesql with an
output parameter - something like this:

declare @jperiodo decimal(12,2)
declare @q varchar(200)
set @q='set @jperiodo = (select sum(' +@teste_varchar2+ ') from ##CONTENC
where contracto = ''' + @cont_descCursor + ''''
exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo output
select @jperiodo

Dean

Show quote

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:E93911E6-635E-4667-98CC-165A66C6F580@microsoft.com...
>I have tried both situations and had diferent errors
>
> with
> exec('select '''+@jperiodo + '''=select sum(' +@teste_varchar2+ ') from
> ##CONTENC where contracto = ''' + @cont_descCursor + '''')
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'select'.
>
>
> with
> Server: Msg 8115, Level 16, State 1, Procedure SP_CALC_JUROS_SUM10, Line
> 1182
> Arithmetic overflow error converting varchar to data type numeric.
> select @jperiodo ='select sum(' +@teste_varchar2+ ') from ##CONTENC where
> contracto = ''' + @cont_descCursor + ''''
> exec (@jperiodo)
>
>
>
>
>
>
>
> "Jim Underwood" wrote:
>
>> I am not sure, but I think you need @jperiodo inside the quotes.
>>
>> Read up on Erland's dynamic SQL Article. He shows you some tips for
>> debugging your code, the most valuable of which is displaying your final
>> SQL
>> Statment before executing it, so you can see the errors.
>>
>> He also explains why you should be using SP_ExecuteSQL instead of Exec.
>>
>> "plan9" <pl***@discussions.microsoft.com> wrote in message
>> news:0FC8C0AA-EBA5-4A87-871D-C6AEF2ADDB91@microsoft.com...
>> > I have made a few changes to the select ....can you help me is this
>> correct?
>> >
>> >
>> > exec('select '+@jperiodo + '=select sum(' +@teste_varchar2+ ') from
>> > ##CONTENC where contracto = ''' + @cont_descCursor + '''')
>> >
>> > "Jim Underwood" wrote:
>> >
>> > > Ahhh... then in this particular case you might use:
>> > >
>> > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC
>> > >           where contracto = ''' + @cont_descCursor + ''''
>> > >
>> > > The two single quotes embed a single quote in your string, and
>> > > enclose
>> the
>> > > value of @cont_descCursor within quotes.
>> > >
>> > > However, you really need to read Erland's article that Arnie and I
>> > > have
>> > > linked. It explains a lot of things that you will need to know to
>> > > use
>> > > dynamic SQL proficiently.
>> > >
>> > >
>> > > "plan9" <pl***@discussions.microsoft.com> wrote in message
>> > > news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
>> > > > The problem with cont_descCursor was one of the firsts debugs I
>> > > > made
>> for
>> > > that
>> > > > I used those instructions:
>> > > >
>> > > > select valor_fact from ##CONTENC where contracto = @cont_descCursor
>> > > > (it works)
>> > > >
>> > > > select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
>> > > > (it works)
>> > > >
>> > > >
>> > > > select valor_fact from ##CONTENC where contracto = "Limpeza X2"
>> > > > (it works)
>> > > >
>> > > >
>> > > > "Roy Harvey" wrote:
>> > > >
>> > > > > To diagnose these sorts of problems, be sure to display the
>> > > > > string
>> you
>> > > > > are executing. So for:
>> > > > >
>> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where
>> > > > > > contracto
>> = '
>> > > +
>> > > > > >@cont_descCursor)
>> > > > >
>> > > > > SELECT ('select ' + @teste_varchar2 +
>> > > > >         ' from ##CONTENC where contracto = ' + @cont_descCursor)
>> > > > >
>> > > > > I think the result from that will make the problem obvious, but I
>> > > > > think it comes down to @cont_descCursor.
>> > > > >
>> > > > > If @cont_descCursor is a number you need to convert it to a
>> > > > > string:
>> > > > > CONVERT(varchar(12),@cont_descCursor)
>> > > > >
>> > > > > If @cont_descCursor is a string you need to put quotes around it.
>> > > > > '''' + @cont_descCursor + ''''
>> > > > >
>> > > > > Roy Harvey
>> > > > > Beacon Falls, CT
>> > > > >
>> > > > > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
>> > > > > <pl***@discussions.microsoft.com> wrote:
>> > > > >
>> > > > > >Hello
>> > > > > >
>> > > > > >I'm trying to do a select and I'm having a problem with it (code
>> below)
>> > > > > >
>> > > > > >
>> > > > > >               declare @teste_varchar2 as varchar(20)
>> > > > > >               declare @teste_varchar as varchar(500)
>> > > > > >
>> > > > > > set @teste_varchar2 = "valor_fact"
>> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where
>> > > > > > contracto
>> = '
>> > > +
>> > > > > >@cont_descCursor)
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > >What is odd with the above code is that if I use a similar code
>> > > > > >but
>> not
>> > > > > >dynamic sql it works.
>> > > > > >
>> > > > > >
>> > > > > >select valor_fact from ##CONTENC where contracto =
>> > > > > >@cont_descCursor
>> > > > >
>> > >
>> > >
>> > >
>>
>>
>>

30 Aug 2006 11:58 PM

plan9

it gives an error

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1179
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Show quote

"Dean" wrote:

> It looks like you want to select result of the sum over the column denoted
> with @teste_varchar2 into a local variable, right? Try sp_executesql with an
> output parameter - something like this:
>
> declare @jperiodo decimal(12,2)
> declare @q varchar(200)
> set @q='set @jperiodo = (select sum(' +@teste_varchar2+ ') from ##CONTENC
> where contracto = ''' + @cont_descCursor + ''''
> exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo output
> select @jperiodo
>
>
> Dean
>
>
> "plan9" <pl***@discussions.microsoft.com> wrote in message
> news:E93911E6-635E-4667-98CC-165A66C6F580@microsoft.com...
> >I have tried both situations and had diferent errors
> >
> > with
> > exec('select '''+@jperiodo + '''=select sum(' +@teste_varchar2+ ') from
> > ##CONTENC where contracto = ''' + @cont_descCursor + '''')
> > Server: Msg 156, Level 15, State 1, Line 1
> > Incorrect syntax near the keyword 'select'.
> >
> >
> > with
> > Server: Msg 8115, Level 16, State 1, Procedure SP_CALC_JUROS_SUM10, Line
> > 1182
> > Arithmetic overflow error converting varchar to data type numeric.
> > select @jperiodo ='select sum(' +@teste_varchar2+ ') from ##CONTENC where
> > contracto = ''' + @cont_descCursor + ''''
> > exec (@jperiodo)
> >
> >
> >
> >
> >
> >
> >
> > "Jim Underwood" wrote:
> >
> >> I am not sure, but I think you need @jperiodo inside the quotes.
> >>
> >> Read up on Erland's dynamic SQL Article. He shows you some tips for
> >> debugging your code, the most valuable of which is displaying your final
> >> SQL
> >> Statment before executing it, so you can see the errors.
> >>
> >> He also explains why you should be using SP_ExecuteSQL instead of Exec.
> >>
> >> "plan9" <pl***@discussions.microsoft.com> wrote in message
> >> news:0FC8C0AA-EBA5-4A87-871D-C6AEF2ADDB91@microsoft.com...
> >> > I have made a few changes to the select ....can you help me is this
> >> correct?
> >> >
> >> >
> >> > exec('select '+@jperiodo + '=select sum(' +@teste_varchar2+ ') from
> >> > ##CONTENC where contracto = ''' + @cont_descCursor + '''')
> >> >
> >> > "Jim Underwood" wrote:
> >> >
> >> > > Ahhh... then in this particular case you might use:
> >> > >
> >> > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC
> >> > >           where contracto = ''' + @cont_descCursor + ''''
> >> > >
> >> > > The two single quotes embed a single quote in your string, and
> >> > > enclose
> >> the
> >> > > value of @cont_descCursor within quotes.
> >> > >
> >> > > However, you really need to read Erland's article that Arnie and I
> >> > > have
> >> > > linked. It explains a lot of things that you will need to know to
> >> > > use
> >> > > dynamic SQL proficiently.
> >> > >
> >> > >
> >> > > "plan9" <pl***@discussions.microsoft.com> wrote in message
> >> > > news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
> >> > > > The problem with cont_descCursor was one of the firsts debugs I
> >> > > > made
> >> for
> >> > > that
> >> > > > I used those instructions:
> >> > > >
> >> > > > select valor_fact from ##CONTENC where contracto = @cont_descCursor
> >> > > > (it works)
> >> > > >
> >> > > > select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
> >> > > > (it works)
> >> > > >
> >> > > >
> >> > > > select valor_fact from ##CONTENC where contracto = "Limpeza X2"
> >> > > > (it works)
> >> > > >
> >> > > >
> >> > > > "Roy Harvey" wrote:
> >> > > >
> >> > > > > To diagnose these sorts of problems, be sure to display the
> >> > > > > string
> >> you
> >> > > > > are executing. So for:
> >> > > > >
> >> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where
> >> > > > > > contracto
> >> = '
> >> > > +
> >> > > > > >@cont_descCursor)
> >> > > > >
> >> > > > > SELECT ('select ' + @teste_varchar2 +
> >> > > > >         ' from ##CONTENC where contracto = ' + @cont_descCursor)
> >> > > > >
> >> > > > > I think the result from that will make the problem obvious, but I
> >> > > > > think it comes down to @cont_descCursor.
> >> > > > >
> >> > > > > If @cont_descCursor is a number you need to convert it to a
> >> > > > > string:
> >> > > > > CONVERT(varchar(12),@cont_descCursor)
> >> > > > >
> >> > > > > If @cont_descCursor is a string you need to put quotes around it.
> >> > > > > '''' + @cont_descCursor + ''''
> >> > > > >
> >> > > > > Roy Harvey
> >> > > > > Beacon Falls, CT
> >> > > > >
> >> > > > > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
> >> > > > > <pl***@discussions.microsoft.com> wrote:
> >> > > > >
> >> > > > > >Hello
> >> > > > > >
> >> > > > > >I'm trying to do a select and I'm having a problem with it (code
> >> below)
> >> > > > > >
> >> > > > > >
> >> > > > > >               declare @teste_varchar2 as varchar(20)
> >> > > > > >               declare @teste_varchar as varchar(500)
> >> > > > > >
> >> > > > > > set @teste_varchar2 = "valor_fact"
> >> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where
> >> > > > > > contracto
> >> = '
> >> > > +
> >> > > > > >@cont_descCursor)
> >> > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > > >What is odd with the above code is that if I use a similar code
> >> > > > > >but
> >> not
> >> > > > > >dynamic sql it works.
> >> > > > > >
> >> > > > > >
> >> > > > > >select valor_fact from ##CONTENC where contracto =
> >> > > > > >@cont_descCursor
> >> > > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>

31 Aug 2006 8:07 AM

Dean

OK, the closing paren is missing, and the @q variable should be of type
nvarchar. This wouldn't happen if we were given the chance to actually test
the solution, BTW.

declare @jperiodo decimal(12,2)
declare @q nvarchar(200)
set @q='set @jperiodo = (select sum(' +@teste_varchar2+ ') from ##CONTENC
where contracto = ''' + @cont_descCursor + ''')'
exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo output
select @jperiodo

Dean

Show quote

"plan9" <pl***@discussions.microsoft.com> wrote in message
news:FF18CA39-9CE7-40E8-9933-B7E136F9BFBE@microsoft.com...
> it gives an error
>
>
> Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1179
> Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
>
>
>
> "Dean" wrote:
>
>> It looks like you want to select result of the sum over the column
>> denoted
>> with @teste_varchar2 into a local variable, right? Try sp_executesql with
>> an
>> output parameter - something like this:
>>
>> declare @jperiodo decimal(12,2)
>> declare @q varchar(200)
>> set @q='set @jperiodo = (select sum(' +@teste_varchar2+ ') from ##CONTENC
>> where contracto = ''' + @cont_descCursor + ''''
>> exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
>> output
>> select @jperiodo
>>
>>
>> Dean
>>
>>
>> "plan9" <pl***@discussions.microsoft.com> wrote in message
>> news:E93911E6-635E-4667-98CC-165A66C6F580@microsoft.com...
>> >I have tried both situations and had diferent errors
>> >
>> > with
>> > exec('select '''+@jperiodo + '''=select sum(' +@teste_varchar2+ ') from
>> > ##CONTENC where contracto = ''' + @cont_descCursor + '''')
>> > Server: Msg 156, Level 15, State 1, Line 1
>> > Incorrect syntax near the keyword 'select'.
>> >
>> >
>> > with
>> > Server: Msg 8115, Level 16, State 1, Procedure SP_CALC_JUROS_SUM10,
>> > Line
>> > 1182
>> > Arithmetic overflow error converting varchar to data type numeric.
>> > select @jperiodo ='select sum(' +@teste_varchar2+ ') from ##CONTENC
>> > where
>> > contracto = ''' + @cont_descCursor + ''''
>> > exec (@jperiodo)
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Jim Underwood" wrote:
>> >
>> >> I am not sure, but I think you need @jperiodo inside the quotes.
>> >>
>> >> Read up on Erland's dynamic SQL Article. He shows you some tips for
>> >> debugging your code, the most valuable of which is displaying your
>> >> final
>> >> SQL
>> >> Statment before executing it, so you can see the errors.
>> >>
>> >> He also explains why you should be using SP_ExecuteSQL instead of
>> >> Exec.
>> >>
>> >> "plan9" <pl***@discussions.microsoft.com> wrote in message
>> >> news:0FC8C0AA-EBA5-4A87-871D-C6AEF2ADDB91@microsoft.com...
>> >> > I have made a few changes to the select ....can you help me is this
>> >> correct?
>> >> >
>> >> >
>> >> > exec('select '+@jperiodo + '=select sum(' +@teste_varchar2+ ') from
>> >> > ##CONTENC where contracto = ''' + @cont_descCursor + '''')
>> >> >
>> >> > "Jim Underwood" wrote:
>> >> >
>> >> > > Ahhh... then in this particular case you might use:
>> >> > >
>> >> > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC
>> >> > >           where contracto = ''' + @cont_descCursor + ''''
>> >> > >
>> >> > > The two single quotes embed a single quote in your string, and
>> >> > > enclose
>> >> the
>> >> > > value of @cont_descCursor within quotes.
>> >> > >
>> >> > > However, you really need to read Erland's article that Arnie and I
>> >> > > have
>> >> > > linked. It explains a lot of things that you will need to know to
>> >> > > use
>> >> > > dynamic SQL proficiently.
>> >> > >
>> >> > >
>> >> > > "plan9" <pl***@discussions.microsoft.com> wrote in message
>> >> > > news:3C6DA274-35A6-4AC6-AC8C-6DDEBE0F0D10@microsoft.com...
>> >> > > > The problem with cont_descCursor was one of the firsts debugs I
>> >> > > > made
>> >> for
>> >> > > that
>> >> > > > I used those instructions:
>> >> > > >
>> >> > > > select valor_fact from ##CONTENC where contracto =
>> >> > > > @cont_descCursor
>> >> > > > (it works)
>> >> > > >
>> >> > > > select valor_fact from ##CONTENC where contracto = 'Limpeza X2'
>> >> > > > (it works)
>> >> > > >
>> >> > > >
>> >> > > > select valor_fact from ##CONTENC where contracto = "Limpeza X2"
>> >> > > > (it works)
>> >> > > >
>> >> > > >
>> >> > > > "Roy Harvey" wrote:
>> >> > > >
>> >> > > > > To diagnose these sorts of problems, be sure to display the
>> >> > > > > string
>> >> you
>> >> > > > > are executing. So for:
>> >> > > > >
>> >> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where
>> >> > > > > > contracto
>> >> = '
>> >> > > +
>> >> > > > > >@cont_descCursor)
>> >> > > > >
>> >> > > > > SELECT ('select ' + @teste_varchar2 +
>> >> > > > >         ' from ##CONTENC where contracto = ' +
>> >> > > > > @cont_descCursor)
>> >> > > > >
>> >> > > > > I think the result from that will make the problem obvious,
>> >> > > > > but I
>> >> > > > > think it comes down to @cont_descCursor.
>> >> > > > >
>> >> > > > > If @cont_descCursor is a number you need to convert it to a
>> >> > > > > string:
>> >> > > > > CONVERT(varchar(12),@cont_descCursor)
>> >> > > > >
>> >> > > > > If @cont_descCursor is a string you need to put quotes around
>> >> > > > > it.
>> >> > > > > '''' + @cont_descCursor + ''''
>> >> > > > >
>> >> > > > > Roy Harvey
>> >> > > > > Beacon Falls, CT
>> >> > > > >
>> >> > > > > On Wed, 30 Aug 2006 11:21:02 -0700, plan9
>> >> > > > > <pl***@discussions.microsoft.com> wrote:
>> >> > > > >
>> >> > > > > >Hello
>> >> > > > > >
>> >> > > > > >I'm trying to do a select and I'm having a problem with it
>> >> > > > > >(code
>> >> below)
>> >> > > > > >
>> >> > > > > >
>> >> > > > > >               declare @teste_varchar2 as varchar(20)
>> >> > > > > >               declare @teste_varchar as varchar(500)
>> >> > > > > >
>> >> > > > > > set @teste_varchar2 = "valor_fact"
>> >> > > > > > exec ('select ' +@teste_varchar2+ ' from ##CONTENC where
>> >> > > > > > contracto
>> >> = '
>> >> > > +
>> >> > > > > >@cont_descCursor)
>> >> > > > > >
>> >> > > > > >
>> >> > > > > >
>> >> > > > > >What is odd with the above code is that if I use a similar
>> >> > > > > >code
>> >> > > > > >but
>> >> not
>> >> > > > > >dynamic sql it works.
>> >> > > > > >
>> >> > > > > >
>> >> > > > > >select valor_fact from ##CONTENC where contracto =
>> >> > > > > >@cont_descCursor
>> >> > > > >
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
>>
>>

30 Aug 2006 6:30 PM

SQL Menace

well first of all where is @cont_descCursor declared?

here is how this would work in the pubs db

use pubs
go

select * into ##CONTENC
from authors

declare @teste_varchar2 as varchar(20)
declare @cont_descCursor as varchar(500)

select @cont_descCursor =   '172-32-1176'

        set @teste_varchar2 = 'au_lname'
        exec ('select ' +@teste_varchar2+ ' from ##CONTENC where au_id
= ''' +
@cont_descCursor +'''')

next read this The Curse and Blessings of Dynamic SQL
(http://www.sommarskog.se/dynamic_sql.html) to understand some of the
security issues

Denis the SQL Menace
http://sqlservercode.blogspot.com/
plan9 wrote:
Show quote

> Hello
>
> I'm trying to do a select and I'm having a problem with it (code below)
>
>
>                declare @teste_varchar2 as varchar(20)
>                declare @teste_varchar as varchar(500)
>
>     set @teste_varchar2 = "valor_fact"
>     exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> @cont_descCursor)
>
>
>
> What is odd with the above code is that if I use a similar code but not
> dynamic sql it works.
>
>
> select valor_fact from ##CONTENC where contracto = @cont_descCursor

30 Aug 2006 6:44 PM

plan9

it's declared as

declare @cont_descCursor as varchar(100)

in the beggining of the Procedure

Show quote

"SQL Menace" wrote:

> well first of all where is @cont_descCursor declared?
>
> here is how this would work in the pubs db
>
> use pubs
> go
>
> select * into ##CONTENC
> from authors
>
> declare @teste_varchar2 as varchar(20)
> declare @cont_descCursor as varchar(500)
>
> select @cont_descCursor =   '172-32-1176'
>
>
>
>
>         set @teste_varchar2 = 'au_lname'
>         exec ('select ' +@teste_varchar2+ ' from ##CONTENC where au_id
> = ''' +
> @cont_descCursor +'''')
>
> next read this The Curse and Blessings of Dynamic SQL
> (http://www.sommarskog.se/dynamic_sql.html) to understand some of the
> security issues
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> plan9 wrote:
> > Hello
> >
> > I'm trying to do a select and I'm having a problem with it (code below)
> >
> >
> >                declare @teste_varchar2 as varchar(20)
> >                declare @teste_varchar as varchar(500)
> >
> >     set @teste_varchar2 = "valor_fact"
> >     exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' +
> > @cont_descCursor)
> >
> >
> >
> > What is odd with the above code is that if I use a similar code but not
> > dynamic sql it works.
> >
> >
> > select valor_fact from ##CONTENC where contracto = @cont_descCursor
>
>

30 Aug 2006 8:17 PM

--CELKO--

>> I'm trying to do a select and I'm having a problem with it (code below) <<

Oh yes, the old "Britney Spearas, Automoible and Squid" code Module!!

The short answer is use slow, proprietrary dynamic SQL to kludge a
query together on the fly with your table name in the FROM clause.

The right answer is never pass a table name as a parameter. You need to
understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A model
of a set of entities or relationships. EACH TABLE SHOULD BE A DIFFERENT
KIND OF ENTITY. When you have many tables that model the same entity,
then you have a magnetic tape file system written in SQL, and not an
RDBMS at all.

If the tables are different, then having a generic procedure which
works equally on automobiles, octopi or Britney Spear's discology is
saying that your application is a disaster of design.

1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.

2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember the
basics of Software Engineering? Modules need weak coupling and strong
cohesion, etc. This is far more fundamental than just SQL; it has to
do with learning to programming at all.

3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.

4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.

30 Aug 2006 11:15 PM

Steve Dassin

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1156969063.427674.58930@e3g2000cwe.googlegroups.com...
>
>..Go back to basics.
>

Whose basics?:) So that's out.
Go forward...into a fog?
So we have the Al Bundy syndrome. Stuck in an sql shoestore forever :(
It don't get much better than that:)

http://racster.blogspot.com/

30 Aug 2006 11:50 PM

Arnie Rowland

Steve,

Your post sparked an idea. Entertain us, write your entreaties as a haiku,
or a koan.

Shamelessly plug your product with wit and humor...

;-)

haiku
1.. A Japanese lyric verse form having three unrhymed lines of five,
seven, and five syllables, traditionally invoking an aspect of nature or the
seasons.
2.. A poem written in this form.
koan

/koh'an/ n. A Zen teaching riddle. Classically, koans
are attractive paradoxes to be meditated on; their purpose is to
help one to enlightenment by temporarily jamming normal cognitive
processing so that something more interesting can happen (this
practice is associated with Rinzei Zen Buddhism).

The challenge is now yours ...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Steve Dassin" <steve@nospamrac4sql.net> wrote in message
news:OyrhUpIzGHA.480@TK2MSFTNGP06.phx.gbl...
> "--CELKO--" <jcelko***@earthlink.net> wrote in message
> news:1156969063.427674.58930@e3g2000cwe.googlegroups.com...
>>
>>..Go back to basics.
>>
> Whose basics?:) So that's out.
> Go forward...into a fog?
> So we have the Al Bundy syndrome. Stuck in an sql shoestore forever :(
> It don't get much better than that:)
>
> http://racster.blogspot.com/
>
>

31 Aug 2006 12:30 AM

Steve Dassin

Dr. Rowland,

A post worthy of a place in a certain faq.

I can do Koan. Until someone tells me to KoOff.

best,
Spock @ Romulus . com

Show quote

"Arnie Rowland" <ar***@1568.com> wrote in message
news:uddnQ8IzGHA.4044@TK2MSFTNGP04.phx.gbl...
> Steve,
>
> Your post sparked an idea. Entertain us, write your entreaties as a haiku,
> or a koan.
>
> Shamelessly plug your product with wit and humor...
>
> ;-)
>
> haiku
> 1.. A Japanese lyric verse form having three unrhymed lines of five,
> seven, and five syllables, traditionally invoking an aspect of nature or
> the seasons.
> 2.. A poem written in this form.
> koan
>
> /koh'an/ n. A Zen teaching riddle. Classically, koans
> are attractive paradoxes to be meditated on; their purpose is to
> help one to enlightenment by temporarily jamming normal cognitive
> processing so that something more interesting can happen (this
> practice is associated with Rinzei Zen Buddhism).
>
> The challenge is now yours ...
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Steve Dassin" <steve@nospamrac4sql.net> wrote in message
> news:OyrhUpIzGHA.480@TK2MSFTNGP06.phx.gbl...
>> "--CELKO--" <jcelko***@earthlink.net> wrote in message
>> news:1156969063.427674.58930@e3g2000cwe.googlegroups.com...
>>>
>>>..Go back to basics.
>>>
>> Whose basics?:) So that's out.
>> Go forward...into a fog?
>> So we have the Al Bundy syndrome. Stuck in an sql shoestore forever :(
>> It don't get much better than that:)
>>
>> http://racster.blogspot.com/
>>
>>
>
>

31 Aug 2006 1:42 AM

Tom Cooper

Sample Haiku

Steve did not mention
Rac in his latest message
But he will next time

Sample Koan

What is the sound of Tony Rogerson applauding a posting from Joe Celko?

<Big Grin>

Tom

Show quote

"Steve Dassin" <steve@nospamrac4sql.net> wrote in message
news:uaHYXTJzGHA.4104@TK2MSFTNGP02.phx.gbl...
> Dr. Rowland,
>
> A post worthy of a place in a certain faq.
>
> I can do Koan. Until someone tells me to KoOff.
>
> :)
>
> best,
> Spock @ Romulus . com
>
> "Arnie Rowland" <ar***@1568.com> wrote in message
> news:uddnQ8IzGHA.4044@TK2MSFTNGP04.phx.gbl...
>> Steve,
>>
>> Your post sparked an idea. Entertain us, write your entreaties as a
>> haiku, or a koan.
>>
>> Shamelessly plug your product with wit and humor...
>>
>> ;-)
>>
>> haiku
>> 1.. A Japanese lyric verse form having three unrhymed lines of five,
>> seven, and five syllables, traditionally invoking an aspect of nature or
>> the seasons.
>> 2.. A poem written in this form.
>> koan
>>
>> /koh'an/ n. A Zen teaching riddle. Classically, koans
>> are attractive paradoxes to be meditated on; their purpose is to
>> help one to enlightenment by temporarily jamming normal cognitive
>> processing so that something more interesting can happen (this
>> practice is associated with Rinzei Zen Buddhism).
>>
>> The challenge is now yours ...
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>>
>> "Steve Dassin" <steve@nospamrac4sql.net> wrote in message
>> news:OyrhUpIzGHA.480@TK2MSFTNGP06.phx.gbl...
>>> "--CELKO--" <jcelko***@earthlink.net> wrote in message
>>> news:1156969063.427674.58930@e3g2000cwe.googlegroups.com...
>>>>
>>>>..Go back to basics.
>>>>
>>> Whose basics?:) So that's out.
>>> Go forward...into a fog?
>>> So we have the Al Bundy syndrome. Stuck in an sql shoestore forever :(
>>> It don't get much better than that:)
>>>
>>> http://racster.blogspot.com/
>>>
>>>
>>
>>
>
>