Cretae a view in one DB with a stored procedure from another DB

Hi,

Ik created an application with visuals basic.NET. This has a
connection string to one database, let's say 'A'. In this database a
stored procedure is called which should execute a string (which is
passed by the) VB tool. This string is a CREATE VIEW statement en this
should be executed in another database let's say 'B'.

I tried this in Transact - SQL

EXEC('USE B;' + Query)

An error occurs : CREATE VIEW should be the first in a batched
statement.

Could anyone help me with this one?

Greetz,
Hennie"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0405240403.724292f@.posting.google.co m...
> Hi,
> Ik created an application with visuals basic.NET. This has a
> connection string to one database, let's say 'A'. In this database a
> stored procedure is called which should execute a string (which is
> passed by the) VB tool. This string is a CREATE VIEW statement en this
> should be executed in another database let's say 'B'.
> I tried this in Transact - SQL
> EXEC('USE B;' + Query)
> An error occurs : CREATE VIEW should be the first in a batched
> statement.
> Could anyone help me with this one?
> Greetz,
> Hennie

You need a GO right before the CREATE VIEW statement, and this won't work in
dynamic SQL. In any case, this isn't really a good way to create a database
object - it would probably be a lot easier for your client application to
connect directly to database B. Or if you really want to do it from SQL,
then why not create the stored procedure in database B and call it from
database A?

It might help if you can explain what you're trying to achieve, and someone
may be able to suggest a better solution, as creating objects dynamically
from a user application can create significant problems with security and
maintenance. But since you didn't say what your goal is, you may have a good
reason for doing this.

Simon

Creation of two tables in a stored procedure

Hi,

I was wondering if there was a way to create two temp tables within the same stored procedure.

Can we have two create statements one after the other?

Thanks

Crazy:

I have done so many, many times.

Dave

|||

what exactly error you are getting?

i too did so many number of tmes.

Gurpreet S. Gill

Creating View by calling Stored Proc

Hello all,
Is it possible to create a view by running the Stored Procedure in SQL Server 2000?
Thanks in advance,
VenugopalCreate Procedure CreateView
as

declare @.sql varchar(8000)

set @.sql =
'Create View MyView
as
select * from MyTable'

drop view MyView

exec(@.sql)

grant select on MyView to public

go|||Thanks for the reply. But what i was looking for what
Create View MyView As Exec SP1

Now i am able to do the same with OPenRowset, but the problem with that, it does allow the view to be schema bound without that i can't provide index to it.

Originally posted by mdhealy
Create Procedure CreateView
as

declare @.sql varchar(8000)

set @.sql =
'Create View MyView
as
select * from MyTable'

drop view MyView

exec(@.sql)

grant select on MyView to public

go|||Can't you just call the SP from the client?

Openrowset will create a new connection and can cause unexpected problems.|||Originally posted by nigelrivett
Can't you just call the SP from the client?

Openrowset will create a new connection and can cause unexpected problems.

No because requirement is that i have to write the query in access.
Currently there is a link table from Access to SQL Server. So if i create a view then there is no need for duplication and storing of data. Thats the reason i want to create the view with a SP.

Thanks

creating variables in a Stored procedure

I would like to know if I can create new variables using existing variables in a stored procedure.

To be clear, in the SP I use, I pass table name. But I need the results from 2 other tables as well.

The tables are named 1996, 1997, 1998.......2007.
If I pass 2005 to SP, I need results from 2005, 2004 & 2003. How do I assign or get 2 new table names (variables) for 2004 & 2003 ?

Part of code:
ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

when I pass 2005 to SP, I need
@.TblName1 = 2005
@.TblName2 = 2004
@.TblName3 = 2003

How can assign 2004 & 2003 to variables TblName2 & Tblname3 ?

I really appreciate any help.

look up either the SELECT statement or the SET statement in books online. But you seem to have an inconsistency in the way you are using the data. Your code has:

Code Snippet

ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

when I pass 2005 to SP, I need
@.TblName1 = 2005
@.TblName2 = 2004
@.TblName3 = 2003

But you seem to be subtracting 1 and 2 from parameter passed to the proedure. But if that is what you want, you need something like:

Code Snippet

ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

as

declare @.TblName2 varchar(20)

declare @.TblName3 varchar(20)

