September 26, 2001 at 1:15 pm
Hello, all.
I am creating a stored procedure that returns a result set. The select statement is built through the routine and consists of x number of seperate statements strung together with UNION. I'm using a varchar variable to store the string and then I execute it at the end, but varchar has a limit of 8000 characters. The string is at 2500 characters or so right now, but I fully expect that in a couple of years there may be enough users (there is one select statment per user) to exceed the 8000 mark. How can I build a select statement that is more than 8000 characters?
I have to return the results as rows through a select statement so the web app on the other end can display it.
Any ideas?
Thanks in advance...
September 26, 2001 at 1:26 pm
Also, if I have a string of select statments put together using UNION, is there a way to sort the result set?
Maybe something like ((select x, y from a) union (select x, y from b) order by a)?
September 26, 2001 at 1:33 pm
For the first part, a string > 4000 characters, you've always got the option of the text data type.
As for union, the sort is going to be on the entire set, and there's no way to turn it "off" so far as I am aware. You could add another column which is hard-coded with a value:
SELECT x, y, 'a' AS SortCol FROM a
UNION
SELECT x, y, 'b' AS SortCol FROM b
ORDER BY SortCol, x, y
Another idea is to toss the results into a temp table, again with another column to signify where it came from, and do an order by that column, even though you aren't displaying it:
SELECT x, y
FROM #mytable
ORDER BY SortCol, x, y
Agreed, neither is a particularly appetizing solution, but you're going up against the definition of UNION here.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
September 26, 2001 at 1:35 pm
I like the temp table idea as a start, maybe even step back and look for another way to solve it - maybe something like getting your criteria into a temp table so you can join all at once or something. Post some code if you'd like to see if we can help you think of anything!
Andy
September 26, 2001 at 1:45 pm
Thanks for your suggestions. I'm very very new to all this. I've only been using SQL for a couple months and I've never done any programming to speak of. Here's the code...
This sp is supposed to return a resultset of stats for the users of the application, showing how many pages of work they are averaging per hour in each module. Anytime a user completes an activity in the app, it inserts a record into btstats of the username, activity, start time, finish time, and some other stuff we want to track internally.
I am open to suggestions...
CREATE PROCEDURE sp_bt_stats_users
AS
declare @usernum int
declare @c1 int
declare @name varchar(50)
declare @minute decimal(10,2)
declare @select varchar(200)
declare @select2 varchar(8000)
declare @tempid int
declare @pages decimal(10,2)
declare @vperhour varchar(20)
declare @vperday varchar(20)
declare @vperdaytemp decimal(10,2)
declare @vperhourtemp decimal(10,2)
declare @sperhour varchar(20)
declare @sperday varchar(20)
declare @sperdaytemp decimal(10,2)
declare @sperhourtemp decimal(10,2)
declare @cperhour varchar(20)
declare @cperday varchar(20)
declare @cperdaytemp decimal(10,2)
declare @cperhourtemp decimal(10,2)
declare @rperhour varchar(20)
declare @rperday varchar(20)
declare @rperdaytemp decimal(10,2)
declare @rperhourtemp decimal(10,2)
--set the loop
set @usernum = (select max(btukey) from btusers)
set @c1 = @usernum
--this is a placeholder for the dummy select statement. just pick the last record of btstats
set @tempid = (select max(btiid) from btstats)
--Loop through all names in the btusers database
While @c1 > 0
BEGIN
--Select names based on the key ID from the loop
set @name = (select btulogin from btusers where btukey = @c1)
set @name = (rtrim(ltrim(@name)))
--Calculate pages per hour (sum/sum(seconds)/(pages)/60/60)
set @vperhourtemp = (select sum(datediff(second, btiin, btiout)) from btstats where btiuser = @name and btiactivity = 'verify')
set @pages = (select sum(btipages) from btstats where btiuser = @name and btiactivity = 'verify')
set @vperhourtemp = @pages / (@vperhourtemp/60/60)
set @vperhour = cast(@vperhourtemp as varchar(20))
if @vperhour is null
BEGIN
set @vperhour = 'n/a'
END
--Calculate pages per hour (sum/sum(seconds)/(pages)/60/60)
set @sperhourtemp = (select sum(datediff(second, btiin, btiout)) from btstats where btiuser = @name and btiactivity = 'scan')
set @pages = (select sum(btipages) from btstats where btiuser = @name and btiactivity = 'scan')
set @sperhourtemp = @pages / (@sperhourtemp/60/60)
set @sperhour = cast(@sperhourtemp as varchar(20))
if @sperhour is null
BEGIN
set @sperhour = 'n/a'
END
--Calculate pages per hour (sum/sum(seconds)/(pages)/60/60)
set @cperhourtemp = (select sum(datediff(second, btiin, btiout)) from btstats where btiuser = @name and btiactivity = 'commit')
set @pages = (select sum(btipages) from btstats where btiuser = @name and btiactivity = 'commit')
set @cperhourtemp = @pages / (@cperhourtemp/60/60)
set @cperhour = cast(@cperhourtemp as varchar(20))
if @cperhour is null
BEGIN
set @cperhour = 'n/a'
END
--Calculate pages per hour (sum/sum(seconds)/(pages)/60/60)
set @rperhourtemp = (select sum(datediff(second, btiin, btiout)) from btstats where btiuser = @name and btiactivity = 'review')
set @pages = (select sum(btipages) from btstats where btiuser = @name and btiactivity = 'review')
set @rperhourtemp = @pages / (@rperhourtemp/60/60)
set @rperhour = cast(@rperhourtemp as varchar(20))
if @rperhour is null
BEGIN
set @rperhour = 'n/a'
END
--Build select statement
if @name is not null
BEGIN
set @select = ('(select username = ''' + @name + ''', vperhour = ''' + @vperhour + ''', sperhour = ''' + @sperhour + ''', cperhour = ''' + @cperhour + ''', rperhour = ''' + @rperhour + ''' from btstats where btiid = ' + cast(@tempid as varchar(20)) + ')')
if @select2 is not null
set @select2 = ('' + @select2 + ' UNION ' + @select + ' ')
if @select2 is null
set @select2 = (@select)
END
set @c1 = (@c1-1)
END
set @select2 = (@select + ' order by username ')
exec (@select2)
September 26, 2001 at 1:47 pm
Here's an example of what it kicks out.... I'd like to be able to order by most/least in a category and/or username.
username vperhour sperhour cperhour rperhour
----------- -------- -------- -------- --------
aaronc n/a n/a n/a n/a
alysiah n/a n/a n/a n/a
conniea n/a n/a n/a n/a
deborahg n/a n/a n/a n/a
dinht n/a n/a n/a n/a
ernestob n/a n/a n/a n/a
eugenel n/a n/a n/a n/a
eveliaa n/a n/a n/a n/a
jacquelinel n/a n/a n/a n/a
kippinh n/a n/a n/a n/a
kristineh n/a n/a n/a n/a
londelle n/a n/a n/a n/a
mikea 84.06 4281.09 n/a n/a
nekiak n/a n/a n/a n/a
seanc 4500.05 782.61 n/a n/a
shawnc 1286.93 12074.16 7330.12 15178.40
telithaf n/a n/a n/a n/a
tinah n/a n/a n/a n/a
williama n/a n/a n/a n/a
zanaciusc n/a n/a n/a n/a
September 26, 2001 at 3:02 pm
does this work for you??
select btulogin as username ,
ISNULL ( vperhour , 'n/a') as vperhour ,
ISNULL ( sperhour , 'n/a') as sperhour ,
ISNULL ( cperhour , 'n/a') as cperhour ,
ISNULL ( rperhour , 'n/a') as rperhour
from btusers
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20),'n/a' as vperhour
FROM btstats
WHERE btiactivity = 'verify'
) as vperhour
on btusers.btulogin = vperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
ISNULL(cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20),'n/a' as sperhour
FROM btstats
WHERE btiactivity = 'scan'
)as sperhour
on btusers.btulogin = sperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
ISNULL(cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20),'n/a' as cperhour
FROM btstats
WHERE btiactivity = 'commit'
)as cperhour
on btusers.btulogin = cperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
ISNULL(cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20),'n/a' as rperhour
FROM btstats
WHERE btiactivity = 'review'
)as rperhour
on btusers.btulogin = rperhour.btiuser
ORDER by username , vperhour , sperhour , cperhour , rperhour
September 26, 2001 at 3:07 pm
sorry that should be!!
select btulogin as username ,
ISNULL ( vperhour , 'n/a') as vperhour ,
ISNULL ( sperhour , 'n/a') as sperhour ,
ISNULL ( cperhour , 'n/a') as cperhour ,
ISNULL ( rperhour , 'n/a') as rperhour
from btusers
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as vperhour
FROM btstats
WHERE btiactivity = 'verify'
) as vperhour
on btusers.btulogin = vperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as sperhour
FROM btstats
WHERE btiactivity = 'scan'
)as sperhour
on btusers.btulogin = sperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as cperhour
FROM btstats
WHERE btiactivity = 'commit'
)as cperhour
on btusers.btulogin = cperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as rperhour
FROM btstats
WHERE btiactivity = 'review'
)as rperhour
on btusers.btulogin = rperhour.btiuser
ORDER by username , vperhour , sperhour , cperhour , rperhour
September 26, 2001 at 3:10 pm
That looks right, but I keep getting errors:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'as'.
something not right in that cast statment?
September 26, 2001 at 3:15 pm
as for the string being more than 8000 characters the solution would be to join
several variables like EXEC ( @var1 + @var2)
brian ,i think the solution using text datatype would not work as we cannot declare text variables in a SP or script..
all in all if the sql statement is going to exceed a certain length then i think its better and cleaner to look for alternative solutions like temp tables!!!
September 26, 2001 at 3:36 pm
should this line:
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as vperhour
be more like this?
rperhour = (sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60))
I tried it this way and now:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'btstats.btiuser' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
September 26, 2001 at 3:41 pm
i should go and get some sleep 😉
its a group by clause thats missing , sorry for that
select btulogin as username ,
ISNULL ( vperhour , 'n/a') as vperhour ,
ISNULL ( sperhour , 'n/a') as sperhour ,
ISNULL ( cperhour , 'n/a') as cperhour ,
ISNULL ( rperhour , 'n/a') as rperhour
from btusers
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as vperhour
FROM btstats
WHERE btiactivity = 'verify'
GROUP by btiuser
) as vperhour
on btusers.btulogin = vperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as sperhour
FROM btstats
WHERE btiactivity = 'scan'
GROUP by btiuser
)as sperhour
on btusers.btulogin = sperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as cperhour
FROM btstats
WHERE btiactivity = 'commit'
GROUP by btiuser
)as cperhour
on btusers.btulogin = cperhour.btiuser
LEFT OUTER JOIN
(
SELECT btiuser ,
cast ((sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60) as varchar(20)) as rperhour
FROM btstats
WHERE btiactivity = 'review'
GROUP by btiuser
)as rperhour
on btusers.btulogin = rperhour.btiuser
ORDER by username , vperhour , sperhour , cperhour , rperhour
September 26, 2001 at 4:27 pm
"Divide by zero error encountered."
If the user doesn't have any stats matching the criteria, it will bomb on 0. I guess it's back to temprorary tables, then. The current solution works for now, but I'll need to redo this very soon.
Thank you everyone for the help!
September 26, 2001 at 5:04 pm
oh ye of little faith,
i think the query can be refined a bit to look for 0s'
this subquery can be modified like this
SELECT btiuser ,
cast (CASE
WHEN sum(datediff(second, btiin, btiout) > 0 THEN
(sum(btipages) / sum(datediff(second, btiin, btiout)/ 60 / 60)
ELSE 0
END
as varchar(20)) as rperhour
FROM btstats
WHERE btiactivity = 'review'
GROUP by btiuser
September 26, 2001 at 6:02 pm
I was a disbeliever, it's true!
I ended up going with a temporary table, though, before that reply was posted. It was just a little easier for me to do.
Thanks again for all of your help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply