Hi all,
What I want to achieve is to create a SP which accepts a SP name as input
and  then declares and opens a cursor on that stored procedure, queries the
output columns of the SP (using sp_describe_cursor catalog procedures) and
then constructs a temporary table which is an exact representation of the
schema of the SP's resultset.

The article
http://www.databasejournal.com/features/mssql/article.php/1474571
demonstrates how to do the above for SELECT statements.
I would like to be able to do the same thing for SPs.

If i could do that, I would no longer have to manually code a CREATE TABLE
#temporary table each time I need to store the results of a SP like e.g. when
using INSERT #tmp EXEC SP


EXAMPLE:
Instead of coding:

CREATE TABLE #aTempTableDefinitionWhichTakesSoMuchTimeToFigureOutAndToType
(
col1 int
,col2 varchar(2)
,col3 varchar(3)
,col4 varchar(4)
,col5 varchar(5)
....
)

INSERT #aTempTableDefinitionWhichTakesSoMuchTimeToFigureOutAndToType
EXEC   sp_I_want_to_call

I could then code:

sp_TableGenerator 'sp_I_want_to_call @mandatoryparam1=555,
@mandatoryparam2=999', '#myTempTableTobeCreatedBySp_TableGenerator'

INSERT #myTempTableTobeCreatedBySp_TableGenerator
EXEC   sp_I_want_to_call @mandatoryparam1=123, @mandatoryparam2=5687,
@optionalparam3='blablabla'


After all SYBASE does is, so why cant SQL Server?
Here is an except from Sybase documentation.
Executing Stored Procedures
An application can execute a stored procedure with DBMS SQL and the engine’s
command for execution, EXEC. For example:
DBMS SQL [ DECLARE parameter data-type \
[ DECLARE parameter data-type ... ] ] \
EXEC procedure-name [ parameter [, parameter ...] ]
Using the
Default Error
Handler
Using an
Installed Error
Handler
Using Stored Procedures
16 Database Drivers: Panther 4.25
An application can also use a named cursor to execute a stored procedure:
DBMS DECLARE cursor CURSOR FOR \
[ DECLARE parameter data-type [ DECLARE parameter data-type ... ] ] \
EXEC procedure-name [ parameter [, parameter ...] ]
The cursor can then be executed with the following statement:
DBMS [ WITH CURSOR cursor ] EXECUTE [ USING values ]

Output parameters and return codes are not supported for stored procedures
in this
release of Prolifics for SYBASE Client-Library.
For example, update_tapes is a stored procedure that changes the video tape
status to O whenever a video is rented.
create proc update_tapes @parm1 int, @parm2 int
as
update tapes set status = ’O’
where title_id = @parm1 and copy_num = @parm2
The following statement executes this stored procedure, updating the status
column of the tapes table using the onscreen values of the widgets title_id
and
copy_num.
DBMS SQL EXEC update_tapes :+title_id, :+copy_num
DBMS DECLARE x CURSOR FOR EXEC update_tapes \
DBMS WITH CURSOR x EXECUTE USING title_id, copy_num

select top 1 * into #TempTable from MyTable
truncate table #TempTable

also creates an empty temporary table of the same structure like the queried
table.

Why do you need a complicated cursor based routine?

Further to that, why does it take so long to figure out the column layout?
And why is a #temp table needed at all?  Why are we too lazy to understand
the interface and code it correctly?

Yes but your suggestion only works with tables.
There is no such thing like

select top 1 *
into #TempTable
EXEC MyStoredProcedure

Because of this, one has to examine the resultset of the SP and has to
manually create a temp table, to be able to do the following:

INSERT #MyTempTable
EXEC    MyStoredProcedure

I would like to avoid this overhead. Thatswhy I am working on a SP which can
take a SP name as a parameter, execute it behind-the-scenes, analyse the
metadata of its resultset to figure out
..) the number
..) the ordering
..) the names
..)t he data types including the precision and scale
of all columns contained in the resultset.
Finally it should dynamically construct an appropriate CREATE TABLE
statement and execute it.

