February 25, 2002 at 11:10 am
I have a stored procedure that builds a select statement based on search options in a web page. At the end of the procedure, I have a command:
exec @search
so that it returns the values in the custom-built select statement. Now my users are asking me for a sum value for a field of the values selected. I built another select statement alongside the other one that selects the sum of the field in question and not all the other fields. I want to return this value inside the first select.
My question is this. I now have the sum select statement stored in another variable. Is there a way to assign the value of that select statement to an integer variable? Kind of like this:
declare @sum int
set @sum = (exec(@searchSum))
The example above obviously does not work. Any ideas?
Thanks!
February 25, 2002 at 11:13 am
You can't really do this because the exec() statement essentially "shells" to another batch. The best way would be to build a table (perm or global temp ) and then reference this inside the exec. So your stmt would be
select @stmt = 'insert myTable select ...'
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 25, 2002 at 11:19 am
Or post your code and let us see if there is another option, also what is input would be helpfull.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 25, 2002 at 11:23 am
What you are saying is that you already have the query that gets you the correct SUM and you put that sum into a variable. you can do the following to get the variable into your first query:
SET @search = 'SELECT value, ' + LTRIM(STR(@sum)) + ' AS [SUM]'
and then add the remaining portion of your select statement.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 25, 2002 at 11:26 am
I'll post the code, but it's a little ugly at this point...
I think I may have to take your suggestion of sticking this in a temp table.
--new variables for date range
declare @date1 datetime
declare @date2 datetime
declare @date2temp datetime
--existing
declare @client varchar(50)
declare @days int
declare @state varchar(5)
declare @stage int
declare @completed int
declare @rescan int
declare @conversion int
declare @escalated int
declare @createdate datetime
declare @action int
declare @actionuser varchar(50)
declare @orderby varchar(50)
declare @ascdesc varchar(20)
declare @onoffst int
declare @created int
set @date1 = '1/1/02'
set @date2 = '1/31/02'
set @client = '%'
set @days = 0
--set @state
--set @stage
--set @completed
--set @rescan
--set @conversion
--set @escalated
--set @createdate
--set @action = 111
--set @actionuser
set @orderby = 'btbclnt'
set @ascdesc ='asc'
set @onoffst = 0
set @created = 0
--increment the second date by one so we can do a < statement and include the actual requested date
set @date2 = dateadd(day, 1, @date2)
declare @search varchar(1000)
declare @searchSum varchar(1000)
-- this exceeded maximum row size
--set @search= ('SELECT btbatch.*, bthist.*, btnotes.*, btstages.*, ptsclnt.clientid, ptsclnt.descrip FROM ((((btbatch LEFT OUTER JOIN bthist ON btbbcn = bthbcn )
--LEFT OUTER JOIN btnotes ON btbbcn = btnbcn ) LEFT OUTER JOIN btstages ON btbstage = btsstage ) LEFT OUTER JOIN ptsclnt ON btbclnt = clientid )')
set @search= ('SELECT btbpg, btbbcn, btbtfbatch, btbclnt, btbstate, btsdesc, btbin, btblastout, btstages.*, ptsclnt.clientid, ptsclnt.descrip FROM ((((btbatch LEFT OUTER JOIN bthist ON btbbcn = bthbcn )
LEFT OUTER JOIN btnotes ON btbbcn = btnbcn ) LEFT OUTER JOIN btstages ON btbstage = btsstage ) LEFT OUTER JOIN ptsclnt ON btbclnt = clientid )')
set @searchSum = ('SELECT sum(btbpg) FROM ((((btbatch LEFT OUTER JOIN bthist ON btbbcn = bthbcn )
LEFT OUTER JOIN btnotes ON btbbcn = btnbcn ) LEFT OUTER JOIN btstages ON btbstage = btsstage ) LEFT OUTER JOIN ptsclnt ON btbclnt = clientid )')
set @search = (@search + ' WHERE ')
set @searchSum = (@search + ' WHERE ')
if @client is not null
BEGIN
set @client = rtrim(@client)
set @search = (@search + 'rtrim(btbclnt) LIKE ''' + @client + '''')
set @searchSum = (@searchSum + 'rtrim(btbclnt) LIKE ''' + @client + '''')
END
--added 2/21/02
if @date1 is not null and @date2 is not null
BEGIN
set @search = (@search + ' and btbin >= ''' + convert(varchar(20), @date1) + ''' and btbin < ''' + convert(varchar(20), @date2) + ''' ')
set @searchSum = (@searchSum + ' and btbin >= ''' + convert(varchar(20), @date1) + ''' and btbin < ''' + convert(varchar(20), @date2) + ''' ')
END
--end
if @days is not null and @days !=0
BEGIN
set @days = (@days * 24 * 60)
declare @days3 varchar(5)
set @days3 = cast(@days as varchar(5))
set @search = (@search + ' AND datediff(minute, btbin, getdate()) >= ' + @days3)
set @searchSum = (@searchSum + ' AND datediff(minute, btbin, getdate()) >= ' + @days3)
END
if @state is not null
BEGIN
set @search = (@search + ' AND (rtrim(ltrim(btbstate))) LIKE ''' + @state + ''' ')
set @searchSum = (@searchSum + ' AND (rtrim(ltrim(btbstate))) LIKE ''' + @state + ''' ')
END
if @stage != 0
BEGIN
declare @stage2 varchar(5)
set @stage2 = cast(@stage as varchar(5))
set @search = (@search + ' AND btbstage = ' + @stage2)
set @searchSum = (@searchSum + ' AND btbstage = ' + @stage2)
END
if @completed = 0
BEGIN
set @search = (@search + ' AND btbstage < 8 ')
set @searchSum = (@searchSum + ' AND btbstage < 8 ')
END
if @rescan = 1
BEGIN
set @search = (@search + ' AND btbrescan = 1')
set @searchSum = (@searchSum + ' AND btbstage < 8 ')
END
if @conversion = 1
BEGIN
set @search = (@search + ' AND btbconversion = 1')
set @searchSum = (@searchSum + ' AND btbconversion = 1')
END
if @escalated = 1
BEGIN
set @search = (@search + ' AND (btbescalated2 = 1 OR btbescalated = 1)' )
set @searchSum = (@searchSum + ' AND (btbescalated2 = 1 OR btbescalated = 1)' )
END
if @onoffst = 1
BEGIN
set @actionuser = rtrim(ltrim(upper(@actionuser)))
if @action = 111
set @search = (@search + ' AND (btbsort LIKE ''' + @actionuser + ''' OR btbscan LIKE ''' + @actionuser + ''' OR btbverify LIKE ''' + @actionuser + ''' OR btbcommit LIKE ''' + @actionuser + ''' OR btbreview LIKE ''' + @actionuser + ''')')
set @searchSum = (@searchSum + ' AND (btbsort LIKE ''' + @actionuser + ''' OR btbscan LIKE ''' + @actionuser + ''' OR btbverify LIKE ''' + @actionuser + ''' OR btbcommit LIKE ''' + @actionuser + ''' OR btbreview LIKE ''' + @actionuser + ''')')
if @action = 1
BEGIN
set @search = (@search + ' AND btbsort LIKE ''' + @actionuser + ''' ')
set @searchSum = (@searchSum + ' AND btbsort LIKE ''' + @actionuser + ''' ')
END
if @action = 4
BEGIN
set @search = (@search + ' AND btbscan LIKE ''' + @actionuser + ''' ')
set @searchSum = (@searchSum + ' AND btbscan LIKE ''' + @actionuser + ''' ')
END
if @action = 5
BEGIN
set @search = (@search + ' AND btbverify LIKE ''' + @actionuser + ''' ')
set @searchSum = (@searchSum + ' AND btbscan LIKE ''' + @actionuser + ''' ')
END
if @action = 6
BEGIN
set @search = (@search + ' AND btbcommit LIKE ''' + @actionuser + ''' ')
set @searchSum = (@searchSum + ' AND btbcommit LIKE ''' + @actionuser + ''' ')
END
if @action = 8
BEGIN
set @search = (@search + ' AND btbreview LIKE ''' + @actionuser + ''' ')
set @searchSum = (@searchSum + ' AND btbreview LIKE ''' + @actionuser + ''' ')
END
END
set @search = (@search + ' AND btbactive = 1 ')
set @searchSum = (@search + ' AND btbactive = 1 ')
set @search = (@search + ' ORDER BY ' + @orderby + ' ' + @ascdesc)
declare @sum int
--set @sum= exec(@searchSum)
--print @sum
--print @search
exec (@search)
February 25, 2002 at 11:37 am
Yikes? How often would this change? If not often, I'd spend the effort to write the stored procs to handle each case and return the right results. It is a maintenance issue, but it's better performance and your time once in a great while might be more efficient than having the server run this constantly.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 25, 2002 at 11:45 am
I know this one is a doosy, but it's not run constantly. It's part of a paper-tracking product and this is a manager's report for the most part. The flexibility they want for their reporting is insane (obviously)! We move up to 15,000 pieces of paper through our organization a day and there are a lot of variables. If I were to go with seperate stored procedures, I'd have to write all kinds of nutty logic into the web page. With the way this stuff can change, it's much easier for me to keep changes at the server level.
I think I'll try dumping the results into a table and reading from there. Is this thing a huge performance hit?
February 25, 2002 at 11:55 am
Probably when you run this, there is more of a hit than you need, but if it doesn't run that often, might not be worth it. Of course, it depends on how busy you are.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 25, 2002 at 12:00 pm
Extremely busy. =)
Steve, you've been helpful as always. Thanks again, everyone. I changed it to just dump everything into a temp table at the end and I get my values from there.
One more question for my own education:
----
set @search = ('INSERT INTO btSearchTemp ' + @search)
exec (@search)
delete @btSearchTemp
----
What happens if two people run this at the same time? Does one wait for the other? Do they clash? Since I write, read and clear almost in the same breath, the overlap is minimal, but I'm curious about running something like this in busier applications.
Thanks.
February 25, 2002 at 12:06 pm
Two things:
Global temp tables, unnique to your user connection.
Perm table, include a spid column and poopulate this with @@spid when you run. Be sure to delete all matching @@spid values before and after running the insert.
so
set @search = ('delete btsearchtemp where @@spid = spid INSERT INTO btSearchTemp ' + @search )
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 25, 2002 at 12:15 pm
why don't you try sp_executesql
here's an example for the pubs database
DECLARE @Qty int,
@Sql nvarchar(100),
@Def nvarchar(50)
SET @Sql = 'SELECT @Qty = sum(qty) FROM Sales'
SET @Def = '@Qty int output'
EXEC Sp_ExecuteSql @Sql , @Def , @Qty output
PRINT @Qty
this way you don't need a temp table
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply