SQL Server Performance

How toinsert into table result of stored procedure

Discussion in 'General Developer Questions' started by NewDBA, Jul 23, 2003.

  1. NewDBA New Member

    This is actually the question .
    I need to do something like that

    inserti into myTable
    exec sp_helpdb

    How i can do it?
  2. bambola New Member

    Just like you have said <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br />Create a [temp] table with all the values returned by sp_helpdb (CHECK BOL for that), and run<br /><pre><br />insert into #temp<br /> exec sp_helpdb<br /></pre><br />Bambola.<br /> <br />
  3. NewDBA New Member

    Sorry wrong question
    I try to create a view or UDF:

    CREATE FUNCTION fnc_currentDatabases ()
    RETURNS @currentDatabases table
    (name nvarchar(24),
    db_size nvarchar(13),
    owner nvarchar(24),
    dbid smallint,
    created char(11),
    status varchar(340),
    compatibility_level tinyint

    )



    AS
    BEGIN
    --SET ANSI_NULLS OFF
    INSERT into @currentDatabases
    exec sp_helpdb
    return

    END

    I need to have something dynamic



  4. bambola New Member

    The problem is that you cannot use table datatype in INSERT INTO EXEC...

    Bambola.
  5. bambola New Member

    You also cannot create a temporary table inside a function.
    I unserstand you are looking for a way to tun a direct SELECT on the result set.
    Maybe in this case you can benefit from Nathan's (vbkenya) idea and do it using linked server and OPENQUERY.
    I'll try to find the relevent post.

    EDIT: here it is
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1001

    Bambola.
  6. Twan New Member

    I also thought that you can't call stored procedures from a function unless it is an extended stored procedure...

    You can however call a function from a procedure, so another option may be to put the procedure code into the function, and then call this function from the procedure to return the same result set as the proc does now...

    Twan
  7. bambola New Member

    What NewDBA was trying to do was insert the result set of exec sp_helpdb into a table. I don't see how you could do it within a function...

    Bambola.


  8. Adriaan New Member

    Why not look at the definition of sp_helpdb to see how the information is collected? You could also create a view, and use that to retrieve the data instead of calling a UDF.
  9. Adriaan New Member

    Something like this ... I know we shouldn't query system tables, but where else would you find this?

    SELECT DB.[Name] AS DBName, SUSER_SNAME(DB.sid) AS Owner,
    CONVERT(NVARCHAR(11), DB.crdate, 120) AS DateCreated, DB.dbid, DB.cmptlevel,
    DATABASEPROPERTYEX(DB.[Name], 'Status') AS Status,
    DATABASEPROPERTYEX(DB.[Name], 'Updateability') AS Updateability,
    DATABASEPROPERTYEX(DB.[Name], 'UserAccess') AS UserAccess,
    DATABASEPROPERTYEX(DB.[Name], 'Recovery') AS [Recovery],
    DATABASEPROPERTYEX(DB.[Name], 'Version') AS Version,
    CASE WHEN DATABASEPROPERTY(DB.[Name], 'IsShutdown') = 0
    THEN DATABASEPROPERTYEX(DB.[Name], 'Collation') ELSE '' END AS Collation,
    CASE WHEN DATABASEPROPERTY(DB.[Name], 'IsShutdown') = 0
    THEN DATABASEPROPERTYEX(DB.[Name], 'SQLSortOrder') ELSE '' END AS SQLSortOrder,
    DATABASEPROPERTYEX(DB.[Name], 'AutoClose') AS AutoClose,
    DATABASEPROPERTYEX(DB.[Name], 'AutoShrink') AS AutoShrink,
    DATABASEPROPERTYEX(DB.[Name], 'IsInStandby') AS IsInStandby,
    DATABASEPROPERTYEX(DB.[Name], 'IsTornPageDetectionEnabled') AS IsTornPageDetectionEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsAnsiNullDefault') AS IsAnsiNullDefault,
    DATABASEPROPERTYEX(DB.[Name], 'IsAnsiNullEnabled') AS IsAnsiNullEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsAnsiPaddingEnabled') AS IsAnsiPaddingEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsArithmeticAbortEnabled') AS IsArithmeticAbortEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
    DATABASEPROPERTYEX(DB.[Name], 'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
    DATABASEPROPERTYEX(DB.[Name], 'IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsFullTextEnabled') AS IsFullTextEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
    DATABASEPROPERTYEX(DB.[Name], 'IsNullConcat') AS IsNullConcat,
    DATABASEPROPERTYEX(DB.[Name], 'IsNumericRoundAbortEnabled') AS IsNumericRoundAbortEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
    DATABASEPROPERTYEX(DB.[Name], 'IsMergePublished') AS IsMergePublished,
    DATABASEPROPERTYEX(DB.[Name], 'IsPublished') AS IsPublished,
    DATABASEPROPERTYEX(DB.[Name], 'IsSubscribed') AS IsSubscribed,
    DATABASEPROPERTYEX(DB.[Name], 'IsSyncWithBackup') AS IsSyncWithBackup

    FROM master.dbo.sysdatabases AS DB
  10. bambola New Member

    quote:Originally posted by Adriaan

    Why not look at the definition of sp_helpdb to see how the information is collected?
    I actually did. sp_helpdb uses a temporary table, open a cursor on it and gets the size of the databases. So I figures it is not something you can do in a function.

    Bambola.
  11. Adriaan New Member

    quote:Originally posted by bambola


    quote:Originally posted by Adriaan

    Why not look at the definition of sp_helpdb to see how the information is collected?
    I actually did. sp_helpdb uses a temporary table, open a cursor on it and gets the size of the databases. So I figures it is not something you can do in a function.

    Bambola.

    If the client application supports pass-through queries, you can set one up to call a stored procedure - one that creates a temp table, fills it with the data you need and then returns the records from the temp table in a final SELECT statement.

    The most important thing that is missing in UDFs must be the ability to call sp_ExecuteSQL, or in general: use dynamic T-SQL.

    Adriaan
  12. Twan New Member

    There is a good reason for that though... UDFs are a great tool, but can be very dangerous in terms of performance and possibilities on how it is used. If Dynamic SQL was allowed then there is nothing stopping you from doing anything you like. This can get rather odd if you then use the UDF in a select statement...

    I don't particularly like dynamic sql as a way of building applications... it is cumbersome to use, hard to debug, bad for security, and usually unnecessary (there will be exceptions I'm sure)

    Cheers
    Twan
  13. Adriaan New Member

    Some of my UDFs can be called from views to include a list of related values from a subtable in the main resultset. I know the set-based method for this horizontalization (and it performs much better than a cursor-based method) but it would be nice to improve this further by having some sort of a "sp_ExecuteSQL_NoUpdatesNoDeletes" procedure that we can call from UDFs, triggers and views; I'm thinking of the benefit of re-usable execution plans.

    I know all too well what a mess you can get when setting up dynamic SQL, but unfortunately you cannot always avoid it. Sometimes the alternative would be an extremely long procedure to cover all situations, which can be even more difficult to trouble-shoot.

    Another dream-wish would be to be able to derive a field name 'just in time' in triggers, procedures, views and functions, instead of having to cover all possibilities in a CASE construct or - again - having to use dynamic SQL.

    Adriaan

Share This Page