set @.TblName2 = convert(integer, @.TblName1) - 1

set @.TblName3 = convert(integer, @.TblName1) - 2

But a big problem now is that if you pass a string in for you table name such as 'aTable'

you will get execution errors.

You can avoid this by use of IF statements such as:

Code Snippet

IF isNumeric(@.TblName1) = 1

select @.TblName2 = convert(integer, @.TblName1) - 1,

@.TblName3 = convert(integer, @.TblName2)

Now if that isn't enough, there are times (actually, many times) in which the "IsNumeric" function will not correctly assess whether or not a string is numeric. There is an article that talks about these particular problems here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Look for the "IsReallyNumeric" funtion at this site. That should be enough to keep you going for at least a little while.

Kent

|||

Miamik,

This has all the markings of a very unwieldy design. I would not consider having identical tables named as this seems to indicate JUST as a method to partition data.

You would be better served to explore table partitioning in Books Online. Then your code would be so-o-o-o much easier, and you wouldn't have to resort to using dynamic SQL (with all of its' issues and security problems.)

|||I think Arnie is definitely telling it like it is.|||Thanks for all your replies.

creating user procedures in the master database

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

Thanks...

Moving thread to the DB Engine forum.|||

It is not a best practice to create user stored procs in master. This SP was undocumented and not subject to the 3 release deprecation policy. I think it went away with the move to the resource database in SQL2K5.

With that, what is your specific scenario for creating stored procs in master?

Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:
Categories
---
| CATID | NAME | ParentId |
---
1 Electronics 0
2 Computers 1
3 Cameras 1
4 Sony Cameras 3
5 Clothing 0
6 White TShirt 5
I would like the output to like the following
Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt
How can i get this to work via a stored procedure?Something like this would work...
Drop Table #temp
Create Table #temp
(
CatID int,
[Name] varchar(20),
ParentID int
)
insert into #temp values (1, 'Electronics', 0)
insert into #temp values (2, 'Computers', 1)
insert into #temp values (3, 'Cameras', 1)
insert into #temp values (4, 'Sony Cameras', 3)
insert into #temp values (5, 'Clothing', 0)
insert into #temp values (6, 'White TShirt', 5)
Drop Table #temp2
Create Table #temp2
(
CatID int,
[Name] varchar(255),
ParentID int
)
Insert Into #temp2
Select CatID, [Name], ParentID
>From #temp
While @.@.Rowcount > 0
Update t
Set [Name] = t1.[Name] + ' -> ' + t.[Name],
ParentID = t1.ParentID
From #temp2 t Inner Join #temp t1
On t.ParentID = t1.CatID
Select * From #temp2
HTH
Jason|||Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.
ML|||Loops!!' Procedural code' !! God! How non-relational!
Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

Creating Unions in TSQL

Hi everyone. I was wondering if I could get some pointers in creating a union between two tables. This is the sproc I currently have:

CREATE Procedure spGetReturnCheckForCriteria
 @.SearchCriteria VARCHAR(8000),
 @.SortOrder VARCHAR(8000),
 @.PageSize INT
AS
-- Declare vars
DECLARE @.SQLStatement NVARCHAR(4000)
DECLARE @.bldSQLStatement VARCHAR(8000)
DECLARE @.retValue INT
-- Initialize vars
SET @.SQLStatement = ''
SET @.bldSQLStatement = ''
SET @.retValue = -1
-- Sanity Check(s)
IF (@.PageSize IS NULL OR @.PageSize < 1)
 -- Paging Size can not be Null, nor less than One
 BEGIN
 SET @.RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)
 RETURN
 END
