SQL Server 2000 SP3a
Im using a sproc to make a sql statement. The statement is built up and
assigned to @.SQL1 nvarchar(4000), then at the end of the sproc its runs:
exec sp_executesql @.SQL1
The problem is that I've reached the 4000 limit!! How do other people get
round this, bearing in mind that its full of inner joins with sub selects, s
o
I dont think I can split it out. And because the sproc can create a view
which is updateable, I can't turn those sub selects into views as this makes
the newly created view non-updateable.
How can I build up a SQL string that is bigger than 4000 please.Split it up into multiple char/varchar variables and do:
EXEC( @.var1 + @.var2 + ...+ @.varn )
Anith|||Off the top of my head, can you incrementally build this using temp
tables and/or table variables?
Maybe something like...
procedure MyProc
as
-- do something with #temp1
-- do something with #temp2
select * from #temp1
join #temp1 on #temp2.something = #temp1.something
go
Just a thought.
Bryce|||If that means I can link several statements, which each on their own don't
make sense, together and execute in one go that would be perfect!!!
I won't be able to check til Monday, so many thanks in advance, I was
getting REALLY worried that many hours of trying to get get a one sproc does
all approach was going to fall at the last hurdle.
I shall read some more on exec / sp_executesql as it sounds like more can be
done than I had assumed.
Many thanks!
"Anith Sen" wrote:
> Split it up into multiple char/varchar variables and do:
> EXEC( @.var1 + @.var2 + ...+ @.varn )
> --
> Anith
>
>
Showing posts with label building. Show all posts
Showing posts with label building. Show all posts
Wednesday, March 21, 2012
Monday, February 20, 2012
Rating System / AVG (Easy Question)
I am building a rating system, and there will be lots of records that will
simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:
I want to do something like 'SELECT AVG(ratings) as avgRating' And this
always returns an tinyint datatype since the ratings are tinyints. I want
slightly more precision than a tinyint for the average, what I want is
something in the format of '#.##' (i.e. 3.45). What datatypes do I need to
store the ratings in and what command can I use to get JUST a #.##?
I've changed the datatypes to money and decimal and used the ROUND function,
but it still returns something like '3.4500'.
Any help is appreciated, thanks in advance
CraigThis is a multi-part message in MIME format.
--=_NextPart_000_002A_01C36729.1E7833E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select avg (cast (rating as numeric (3, 2)))
from MyTable
--
Tom
"Craig S" <craig@.removethis_birch.net> wrote in message =news:eVy6Pe0ZDHA.3768@.tk2msftngp13.phx.gbl...
> I am building a rating system, and there will be lots of records that =will
> simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:
> > I want to do something like 'SELECT AVG(ratings) as avgRating' And =this
> always returns an tinyint datatype since the ratings are tinyints. I =want
> slightly more precision than a tinyint for the average, what I want is
> something in the format of '#.##' (i.e. 3.45). What datatypes do I =need to
> store the ratings in and what command can I use to get JUST a #.##?
> > I've changed the datatypes to money and decimal and used the ROUND =function,
> but it still returns something like '3.4500'.
> > Any help is appreciated, thanks in advance
> Craig
> > --=_NextPart_000_002A_01C36729.1E7833E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
select avg (cast (rating as =numeric (3, 2)))
from MyTable
Tom
"Craig S" wrote in message news:eVy6Pe0ZDHA.3768@.tk2msftngp13.phx.gbl...> I =am building a rating system, and there will be lots of records that will> =simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:> => I want to do something like 'SELECT AVG(ratings) as avgRating' And =this> always returns an tinyint datatype since the ratings are tinyints. I want> slightly more precision than a tinyint =for the average, what I want is> something in the format of '#.##' (i.e. 3.45). What datatypes do I need to> store the ratings in =and what command can I use to get JUST a #.##?> > I've changed the datatypes to money and decimal and used the ROUND function,> but =it still returns something like '3.4500'.> > Any help is =appreciated, thanks in advance> Craig> >
--=_NextPart_000_002A_01C36729.1E7833E0--
simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:
I want to do something like 'SELECT AVG(ratings) as avgRating' And this
always returns an tinyint datatype since the ratings are tinyints. I want
slightly more precision than a tinyint for the average, what I want is
something in the format of '#.##' (i.e. 3.45). What datatypes do I need to
store the ratings in and what command can I use to get JUST a #.##?
I've changed the datatypes to money and decimal and used the ROUND function,
but it still returns something like '3.4500'.
Any help is appreciated, thanks in advance
CraigThis is a multi-part message in MIME format.
--=_NextPart_000_002A_01C36729.1E7833E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select avg (cast (rating as numeric (3, 2)))
from MyTable
--
Tom
"Craig S" <craig@.removethis_birch.net> wrote in message =news:eVy6Pe0ZDHA.3768@.tk2msftngp13.phx.gbl...
> I am building a rating system, and there will be lots of records that =will
> simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:
> > I want to do something like 'SELECT AVG(ratings) as avgRating' And =this
> always returns an tinyint datatype since the ratings are tinyints. I =want
> slightly more precision than a tinyint for the average, what I want is
> something in the format of '#.##' (i.e. 3.45). What datatypes do I =need to
> store the ratings in and what command can I use to get JUST a #.##?
> > I've changed the datatypes to money and decimal and used the ROUND =function,
> but it still returns something like '3.4500'.
> > Any help is appreciated, thanks in advance
> Craig
> > --=_NextPart_000_002A_01C36729.1E7833E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
select avg (cast (rating as =numeric (3, 2)))
from MyTable
Tom
"Craig S"
--=_NextPart_000_002A_01C36729.1E7833E0--
Subscribe to:
Posts (Atom)