It's possible to analyse the resultset of a SELECT query by declaring a
cursor on it
like e.g.
DECLARE CURSOR mycur FOR SELECT * FROM myTable

and then analysing the cursor metadata using the catalog system functions
for cursors
sp_describe_cursor
sp_describe_cursor_columns etc

However SQL Server does not seem to support a cursor on a SP like e.g.
DECLARE CURSOR mycur FOR EXEC MyStoredProcedure

so I just wonder whether there is a workaround for this...


Show quoteHide quote

And what if it has more than one resultset?  Or no resultsets?  Or only uses
output and/or return parameters?

Can you explain why you need to build a generic procedure that can execute
another procedure and blindly insert its results into a dynamically built
temp table?  How on earth are you going to manage such a beast?

I'd bet the number of columns is 666.


ML

You can create a stored procedure with a cursor output parameter.

One thing, the stored proc you're discussing won't work.  CREATE #TEMP
executed within an sp creates a temp table, but it is dropped when the sp
goes out of scope.

Show quoteHide quote

ok, i c that there is some confusion about what I want to achive with this SP
and why so let me give u a few brief examples.

1.)
I need to write a SP which can take any SELECT statement as input, execute
it and stored the result in a temporary table for further processing.

I could try the following:
CREATE PROCEDURE sp_execute_customsql @SELECT as varchar(8000)
AS
BEGIN
DECLARE @sql as varchar(8000)
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #tmp' + CHAR(13)
SET @sql = @sql + @SELECT
EXEC (@sql)

but the problem is, that the #tmp will be discarded as soon as the EXEC
statement goes out of scope.

The workaround to this problem is decribed in the article
http://www.databasejournal.com/features/mssql/article.php/1474571

Now, I would like to be able to do the same thing (as this seems to be the
only possible way) for SPs.

It would be just so practical if I could write code which could use a SP as
it is (ie without any modifications to it) and populate a temp table with its
resultset WITHOUT having to first manually prepare a table which matches the
schema of the resultset.

Aaron has replied:
"And what if it has more than one resultset?  Or no resultsets?  Or only uses
output and/or return parameters?"

The method described in the article mention is totaly fleixble with regard
to the schema of the resultset. It simply declares a cursor on the resultset
and then queries the metadata of the cursor to figure out which columns,
which datatypes etc... So if it was possible to make the same method for also
for SPs, the resultsets being different from call to call would not be an
issue.

Of course return parameters and output parameters would have to handled
extra, but this is already outside of the scope of my original question which
was

is it possible to
DECLARE CURSOR myCur FOR EXEC mySP ?

it is possible in Sybase and if it is not possible in SQL Server (which I
think it is not), then are there any workarounds?

Regards
biramen



Show quoteHide quote

I don't think much of the technique shown in the article you posted. It
isn't obvious why this would be useful in a well-designed system. It
relies on cursors and dynamic SQL with all that implies. What's wrong
with explicitly creating a table that matches the known output of your
SP? Writing code that dynamically creates objects at design time is not
the way to build a reliable database. In fact it's usually seriously
unreliable and kludgy way of doing anything. Your point about saving
the "overhead" in development would surely be more than offset by the
time wasted on *support* for a system like this.

Also, your requirement seems to be founded on the assumption that this
is necessary to pass data between procs. Moving chunks of data around
the like this is not the way databases are supposed to work. You would
be better off writing procs that share the same data in permanent
tables rather than creating temporary images of the data. If you must
use temp tables then create them at the highest calling level of proc
and your local temp tables will still be accessible at lower levels -
they won't go out of scope.

You would do better to concentrate on best practice techniques in MSSQL
rather than try to replicate redundant features familiar to you from
Sybase.

Correction:

