Post reply

Can you 'Select' from a stored procedure

  • July 15, 2010 at 11:14 am

    #97235

    Hi all,

    This is a two part question, but first. I ain't got any data or table structure to post. It's more of a can I / how can I general question.

    Part A) I have a stored procedure that queries from an OpenQuery. When I execute everything looks fine like a select statement from any ole' table. I know that if I create a simple query "Select * from sp_MyRemoteTable" I get invalide object name sp_MyRemoteTable. So part A is can I select from the stored procedure. It more of a test question than practicle application, for now. It makes it easier to test and there are other things I can do etc...

    Part B) The other reason I used stored procedures is because I declare variables and have input parameters, etc... If I can Select * From a Stored Procedure, can I pass it the value of a (or more) parameter(s)?

    Let me know if I'm just barking up the wrong tree. Still being a novice I tend to go off in directions to try things (or other things, or other things) until I find what works or what works better.

    Thank you in advance for your help,

  • July 15, 2010 at 11:22 am

    #1193655

    What you are looking for is the INSERT...EXEC construct. Consider:

    INSERT INTO @tablevariable

    EXEC sp_MyRemoteTable

    You will need to prepare the table variable (or a temporary table will work) ahead of time to have a schema that matches the data that will be returned.

    Note that you can not nest INSERT..EXEC constructs. I have run into this often. This means while you can return the results of a stored procedure (or dynamic SQL) into a table variable or temp table, you can not then wrap this inside stored procedure and try to do the same with it's results.

    --J

  • July 15, 2010 at 11:23 am

    #1193656

    personally I usually put the results of a proc into a temp table, like this:

    CREATE TABLE #WHORESULTS (

    [WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [SPID] CHAR (5) NULL,

    [INSERTDT] DATETIME NULL DEFAULT(GETDATE()),

    [STATUS] VARCHAR(30) NULL,

    [LOGIN] VARCHAR(30) NULL,

    [HOSTNAME] VARCHAR(30) NULL,

    [BLKBY] VARCHAR(30) NULL,

    [DBNAME] VARCHAR(30) NULL,

    [COMMAND] VARCHAR(30) NULL,

    [CPUTIME] INT NULL,

    [DISKIO] INT NULL,

    [LASTBATCH] VARCHAR(30) NULL,

    [PROGRAMNAME] VARCHAR(200) NULL,

    [SPIDINT] INT NULL

    )

    --table exists, insert some data

    INSERT INTO #WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)

    EXEC sp_who2

    but by using the openrowset method, and pointing it to your owns erver, you can query the results of a stored proc:

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who')

    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!

  • July 15, 2010 at 11:25 am

    #1193657

    You can select from the openquery

    select * from openquery(...)

    http://msdn.microsoft.com/en-us/library/ms188427.aspx

  • July 15, 2010 at 11:34 am

    #1193662

    Wow, lots of great answers.

    First, for some reason I never thougth of using "execute" insteat of select. Brillant! Thanks, I'm still learning.

    Second, yeah, I used to put all of my OpenQueries in Views but that caused two problems. A) I couldn't edit them, I had to recreate them, bummer. Not sure why, I'm using Management console Version 9 with Version 8 MSSQL maybe that has something to do with it. B) The more tables and views I have the more connections I end up with in the Front End application. I don't know if this is good or bad but it gets to be a lot. I started using Pass-Through queries and change them on the fly to whichever stored procedure I need.

    Lastely, I've looked at temp tables and honestly think they'd solve some of my issues but quite honestly I just don't know enough abou them. I need to find a good book and a weekend to go over it. Possibly an on-line web tutorial. But for now...

    Thanks guys,

    I appreciate it. Sorry I get stuck on some of the simplist answers, still learning.

Viewing 5 posts - 1 through 4 (of 4 total)

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