Post reply

strange query to get data

  • February 12, 2010 at 9:28 am

    #210871

    Hello Everyone

    Someone was asking me how this may be accomplished. I told them to create a function and have it return the value into a variable.

    Here is the jest of what they are wanting.

    Insert a new row into a table using a simple Insert statement

    One of the values is the resultset from a stored procedure.

    If you could write it out, it would look like this

    INSERT INTO Table1

    (DateOfInsert, RecordCount)

    VALUES

    (

    GETDATE()

    , (exec stored_procedure_name_to_get_Count)

    )

    I told them that could not be accomplished that way.

    Can anyone suggest a means to use the resultset from one stored procedure, and place that into a variable to be used in an Insert statement? But without using a function.

    Thanks

    Andrew SQLDBA

  • February 12, 2010 at 9:37 am

    #1118118

    Hi Andrew,

    Pass your output parameter from the stored procedure in using a variable. Here's a simple example:

    create procedure dbo.test

    @input int

    , @output int output

    as

    begin

    select @output = @input + 1

    end

    -----------------------------

    declare @result int

    exec dbo.test

    @input = 1

    , @output = @result output

    select @result

    Regards, Iain

  • February 12, 2010 at 9:53 am

    #1118127

    Thanks Dood

    That one works perfectly.

    I think that I failed to mention that the stored procedure does not have an Output param. I am sure that one can be added.

    Thanks

    Andrew

  • February 12, 2010 at 9:59 am

    #1118135

    No worries, happy to help.

    Just noticed that you mention 'resultset' in your OP. If the output from your proc is multiple rows then you won't be able to do this. You'll need to push the output to a table and insert from there.

  • February 12, 2010 at 10:34 am

    #1118147

    The resultset will always be a single integer. This one is only performing a count or the rows

    Thanks

    Andrew SQLDBA

  • February 12, 2010 at 11:15 am

    #1118172

    Andrew I'm second guessing the purpose of the function....

    is it just getting the # rows for a given table? is it being called 1000 times, one for each table?

    if that is true, then there is a MUCH faster way to get the # of rowes for all your tables:

    Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id

    From sys.partitions p

    Inner Join sys.sysobjects o

    on p.object_id = o.id

    Where index_id in (0,1)

    And o.type = 'U'

    Group By object_id,index_id

    Order By NumRows Desc

    --or another way

    --need to update to make sure row counts are accurate

    dbcc updateusage (0) with count_rows

    SELECT OBJECT_SCHEMA_NAME(id)

    ,OBJECT_NAME(id)

    FROM SYS.SYSINDEXES WITH (NOLOCK)

    WHERE indid IN (0, 1)

    AND [rowcnt] = 0 ;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 12, 2010 at 11:33 am

    #1118189

    What I actually wanted was a query that counted some records that met a certain criteria, instead of just a count of all the rows. I was only keeping things simple

    I think that I have figured out a way by using a temp table

    Thanks

    Andrew SQLDBA

  • February 12, 2010 at 5:32 pm

    #1118371

    AndrewSQLDBA (2/12/2010)

    I think that I have figured out a way by using a temp table

    Thanks

    Andrew SQLDBA

    Cool... can you share it please?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply