DECLARE CURSOR ON EXEC... (Sybase does it, so why cant SQL Server?

[Ottoman]

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 \
::parm1, ::parm2
DBMS WITH CURSOR x EXECUTE USING title_id, copy_num

[Christian Donner]

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?

[Aaron Bertrand [SQL Server MVP]]

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?

[Ottoman]

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...

[Aaron Bertrand [SQL Server MVP]]

> 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

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?

[Brian Selzer]

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.

"Ottoman" <Ott...@discussions.microsoft.com> wrote in message
news:84A286FE-5FDA-48CA...@microsoft.com...

[Ottoman]

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

[David Portas]

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.

--
David Portas
SQL Server MVP
--

[David Portas]

Correction:

> Writing code that dynamically creates objects at design time is not
> the way to build a reliable database

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?)

[Aaron Bertrand [SQL Server MVP]]

> 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.

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

[Aaron Bertrand [SQL Server MVP]]

> 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?

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

> the "overhead" in development would surely be more than offset by the
> time wasted on *support* for a system like this.

Absolutely!

> 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.

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.

[Brian Selzer]

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.

"Ottoman" <Ott...@discussions.microsoft.com> wrote in message

news:10B573AB-CF7C-4B64...@microsoft.com...

[Ottoman]

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

[Aaron Bertrand [SQL Server MVP]]

> 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

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.

[Ottoman]

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

[Ottoman]

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!

[Ottoman]

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.

[David Portas]

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.

> Such limitations certainly need to be lifted!

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

[Aaron Bertrand [SQL Server MVP]]

> 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.

[David Portas]

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

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.

[Ottoman]

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]

[David Portas]

> 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)

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 .

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

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.

[Ottoman]

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.

[David Portas]

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 Bertrand [SQL Server MVP]]

> 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 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.

[Ottoman]

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!

[Aaron Bertrand [SQL Server MVP]]

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

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

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

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!

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

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.

> 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.

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.

> Aaron, is this your sole purpose of your involvement in this group?

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.

> 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!

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*

[ML]

> How on earth are you going to manage such a beast?

I'd bet the number of columns is 666.

ML