Assigning value of a search

  • 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!

  • 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

    steve@dkranch.net

  • 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)

  • 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

  • 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)

  • 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

    steve@dkranch.net

  • 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?

  • 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

    steve@dkranch.net

  • 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.

  • 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

    steve@dkranch.net

  • why don't you try sp_executesql

    here's an example for the pubs database

    DECLARE @Qtyint,

    @Sqlnvarchar(100),

    @Defnvarchar(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