Should read at RUN TIME not design time. Design time is exactly when
you DO define objects. (How will you design code that uses the table
anyway if you don't know what the table looks like?)

Or just running the SP?  Why the need to stuff its results into a temp
table???

Absolutely!

Yes, Ottoman if you really need to pass data between procs, see
http://www.sommarskog.se/share_data.html ... however I think you can avoid
all of this by just writing the base procs properly in the first place.

ok, as i c us debating far more on why and if a feature like this is needed
that how it can be implemented, I have attached below an example of a SP
which should make it u all more clear why i would like to have a feauture
like the one we are discussing.

The below SP needs, to achieve its purpose, to join the results of 4 system
stored procedures, namely
sp_who
sp_lock
sp_MShasdbaccess
sp_serverrolemembers

While writing the code, I had to prepare several temp tables to hold the
results of the SPs. For this, I had to open the BOL, find the descriptions of
the SPs, copy and paste the resultset definition of these SPs and then strip
it of all the explanatory text parts. This is a stupid and time consuming
task.

How easy life would be, if I could simply do the following:
sp_tableGen 'sp_who', '#sp_who_results'

and valla, the temp table is created and ready to be populated using
INSERT #sp_who_results
EXEC    sp_who

AGAIN, this is just one example demonstrating the need for such a utility.
There are more situations where such a utility would come very handy.

Here is another example. Just imagine u r able to write a SP which takes a
SPname as argument, which it then executes with any specified set of
parameters and thenwrites the result into a resultset table. Wouldnt that be
GREAT for testing or monitoring?

e.g.
sp_tableGen 'sp_ToBeExecuted @param1=99, @param2=''ABC'' ',
'ResultSetTableName'

Speaking generally, I think it would allow for very powerful and robust
programming if it was possible to write a generic SP which can execute any SP
using any specified paramter combination and is able to handle any resultset
those SPs might produce.

Below, the code of my SP I mentioned at the beginning. With a utility like
we are discussing, I could have omitted writing all these dull CREATE TABLE
stuf..

CREATE PROCEDURE dbo.p_monitor_processes_and_locks_drkw
(
@loginname        varchar(255) = NULL,
@hostname        varchar(255) = NULL,
@sourcedb         varchar(255) = NULL,
@targetdb         varchar(255) = NULL,
@orphaned         varchar(255) = 0,
@systemprocesses  varchar(255) = 0,
@userprocesses  varchar(255) = 1,
@blockedonly          varchar(255) = 0,
@process_status        varchar(255) = NULL,
@lock_status        varchar(255) = NULL,
@debug           varchar(255) = 0
)

AS
  --************************************************************
  --* Purpose:                                                 *
  --*                                                          *
  --* Inputs:                                                  *
  --*                                                          *
  --* Returns:                                                 *
  --*                                                          *
  --* Notes:                                                   *
  --************************************************************

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

-- declare error/debug variables

  DECLARE @proc_name  SYSNAME      -- procedure name
  DECLARE @status     INT          -- return status
  DECLARE @error      INT          -- saved error context
  DECLARE @rowcount   INT          -- saved rowcount context
  DECLARE @msg        VARCHAR(255) -- error message text
  DECLARE @tmp_user   VARCHAR(256) -- temp username storage
  DECLARE @granted    bit          -- Security return value

-- initialise error/debug variables

  SELECT @proc_name = OBJECT_NAME( @@PROCID ),
         @status    = 0,
         @error     = 0,
         @rowcount  = 0,
         @tmp_user  = system_user,
         @granted   = 0

-- declare local variables

DECLARE @sql nvarchar(4000)
DECLARE @param nvarchar(50)

-- init local variables





-- perform permission check

EXEC @status = p_sec_chk_permission @user_name                          =
@tmp_user,
                                    @permission_id                      = 300,
                                    @context_value                      =
@proc_name,
                                    @granted                            =
@granted OUTPUT

-- check procedure return code

IF @status <> 0
  BEGIN
    RAISERROR (100320,-1,-1,@proc_name)
    RETURN (@status)
  END

-- check procedure result

IF @granted <> 1
  BEGIN
    RAISERROR (100321,-1,-1,@proc_name)
    RETURN (-1)
  END

-- validation of params

IF @debug  <0 OR @debug >1
BEGIN
        RAISERROR('%s: @debug can only be one of the values NULL, 0 or 1.',
16, 1, @proc_name)
        RETURN (-1)
END

IF @blockedonly  <0 OR @blockedonly >1
BEGIN
        RAISERROR('%s: @blockedonly can only be one of the values NULL, 0 or
1.', 16, 1, @proc_name)
        RETURN (-1)
END

IF @orphaned  <0 OR @orphaned >1
BEGIN
        RAISERROR('%s: @orphaned can only be one of the values NULL, 0 or
1.', 16, 1, @proc_name)
        RETURN (-1)
END

IF @userprocesses  <0 OR @userprocesses >1
BEGIN
        RAISERROR('%s: @userprocesses can only be one of the values NULL, 0
or 1.', 16, 1, @proc_name)
        RETURN (-1)
END

IF @systemprocesses  <0 OR @systemprocesses >1
BEGIN
        RAISERROR('%s: @systemprocesses can only be one of the values NULL,
0 or 1.', 16, 1, @proc_name)
        RETURN (-1)
END

IF  (@process_status IS NOT NULL AND @process_status <>'') AND NOT 
@process_status IN ('BACKGROUND', 'SLEEPING', 'RUNNABLE')
BEGIN
        RAISERROR('%s: @process_status can only be one of the values
''BACKGROUND'', ''SLEEPING'', ''RUNNABLE''.', 16, 1, @proc_name)
        RETURN (-1)
END

IF NOT (@lock_status IS NOT NULL OR @lock_status<>'') AND NOT  @lock_status
IN ('GRANT', 'WAIT', 'CNVRT')
BEGIN
        RAISERROR('%s: @lock_status can only be one of the values ''GRANT'',
''WAIT'', ''CNVRT''.', 16, 1, @proc_name)
        RETURN (-1)
END


-- Execute your code

CREATE TABLE #sp_who_results
(
spid smallint ,
ecid smallint ,
status nchar(30) ,
loginame nchar(128) ,
hostname nchar(128) ,
blk char(5) ,
dbname nchar(128) ,
cmd nchar(16)
)

IF @loginname IS NOT NULL AND @loginname<>''
        INSERT INTO #sp_who_results
        EXEC sp_who @loginname
ELSE
        INSERT INTO #sp_who_results
        EXEC sp_who


CREATE TABLE #sp_who_results_filtered
(
spid smallint ,
ecid smallint ,
status nchar(30) ,
loginame nchar(128) ,
hostname nchar(128) ,
blk char(5) ,
dbname nchar(128) ,
cmd nchar(16)
)

CREATE TABLE #sp_lock_results
(
spid smallint ,
dbid smallint ,
ObjId int ,
IndId smallint ,
Type nchar(4) ,
Resource nchar(16),
Mode nvarchar(8) ,
Status nvarchar(5)
)

CREATE TABLE #objectnames
(
dbid smallint ,
ObjectId int ,
objectname varchar(255)
)

CREATE TABLE #hasDBaccess
(
dbname  varchar(255)
,owner  varchar(255)
,col3  varchar(255)
,col4  varchar(255)
,col5  varchar(255)
,col6  varchar(255)
,col7  varchar(255)
,col8  varchar(255)
,col9  varchar(255)
,col10  varchar(255)
,col11  varchar(255)
,col12  varchar(255)
,col13  varchar(255)
,col14  varchar(255)
)

INSERT #hasDBaccess
EXEC sp_MShasdbaccess

ALTER TABLE #hasDBaccess
DROP COLUMN col3 ,col4  ,col5  ,col6  ,col7  ,col8  ,col9  ,col10  ,col11 
,col12  ,col13  ,col14 

CREATE TABLE #srvrolemembers
(
ServerRole sysname,
MemberName sysname,
MemberSID varbinary(85)
)

INSERT #srvrolemembers
EXEC sp_helpsrvrolemember 'sysadmin'


SET @sql = ''
SET @sql = @sql + 'SELECT t2.* FROM #hasDBaccess t1 INNER JOIN
#sp_who_results t2 ON t1.dbname = t2.dbname WHERE 1=1 '

IF @hostname IS NOT NULL AND @hostname<>''
        SET @sql = @sql + 'AND hostname=''' + RTRIM(@hostname) + ''''

IF @sourcedb IS NOT NULL AND @sourcedb<>''
        SET @sql = @sql + 'AND dbname=''' + RTRIM(@sourcedb) + ''''

IF @blockedonly = 1
        SET @sql = @sql + 'AND blk>0 '

IF @orphaned = 1 OR @systemprocesses = 1 OR @userprocesses = 1
        BEGIN
        SET @sql = @sql + 'AND '
        SET @sql = @sql + '( '
        SET @sql = @sql + '1=2 '

        IF @orphaned = 1
                SET @sql = @sql + ' OR SPID=-2 '
        IF @systemprocesses = 1
                SET @sql = @sql + ' OR (SPID>=0 AND SPID<=50)'
        IF @userprocesses = 1
                SET @sql = @sql + ' OR  SPID>=51 '

        SET @sql = @sql + ') '
        END
ELSE
        BEGIN
        SET @sql = @sql + 'AND '
        SET @sql = @sql + '( '
        SET @sql = @sql + '1=2 '
        SET @sql = @sql + ') '
        END


IF @process_status IS NOT NULL AND @process_status<>''
        SET @sql = @sql + 'AND RTRIM(status)=''' + @process_status + ''''

IF @debug = 1 AND EXISTS (SELECT membername FROM #srvrolemembers WHERE
memberSID = SUSER_SID())
        BEGIN
        PRINT @sql
        END

INSERT INTO #sp_who_results_filtered
EXEC (@sql)

DECLARE crs_processes CURSOR FAST_FORWARD
FOR
SELECT spid FROM #sp_who_results_filtered --blocked processes
UNION
SELECT blk FROM #sp_who_results_filtered  --blocking processes (allthough
they might belong to another login and hostname
ORDER BY spid


DECLARE @spid int
OPEN crs_processes

FETCH NEXT FROM crs_processes INTO @spid
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
                INSERT INTO #sp_lock_results
                EXECUTE sp_lock @spid
    END
    FETCH NEXT FROM crs_processes INTO @spid
END

CLOSE crs_processes
DEALLOCATE crs_processes




DECLARE databases CURSOR FAST_FORWARD
FOR
SELECT DISTINCT t2.dbID, t2.ObjID  FROM #hasDBaccess t1 INNER JOIN
#sp_lock_results t2 ON DB_ID(t1.dbname) = t2.dbid   --blocked processes
ORDER BY dbID


DECLARE @dbID int
DECLARE @ObjID int

OPEN databases

FETCH NEXT FROM databases INTO @dbID, @ObjID
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

                SET @sql = N'INSERT #objectnames SELECT ''' +
convert(varchar,@dbID) + ''',' + convert(varchar,@ObjID)  + ', [name] FROM '
+ DB_NAME(@dbID) + '.dbo.sysobjects WHERE [id] =@objectid'
                SET @param = N'@objectid int'

                IF @debug = 1 AND EXISTS (SELECT membername FROM
#srvrolemembers WHERE memberSID = SUSER_SID())
                        PRINT @sql

                EXECUTE sp_executesql @sql, @param, @objectid=@ObjID
    END
    FETCH NEXT FROM databases INTO @dbID, @ObjID
END

CLOSE databases
DEALLOCATE databases



--FOR DEBUGGING ONLY
IF @debug = 1 AND EXISTS (SELECT membername FROM #srvrolemembers WHERE
memberSID = SUSER_SID())
        BEGIN
        SELECT * FROM #sp_who_results
        SELECT * FROM #sp_who_results_filtered
        SELECT * FROM #sp_lock_results
        SELECT * FROM #objectnames
        SELECT * FROM #hasDBaccess
        SELECT * FROM #srvrolemembers
        END


SET @SQL = ''
SET @SQL = @SQL +
'SELECT
        t1.loginame,
       t1.spid,
        t1.dbname [SourceDB],
        t1.hostname,
        t1.blk         [blocked by SPID],
        t1.cmd         command,
        t1.status [process_status],
       DB_NAME(t2.dbid) [TargetDB],  --The DB from the SP_LOCK resulset!!!
        t2.type,
       t2.ObjID,
       t3.Objectname objname,
       t2.IndID ,
        t2.resource,
        t2.mode,
        t2.status [lock_status]
FROM        #sp_who_results_filtered  t1
LEFT JOIN   #sp_lock_results          t2
ON          t1.spid = t2.spid
LEFT JOIN   #objectnames      t3
ON          t2.dbid = t3.dbid
AND         t2.objid = t3.objectid
WHERE 1=1 '

IF @targetdb IS NOT NULL AND @targetdb <>''
        SET @SQL = @SQL + ' AND DB_NAME(t2.dbid)=''' + @targetdb + ''''

IF @lock_status IS NOT NULL AND @lock_status <>''
        SET @SQL = @SQL + ' AND t2.status=''' + @lock_status + ''''

SET @SQL = @SQL +
' ORDER BY
       loginame
        ,t1.spid
        , hostname
        , t1.blk
       , t1.dbname
       , t2.ObjID
       , t2.IndID'

IF @debug = 1 AND EXISTS (SELECT membername FROM #srvrolemembers WHERE
memberSID = SUSER_SID())
        PRINT @sql

EXEC (@sql)

-- return success
RETURN (0)


GO

But you know the defined interface to sp_who, and it would take all of 10
minutes to hard-code the #table creation.

Further, you could take the code from those objects and create your own
version, joining all of the results (and leaving out the superfluous
columns) in one simple stored procedure.  Even this, while not typically
recommended, is far safer and more sensible than this approach you seem to
love so much.

Obviously there is no way to convince you that you are going about this in a
terribly haphazard and disastrous way, so I bid you luck but will offer no
further advice to you.

I think you missed my point.  Not all stored procedures have exactly one
resultset!  Try running sp_help 'tablename' or create this:

CREATE PROCEDURE dbo.myproc
AS
BEGIN
    SELECT GETDATE(), 1, 'foo'
    SELECT 'blat', 34.6, GETUTCDATE()
END
GO

What you could do is create a template table that defines the structure the
result set of the stored procedure.  This would be a real table in the
database, so you could execute
    SELECT * INTO #tempTable FROM templateTableName WHERE 1=2
    INSERT #tempTable EXEC spName

This would keep the noise out of your stored procedures and would eliminate
the development overhead of modifying a bunch of other procedures when the
result set of a stored procedure changes.  All you have to do is maintain
the layout of the template table any time the stored procedure result set
layout changes.

Show quoteHide quote

For people who follow this thread, I consider Brian's suggestion worth
considering.
Of course the proposed workaound does not help to avoid writing CREATE
TABLEs for temp tables to store the resultsets of SPs, but it woud at least
avoid doing so more than once for any SP, given that the resultset of the SP
doesnt change.

Regarding Aaron's comments:
To start with, I know all articles of  Sommarskog inside out.
And what I am primarily trying to achieve in my SP is not to share any SP
result with any other SP (allthough of course, if I had such a SP, I could
just as well do exactly that too). What I would like to have is a less
cumborsome way of working with SP results. I would like to be able to store
them in temp tables, to join them together etc etc WITHOUT having to manually
define the temp tables.

I am myself MCSE, MCSD, MCSD.NET, MCT etc and have been earning my living
with Microsoft product & technologies for more than 13 yrs for which I am
very thankful to the bright heads in Redmons and elsewhere for all these
great products.
Nevertheless even an already great SW like SQL Server could get better.

Basically, the core of the discussion here is not about what I would like to
do with the SP I am working on but rather on the syntactical limiations
imposed by SQL Server which are preventing me from implementing what I have
on my mind and whether there are any workarounds I dont know of.

Currently the culminations of the following syntactical limitations are
preventing me to write the kinf of generic SP.

--not possible with SPs
SELECT *
INTO #tmp
FROM mySP

--possible with SPs, but the #tmp has to have been previously set up
manually using CREATE TABLE #tmp (.......)
INSERT #tmp
EXEC  mySP

--not possible with SPs (where as it is possible with Sybase)
DECLARE CURSOR mycur FOR mySP

So Aaron, I am glad that u took your time to respond at all, but unless u
have any ideas for a workaround, u could/should indeed use your time to
answer other postings.

erdöl

forgot to mention another limitation imposed by TSQL.

CREATE PROCEDURE mySP
AS
BEGIN
CREATE TABLE #tmp1
(col1 int)

EXEC('CREATE TABLE #tmp2 (col1 int)
         INSERT #tmp1 SELECT 999
         INSERT #tmp2 SELECT 999')

END

Whereas the dynamic SQL can access #tmp1 (allthough it cant access any local
variables defined in the containing SP),
the containing SP cannot access #tmp2 (or any local variables defined in
dynamic SQL)

There are 2 things which appear very weird and illogical to me:

1.) How comes the contained dynamic SQL can access the #tmp1 in the
containing SP, allthough it cannot access any local variables of the
containing code?

2.)  How comes the contained dynamic SQL can access the #tmp1 in the
containing SP, allthough the containing SP cannot access #tmp1 in the dynamic
SQL?

Such limitations certainly need to be lifted!

not to mention the limitation that UDFs (user defined functions) cannot 
contain dynamic SQL.

Otherwise many SPs could simply be re-written as UDFs.

So IMHO the mentioned 5 limitations alltogether make working with SPs
unnecessarily cumbersome.

Only in cases where you have these specific (and bizarre) requirements.
Most of us don't find SPs cumbersome at all, because we spend the time up
front to design the system correctly, giving up the cost of the overhead up
front so as tonot go crazy trying to maintain it over the long run.

I think you've misunderstood what UDFs are for. For very good reasons a
UDF is supposed to be deterministic. Hard to be sure of that with
dynamic code in the mix. Poor idea to execute dynamic code inline
anyway.

Why is dynamic SQL so important to you? Dynamic code is an evil that
you should resort to very, very sparingly in production systems.
Although dynamic code is often useful for DBA type tasks, the fact that
you view these things as limitations suggests you may have some
fundamental problems with your database architecture.

1.) Local temp tables are scoped to all nested processes. Variable
scope is local. If you want to pass variables, use sp_executesql
2.) You'd use a global temp for that.

These aren't limitations! As developer, you have control of the scope.
Scoping is essential for writing good modular code.

of course, life as a developer is much easier when developing a DB for a
single client who is going to use it for a purpose known to the developer at
the time of developing.

Unfortunately, I have to develop a DB which will be used by unlimted number
of clients which will all add their own extensions to it (new tables, new SPs
etc etc)

Still the core parts of the DB has to be able to cope with all these
extensions which are unknown to me at the time of developing.

So i have to code way more generic.

E.g. I have to write SPs (for monitoring or testing purposes) which can call
any SP to take sample of it's results for a predefined set of parameters, no
matter how the parameters look like, no matter how the resultsets look like,
no matter how many different resultsets the SPs might give back.

E.g. Using ODBC, OLE or ADO.NET, it is possible to handle SPs giving back
multiple resultsets. Why it is not possible to have this capability natively
in SQL Server?

Regarding David's answer:
There are dozends of programming languages which can contain statements to
dynamically construct and execute code (e.g. EVAL, never heard? :-)

So I dont c any reason for why cant SQL Server UDFs handle EXEC or
sp_executesql...


In future versions, I would definitively love to c the following feature:

SELECT * FROM SP

It the above was possible, then all the following would probably become
possible too:

SELECT * FROM SP [2]    --returns the 2nd resultset of the SP

SELECT * INTO #tmp FROM mySP

DECLARE CURSOR mycur FOR mySP

DECLARE CURSOR mycur FOR SELECT * FROM SP

DECLARE CURSOR mycur FOR SELECT * FROM SP [2]





Show quoteHide quote

Then TSQL is the wrong tool. Generic utlities are written in .NET, C++,
etc, etc. Much more scope for this kind of stuff. TSQL is not intended
as a general purpose language and it surely never will be. It is
primarily designed for data access code and (static) business logic .

Certainly, but those languages don't have data models with types and
relations. In an RDBMS the business rules belong in the model and in
the procedural access code - not in variables. If your schema is static
then you don't do this. Again, TSQL has no ambitions to be a general
purpose language.

I agree.

Having said that, pls let us not drift away from the core issus of this
thread which is, as the subject of my original posting says, that some other
DBMSs can make a SELECT on SP or open simply declare a cursor on a SP, which
are currently not possible in SQL Server and which, if they were possible,
allow me to finalize my generic SP.

So once again, I appreicate each and every contribution to this thread, but
please a less more philosphical and more technical towards finding a
workaround to the concrete issue I have at hand.


Show quoteHide quote

So the same response here applies to those who want to use LIMIT because
they don't feel like the extra overhead of a client-side paging solution, or
want an inherent SPLIT() function so that they don't have to create their
own function to simulate an array, or want different database diagrams, or
the ability to add/drop IDENTITY property, or the ability to insert a column
into the middle of a table without dropping/re-creating, and the list goes
on and on...

Use "some other DBMS" if you can't work around the limitations of SQL Server
in the ways that people have suggested.

Regarding LIMIT:
a paging aid is already incorporated into version 2005.

Regarding SPLIT:
Yes, indeed, the lack of ParamArrays is a pain in the ass

Regarding Add/Drop IDENTITY:
This is after all what SET IDENTITY_INSERT option is for

And what about the new RowNumber function or the ranking functions or the
enhanced TOP clause and many other functions which were previously missed by
the community and now have been added in version 2005.

Praising the existing functionality while playing down feautures which are
missing.
Aaron, is this your sole purpose of your involvement in this group?

Listen dear MVPs, I am not here to make SQL Server bad. I just have a
technical issue at hand, help if u can or otherwise dont bother, pls!

Yes, but I have already observed stubborn complaints that the syntax is too
cumbersome (read:people are lazy!).

T-SQL is not a programming language!  There are no arrays.  There are
several workarounds.  Not using one and then complaining that they should be
there is pure laziness and nothing more.  Deal with the limitations or use a
different product!

No, I think you misunderstood.  I meant the ability to add the IDENTITY
property to an existing column, or to DROP the property permanently (not for
a few minutes).  I hear these complaints all the time.

Yes, Microsoft has succumbed in several cases where personally I don't think
they should have (and even more that were real shortcomings).  Yes, in some
cases, the changes are welcome, and will make dealing with the database
easier.  But in several cases they further blur the lines between an RDBMS
and an application, the server and the client, and I think it is important
that new users especially () have a firm grasp on the differences instead of
proceeding down the "SQL Server is just another application" mindset.

Yes, absolutely.  Search groups.google.com, you will find that every single
post I have ever made is to shoot down cracker jack approaches to problems.
Seriously, do a search, and you will see that I often show people exactly
how to accomplish the very thing they shouldn't be trying to accomplish.
Yours is a very extreme case, and I do not agree with the kludges required,
so I will not encourage the path you have already decided to take.

We are trying to help, by showing you that your approach is flawed and is
not going to be successful in SQL Server.  Hence, the suggestions to use a
different approach (or a different database).  Sorry if we have different
interpretations of the term "help", so I suppose I will add you to my
twitfile to avoid the temptation of continuing to try to help you understand
why everything you are trying to do is wrong.

(I was also getting kind of tired of all these lazy abbreviations like u and
c and pls anyway, so it's probably for the better.  I guess you approach
written communication with the same laziness and thoughtlessness as you
approach database and application development.)

*plonk*

If someone asks me "How can I build a house out of straw?" the wisest
answer isn't going to be the most direct answer. I know which type of
answer I'd want to give. How about you?

Aaron, Itzik - about the MIN Function
IDENTITY_INSERT
Conditional select into on temp table
Inserting data to a table from other database. Help
ISNUMERIC