Create Table In Store Procedure and inserting data with the Kalen user.

As Mike said, don't create objects in tempdb. However, your stored
procedure is failing because the first thing you are doing is dropping
a table that doesn't exist.
What are you trying to accomplish?
StuThank stu and mike,
I' know: the tempdb is a system database but i use this database like
workspace.
I explain this again, for this
1) Create a user Kalen with the create table permission on tempdb database
2) Connect with this user and execute
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TabTest]
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
This code is simple and the more important work
3) Connect with a user sa and create this SP
Create procedure DBO.ProcTest
as
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to Kalen
4) Connect with the Kalen user this not work
I receive this message
--Invalid object name 'Tempdb..TabTest'
The question is:
I need create this one SP and not use SQL Dynamic.
Easy'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1148603889.848594.234520@.j73g2000cwa.googlegroups.com...
> As Mike said, don't create objects in tempdb. However, your stored
> procedure is failing because the first thing you are doing is dropping
> a table that doesn't exist.
> What are you trying to accomplish?
> Stu
>|||"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:%23s7zNOMgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Thank stu and mike,
> I' know: the tempdb is a system database but i use this database like
> workspace.
<snip> SQL Server uses it as a workspace as well. You're asking for trouble
here. Have fun.</snip>

> I need create this one SP and not use SQL Dynamic.
1) I HIGHLY recommend against forcing your own DDL inside TempDB. At best
you're creating contention within SQL Server for TempDB resources. It's a
System Database for a reason.
2) I don't see the point of what you're doing, but if you're building the
SP in a separate Database from the one you're executing it in (assuming
that's the reason you feel the need to prefix the table with Tempdb..
everywhere), then you need to look at the effect of not using that prefix in
your IF EXISTS statement. I added the database name prefix to your select *
from dbo.sysobjects and object_id statements and dropped the OBJECTPROPERTY
function in favor of checking the XTYPE column. Works great.

> Easy'
Too Easy.sql

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> > theprocedureexists...
> > AsCREATEPROCEDUREcan not be combined with any other Transact-SQL
> > statement in a single batch, I was wondering if there were any way to
> > achieve something like this :
> > (..)
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...