-- Build the Paging SQL Statement
SET @.bldSQLStatement = 'SELECT TOP '
-- Add the Page Size
SET @.bldSQLStatement = @.bldSQLStatement + CAST(@.PageSize AS VARCHAR)
-- Add Columns/Tables/Relationships
SET @.bldSQLStatement = @.bldSQLStatement + '
 ReturnCheck.ReturnCheckID AS ReturnCheckID,
 ReturnCheck.FiscalNumber AS FiscalNumber,
 ReturnCheck.ReturnedDate AS ReturnedDate,
 ReturnCheck.CheckNumber AS CheckNumber,
 ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
 ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
 ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
 ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
 TaxPayment.PaymentID AS PaymentID,
 TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
 TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
 TaxPayment.PaymentAmount AS PaymentAmount,
 TaxPayment.PaymentQuarter AS PaymentQuarter,
 TaxPayment.PaymentYear AS PaymentYear,
 TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
 TaxPayment.PaymentTypeCode AS PaymentTypeCode,
 TaxPayment.PaymentOriginCode AS PaymentOriginCode,
 TaxPayment.VoucherNumber AS VoucherNumber,
 TaxPayment.ReversedIndicator AS ReversedIndicator,
 TaxPayment.PaymentDate AS PaymentDate,
 CAST(NULL AS DATETIME) AS CCReferralDate,
 DistributionPoint.UIDPrime AS UIDPrime,
 DistributionPoint.UIDCheck AS UIDCheck,
 DistributionPoint.UIDDistPoint AS UIDDistPoint,
 CASE
 WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate
 ELSE ReturnCheck.UpdatedDate
 END AS ReturnCheckTimeStamp
FROM TaxPayment
 INNER JOIN DistributionPoint
 ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)
 INNER JOIN ReturnCheck
 ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)
'
-- Add Search Criteria
If (@.SearchCriteria IS NOT NULL)
 SET @.bldSQLStatement = @.bldSQLStatement + ' WHERE ' + @.SearchCriteria
-- Add Sort Order
IF (@.SortOrder IS NOT NULL)
 SET @.bldSQLStatement = @.bldSQLStatement + ' ' + @.SortOrder
-- Set the SQLStatement
SET @.SQLStatement = @.bldSQLStatement
-- Execute the Paging Query
EXECUTE @.retValue = sp_executeSQL @.SQLStatement
GO

Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.In order for a UNION to make sense, the tables should have equivalent columns. That does not appear to be the case here. What are you trying to do?|||Thanks for the reply Douglas. The reason I want to do a union is because sometimes there won't be any records in the table ReturnCheck with the same ID as a record in TaxPayment. Because of this issue, I won't get any records returned to me.

Initially, the sproc had a LEFT OUTER JOIN between TaxPayment and ReturnCheck, but that was causing problems with other methods, so it was changed to an INNER JOIN. Now those problems are fixed, but it's causing my problem to occur.

I thought maybe a UNION would solve the problem, but the SQL is being built dynamically so it would be an extreme pain to implement.|||The LEFT OUTER JOIN is the solution to what you are trying to do. You do not indicate what the problems with "other methods" were, but if they were related to handling NULLs, then I would work around those problems. NULLs are your friend, they mean data is missing.

creating trigger on stored procedure

How Can i create trigger on stored procedure?
I have stored procedure (named "TEST_PROCEDURE"), and would like to run trig
ger after this procedure is executed.Triggers are for tables, not stored procedures. Put the logic in the stored
procedure... That's what it's there for
"dsbs" <anonymous@.discussions.microsoft.com> wrote in message
news:D11ACFC6-885B-4F7C-98E0-DD7545865482@.microsoft.com...
> How Can i create trigger on stored procedure?
> I have stored procedure (named "TEST_PROCEDURE"), and would like to run
trigger after this procedure is executed.

creating trigger on indexed view

Hi,
Is it possible to create trigger on an indexed view? I tried and it keep
give me this error:
Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
Invalid object name 'dbo.VIEW_MYVIEW'.
I also attempted to use Enterprise Manager tool to create the trigger on the
indexed view but got the same error there.
thanks!
Are you trying to create an AFTER trigger (the default). Only INSTEAD OF
triggers may be created on views.
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:unGrKuXkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to create trigger on an indexed view? I tried and it keep
> give me this error:
> Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
> Invalid object name 'dbo.VIEW_MYVIEW'.
> I also attempted to use Enterprise Manager tool to create the trigger on
> the
> indexed view but got the same error there.
> thanks!
>

creating trigger on indexed view

Hi,
Is it possible to create trigger on an indexed view? I tried and it keep
give me this error:
Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
Invalid object name 'dbo.VIEW_MYVIEW'.
I also attempted to use Enterprise Manager tool to create the trigger on the
indexed view but got the same error there.
thanks!Are you trying to create an AFTER trigger (the default). Only INSTEAD OF
triggers may be created on views.
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:unGrKuXkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to create trigger on an indexed view? I tried and it keep
> give me this error:
> Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
> Invalid object name 'dbo.VIEW_MYVIEW'.
> I also attempted to use Enterprise Manager tool to create the trigger on
> the
> indexed view but got the same error there.
> thanks!
>

