Select from stored procedure

  • Comments posted to this topic are about the item Select from stored procedure

  • Nice way to get it to work!

    I don't see why it shouldn't be used in production environment, it just needs to be a bit refined... I wouldn't include connection strings in the statement, but I don't see it nor dangerous nor unstable, connection strings apart.

    I coded something similar a couple of years ago to work around limitations on insert / exec, but I used CLR to achieve it, providing a connection key mapped to a small table holding connection information.

  • Unrelated suggestion or correction:

    I noticed you used this statement to remove the procedure before recreating it

    if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')

    drop procedure dbo.proc_simple

    go

    I've always been using this instead. Is what I'm doing wrong in any way?

    if object_id('dbo.proc_simple','P') is not null

    drop procedure dbo.proc_simple

    go

  • IMHO not.

    Your way seems to me to be more "clean" and I think that it is better than mine (which is perhaps unnecessarily to sofisticated than it should).

    It is just my bad habit to drop objects in this way.

  • Er, I hope it is not a dumb quesiton, but I do not see what go again over just running the stored proc...

  • I use this method when I need a dynamic solution:

    use AdventureWorks2000

    go

    if exists(select * from sysobjects where id = object_id('dbo.proc_dynamic') and type = 'P')

    drop procedure dbo.proc_dynamic

    go

    create procedure dbo.proc_dynamic

    @name varchar(50)

    as

    begin

    select LocationId, [Name], rowguid from dbo.Location where [Name] = @name

    end

    go

    DECLARE @SELECT varchar(1000),

    @NAME varchar(50)

    SET @NAME = 'Location'

    SET @SELECT = 'SELECT * FROM OPENQUERY([' + @@SERVERNAME + '],''exec AdventureWorks2000.dbo.proc_dynamic ''''' + @Name + ''''' '')'

    EXEC(@SELECT)

    go

  • To connect to remote server:

    [font="Courier New"]select * from openrowset('SQLOLEDB', 'Uid=myUsername;Pwd=myPassword;Server=ServerNameOrAddress;Database=AdventureWorks2000', 'exec dbo.proc_simple')[/font]

  • Good idea! But just for few rows result set. Else it takes too long to run if put the result set in join with another table.

  • The first statement is very close to what SQL Server generates when you script a procedure, except that the last condition in the WHERE clause is

    [font="Courier New"] and OBJECTPROPERTY(id, N'IsProcedure') = 1)[/font]

    instead of

    [font="Courier New"]and type = 'P'[/font]

    I was just using this statement out of habit, did not think of doing it in a simpler way, such as yours. Nice one.

    I love this forum, learning something new everyday.

  • I think more explanation is needed about what your script is demonstrating. Is your specific intent to demonstrate how to select from a "remote" stored procedure?

    When I want to return a filtered or transformed result from a stored procedure, I will insert into a table variable or temp table like so. This seems cleaner, because it required no connection string.

    create procedure dbo.proc_simple as

    select top 5 object_id, name from sys.objects

    go

    declare @objects table ( obj_id int, obj_name varchar(180) );

    insert into @objects ( obj_id, obj_name )

    exec dbo.proc_simple;

    select * from @objects;

    obj_id obj_name

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

    4 sysrowsetcolumns

    5 sysrowsets

    7 sysallocunits

    8 sysfiles1

    13 syshobtcolumns

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (12/14/2011)


    I think more explanation is needed about what your script is demonstrating. Is your specific intent to demonstrate how to select from a "remote" stored procedure?

    When I want to return a filtered or transformed result from a stored procedure, I will insert into a table variable or temp table like so. This seems cleaner, because it required no connection string.

    create procedure dbo.proc_simple as

    select top 5 object_id, name from sys.objects

    go

    declare @objects table ( obj_id int, obj_name varchar(180) );

    insert into @objects ( obj_id, obj_name )

    exec dbo.proc_simple;

    select * from @objects;

    obj_id obj_name

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

    4 sysrowsetcolumns

    5 sysrowsets

    7 sysallocunits

    8 sysfiles1

    13 syshobtcolumns

    +1

    This is the way I do it. Then you have a table you can join with too if necessary.

  • We have many situation where we run into this issue using an old "API" that used rowsets for results from stored procs.

    Our solution, which does not require a connection string, is as follows:

    use AdventureWorks2000

    go

    if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')

    drop procedure dbo.proc_simple

    go

    create procedure dbo.proc_simple

    as

    begin

    select LocationId, [Name], rowguid from dbo.Location

    end

    go

    select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=AdventureWorks2000', 'exec dbo.proc_simple')

    go

    -- Declare target variable

    DECLARE @LocationID smallint;

    -- Create Temp table to receive resultset (Table variables will not work)

    CREATE TABLE #TmpProcSimple (

    LocationId smalllint,

    [Name] varchar(50),

    rowguid uniqueidentifier

    )

    -- Execute into Temp table

    INSERT INTO #TmpProcSimple

    EXECUTE dbo.proc_simple;

    -- Select value from Temp table

    SELECT @LocationID = LocationID

    FROM #TmpProcSimple;

    GO

Viewing 12 posts - 1 through 12 (of 12 total)

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