Use a sp to Make a view with a sp inside 
Author Message
 Use a sp to Make a view with a sp inside

I have a sp that uses some variables to return a record set.  Works
fine.  But I can only seem to call it from Query Analyzer.  I want to
make another sp that creates a view from the first sp.  Here is what I
have so far.

CREATE PROCEDURE sp_Make_Fees AS

EXEC("
CREATE VIEW dbo.Fees
AS
EXEC

The part below the second EXEC works fine in Analyzer, but not when I
put it together like this.  Any help would be appreciated.  Thanks in
advance.



Sun, 20 Feb 2005 21:00:22 GMT
 Use a sp to Make a view with a sp inside

Some thoughts....

* you can create a view from a proc if the proc uses xp_cmdshell to call an
OSQL session and you pass the create view string into the OSQL session....

* I'm confused? You're creating a proc to create a view that calls a proc?
I'm not sure I understand all the issues there...

* Are you using SQL 2000? You may find that a table valued user defined
function makes some of your tasks easier....

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist


Quote:
> I have a sp that uses some variables to return a record set.  Works
> fine.  But I can only seem to call it from Query Analyzer.  I want to
> make another sp that creates a view from the first sp.  Here is what I
> have so far.

> CREATE PROCEDURE sp_Make_Fees AS

> EXEC("
> CREATE VIEW dbo.Fees
> AS
> EXEC
> sp_CrossTab





> The part below the second EXEC works fine in Analyzer, but not when I
> put it together like this.  Any help would be appreciated.  Thanks in
> advance.



Sun, 20 Feb 2005 21:51:11 GMT
 Use a sp to Make a view with a sp inside
Boyd,

1. Never, ever name your stored procedures sp_*. It's bad for performance
and can get you in trouble with conflicting procedure names.
2. You can't issue a CREATE VIEW statement from inside a procedure. Sorry,
but you can't.
3. You can't create a view from a EXEC like that. Although I haven't tried
it, you might get away with doing something like this:

First add your server to itself as a linked server, then do this:

CREATE VIEW myView AS
SELECT *
FROM OpenQuery(LinkedLocalServer, '
    EXEC
    sp_CrossTab





Judging from your parameters, you might want to read up on dynamic SQL in
Sommarskog's page:
http://www.algonet.se/~sommar/dynamic_sql.html

HTH,

--

Andrs Taylor


http://www.sql.nu/


Quote:
> I have a sp that uses some variables to return a record set.  Works
> fine.  But I can only seem to call it from Query Analyzer.  I want to
> make another sp that creates a view from the first sp.  Here is what I
> have so far.

> CREATE PROCEDURE sp_Make_Fees AS

> EXEC("
> CREATE VIEW dbo.Fees
> AS
> EXEC
> sp_CrossTab





> The part below the second EXEC works fine in Analyzer, but not when I
> put it together like this.  Any help would be appreciated.  Thanks in
> advance.



Sun, 20 Feb 2005 21:26:23 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Using a SP inside another SP

2. How can I call a SP inside a SP without the owner name

3. Calling SP from inside another SP

4. sp inside sp

5. calling SP inside SP

6. How Get Output Param from Inside SP, calling another SP

7. Check if parameter supplied to SP inside SP

8. Modifying a sp in SQL 7 makes the sp becomes unexecutable

9. Executing a SP inside of a VIEW

10. Create view inside SP

11. SP:ExecContextHit,SP:CacheMiss,SP:CacheInsert

12. Have anyone made Extended SP DLL using Delphi ?


 
Powered by phpBB® Forum Software