creating trigger on indexed view

Hi,
Is it possible to create trigger on an indexed view? I tried and it keep
give me this error:
Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
Invalid object name 'dbo.VIEW_MYVIEW'.
I also attempted to use Enterprise Manager tool to create the trigger on the
indexed view but got the same error there.
thanks!Are you trying to create an AFTER trigger (the default). Only INSTEAD OF
triggers may be created on views.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:unGrKuXkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to create trigger on an indexed view? I tried and it keep
> give me this error:
> Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
> Invalid object name 'dbo.VIEW_MYVIEW'.
> I also attempted to use Enterprise Manager tool to create the trigger on
> the
> indexed view but got the same error there.
> thanks!
>sql

creating trigger on indexed view

Hi,
Is it possible to create trigger on an indexed view? I tried and it keep
give me this error:
Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
Invalid object name 'dbo.VIEW_MYVIEW'.
I also attempted to use Enterprise Manager tool to create the trigger on the
indexed view but got the same error there.
thanks!Are you trying to create an AFTER trigger (the default). Only INSTEAD OF
triggers may be created on views.
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:unGrKuXkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to create trigger on an indexed view? I tried and it keep
> give me this error:
> Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1
> Invalid object name 'dbo.VIEW_MYVIEW'.
> I also attempted to use Enterprise Manager tool to create the trigger on
> the
> indexed view but got the same error there.
> thanks!
>

Creating Text File from Stored Procedure

Greetings,

I have a sp that dumps text into a textfile but I am having trouble
creating the textfile.

EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'

The directory "The File" has a space in it. I've tried putting the
carat ^ before the space, and putting double quotes...but I keep
getting this error

'\\servername\d$\The' is not recognized as an internal or external
command, operable program or batch file.

If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
File\sub\filename.dat"' I get the same thing.

If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
File\sub\filename.dat""' I get

'"\\servername\d$\The File\sub\filename.dat"'
is not recognized as an internal or external command, operable program
or batch file.

Does anybody see what I am doing wrong?At a quick glance, it looks like filename.dat is simply not an
executable file - what exactly are you trying to do?

Simon|||Hi

Enquote the filename with double quotes should have worked, therefore the
file may not exist or could be still open. If "\\servername\d$\The
File\sub\filename.dat" from a command prompt says the file does not exist
then it probably doesn't or permissions are wrong.

If this is a batch file you may want to use a more appropriate extension.

John

"phantom" <phantomtoe@.yahoo.com> wrote in message
news:1126224325.956647.140030@.g43g2000cwa.googlegr oups.com...
> Greetings,
> I have a sp that dumps text into a textfile but I am having trouble
> creating the textfile.
> EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'
> The directory "The File" has a space in it. I've tried putting the
> carat ^ before the space, and putting double quotes...but I keep
> getting this error
> '\\servername\d$\The' is not recognized as an internal or external
> command, operable program or batch file.
> If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
> File\sub\filename.dat"' I get the same thing.
> If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
> File\sub\filename.dat""' I get
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.
> Does anybody see what I am doing wrong?|||phantom (phantomtoe@.yahoo.com) writes:
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.

Windows executes files depending on the suffixes. .dat is normally not
tied to any application. Thus, Windows does not know how to run the file.

If the file is a simple command file, the extension should be .BAT.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating Temporary within Stored Procedure

I'm creating a temporary table within my stored procedure should I add a
drop temporary table statement at the end of my stored procedure so that if
stored procedure fails the temporary table will be dropped?
Thanks
No need to; SQL Server will automatically drop the temporary table when the
procedure returns, regardless of whether it was successful or not.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AB48F052-B137-4F70-A124-2AFF800FB984@.microsoft.com...
> I'm creating a temporary table within my stored procedure should I add a
> drop temporary table statement at the end of my stored procedure so that
if
> stored procedure fails the temporary table will be dropped?
> Thanks

Creating Temporary within Stored Procedure

I'm creating a temporary table within my stored procedure should I add a
drop temporary table statement at the end of my stored procedure so that if
stored procedure fails the temporary table will be dropped?
ThanksNo need to; SQL Server will automatically drop the temporary table when the
procedure returns, regardless of whether it was successful or not.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AB48F052-B137-4F70-A124-2AFF800FB984@.microsoft.com...
> I'm creating a temporary table within my stored procedure should I add a
> drop temporary table statement at the end of my stored procedure so that
if
> stored procedure fails the temporary table will be dropped?
> Thanks

Creating Temporary within Stored Procedure

I'm creating a temporary table within my stored procedure should I add a
drop temporary table statement at the end of my stored procedure so that if
stored procedure fails the temporary table will be dropped?
ThanksNo need to; SQL Server will automatically drop the temporary table when the
procedure returns, regardless of whether it was successful or not.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AB48F052-B137-4F70-A124-2AFF800FB984@.microsoft.com...
> I'm creating a temporary table within my stored procedure should I add a
> drop temporary table statement at the end of my stored procedure so that
if
> stored procedure fails the temporary table will be dropped?
> Thankssql

Creating Temporary Table From a View

Hi,
I would like to create a temporary table is a stored procedure which
contains all the same set of columns as one of my view in the
database. Is there anyway I can do this? I would also like to update
the temp table in the stored procedure itself.
Something like as follows
CREATE TABLE #tmpTable --I dont want to specify the list of columns
SELECT * INTO #tmpTable FROM vwTest
UPDATE #tmpTable SET ......
Can anyone please help me with this?
Thank you
EricGos,
How about SELECT * INTO #mytemptable from viewname
Note that using * in production code is generally not considered good =practice, as things can change oif columns added or removed.
Mike John
Gos wrote:
> Hi,
> > I would like to create a temporary table is a stored procedure which
> contains all the same set of columns as one of my view in the
> database. Is there anyway I can do this? I would also like to update
> the temp table in the stored procedure itself.
> > Something like as follows
> > CREATE TABLE #tmpTable --I dont want to specify the list of columns
> > SELECT * INTO #tmpTable FROM vwTest
> > UPDATE #tmpTable SET ......
> > > Can anyone please help me with this?
> > Thank you
> Eric|||Hello,
Have you tried this:
-- Start
CREATE PROC usp_Test
AS
SELECT * INTO #tmpTable FROM vwCustomers
UPDATE #tmpTable SET CustomerID = 'BERGA' WHERE CustomerID = 'BERGS'
SELECT * FROM #tmpTable
GO
EXEC usp_Test
SELECT * FROM vwCustomers
--End
vwCustomers is a view in the Northwind Sample Database that I created.
You can find the SQL for the vwCustomers here:
http://www.ilopia.com/MSSQL/Tutorials/Views.aspx#PartitionedViews
Hope this helps!
--
Regards,
Kristofer Gafvert
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
"Gos" <ericmoyer2345@.yahoo.com> wrote in message
news:a3cf3db2.0309281229.15c22814@.posting.google.com...
> Hi,
> I would like to create a temporary table is a stored procedure which
> contains all the same set of columns as one of my view in the
> database. Is there anyway I can do this? I would also like to update
> the temp table in the stored procedure itself.
> Something like as follows
> CREATE TABLE #tmpTable --I dont want to specify the list of columns
> SELECT * INTO #tmpTable FROM vwTest
> UPDATE #tmpTable SET ......
>
> Can anyone please help me with this?
> Thank you
> Eric|||Thanks a lot..
It works fine. I have a view which contains only the columns that are
required to be displayed on the User Interface. So, I thought that I
can use that view (SELECT * ) instead of giving a long list of
columns. Isn't it a good programming practice?
Gos

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?
There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>
|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>
|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:
> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
> > I am doing a code review of a stored procedure.
> > What the procedure does is to create a temp table and then based on parameters sent to
> > the procedure, call different stored procedures which inserts into the temp table just
> > created.
> > Are their any issues with procedures creating temp table on the fly. Any scalability
> > issues?
> >
> >|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.sql

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on paramete
rs sent to
the procedure, call different stored procedures which inserts into the temp
table just
created.
Are their any issues with procedures creating temp table on the fly. Any sca
lability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parame
ters sent to
> the procedure, call different stored procedures which inserts into the tem
p table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any s
calability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
>|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.