Stored Procedures - Help

**getinked** · Jul 23 '05, 07:36 AM

Re: Stored Procedures - Help

you need to CREATE TABLE ;)

**pkruti@hotmail.com** · Jul 23 '05, 07:36 AM

Re: Stored Procedures - Help

i tried that and it gives me an error 156: Incorrect syntax near the
key word 'declare'

what am i doing wrong?
CREATE PROCEDURE sp_YesNo AS

IF EXISTS (SELECT * FROM yesno_holding )
DROP TABLE yesno_holding;

Create table yesno_holding

declare @scheyes int
declare @scheno int
declare @schemb int
declare @scheother int

declare @howyes int
declare @howno int
declare @howmb int
declare @howother int

declare @paaexplyes int
declare @paaexplno int
declare @paaexplmb int
declare @paaexplother int

set @scheyes = (select count(*) from ConstructionSur vey where sche=1)
set @scheno = (select count(*) from ConstructionSur vey where sche=2)
set @schemb =(select count (*) from ConstructionSur vey where sche =3)
set @scheother = (select count(*) from ConstructionSur vey where not
(sche in (1,2,3)))

set @howyes = (select count(*) from ConstructionSur vey where howwarr=1)
set @howno = (select count(*) from ConstructionSur vey where howwarr=2)
set @howmb =(select count (*) from ConstructionSur vey where howwarr =3)
set @howother = (select count(*) from ConstructionSur vey where not
(howwarr in (1,2,3)))

set @paaexplyes = (select count(*) from ConstructionSur vey where
paaexpl=1)
set @paaexplno = (select count(*) from ConstructionSur vey where
paaexpl=2)
set @paaexplmb =(select count (*) from ConstructionSur vey where paaexpl
=3)
set @paaexplother = (select count(*) from ConstructionSur vey where not
(paaexpl in (1,2,3)))

getinked wrote:[color=blue]
> you need to CREATE TABLE ;)[/color]

**getinked** · Jul 23 '05, 07:36 AM

Re: Stored Procedures - Help

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definiti on >
| column_name AS computed_column _expression
| < table_constrain t > } [ ,...n ]
)

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definiti on > ::= column_name data_type
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expres sion ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constrai nt > ] [ ...n ]

< column_constrai nt > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_express ion )
}

< table_constrain t > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditio ns )
}

**getinked** · Jul 23 '05, 07:36 AM

Re: Stored Procedures - Help

CREATE TABLE TST_DATA (
RATE_PFX char (2) NOT NULL ,
RATE_ID char (7) NOT NULL ,
ZIP_AREA char (3) NOT NULL ,
EFF_DT datetime NOT NULL ,
TERM_DT datetime NOT NULL ,
RATE money NOT NULL
)
GO

**Erland Sommarskog** · Jul 23 '05, 07:36 AM

Re: Stored Procedures - Help

[posted and mailed, please reply in news]

(pkruti@hotmail .com) writes:[color=blue]
> Below is a stored procedure i am working with and i am trying to drop
> the yesno_holding table if it exists but how do i add it back? Meaning
> i want it to drop if it exists but i want to add it back if it doesnt
> exist. the reason why i have to drop it is because when i run the
> stored procedure it creates mulitple rows with the values in them but
> in my report it takes the top row and insert those values into the
> report and i dont want that i want the new values at the bottom row to
> be inserted so i need to know how to add the table back once i drop it.
> Dont know the syntax that should go after the DROP TABLE yesno_holding;
> line. Any ideas?[/color]

You should normally not drop or create permanent tables from your stored
procedure. Since this is for a report, I assume that this procedure is
to be run by a plain non-priviledged user, that does not have rights
to create and drop tables. (For SELECT, INSERT, DELETE and UPDATE, the
user gets the access rights of the procedure owner through ownership
chaining, but ownership chaining does not apply to other statements
such CREATE/DROP TABLE.)

It would be a lot easier to say "DELETE yesno_holding" to empty the
table.

However, you should maybe think twice before you take this route. What
if two users run the report at the same time?

There might be an even simpler solution: use a temporary table:

CREATE TABLE #yesno_holding (...)

The temporary table will be dropped when the procedure exists. And to
create temp tables, users do not need any extra privileges.
[color=blue]
> CREATE PROCEDURE sp_YesNo AS[/color]

Finally: don't call your stored procedures sp_something. The sp_
prefix is reserved for system stored procedures, and SQL Server first
looks for these in the master database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**SQLDBA** · Jul 23 '05, 07:37 AM

Re: Stored Procedures - Help

I agree with Erland, but instead of Temp tables use variable tables....
(@Tablename instead of #Tablename). Same as temp tables but variable
table will be released to the memory as soon as the transaction is
complete..... Not sure of the amount of data you are going to put in so
this is more effiecent..!

**Erland Sommarskog** · Jul 23 '05, 07:37 AM

Re: Stored Procedures - Help

SQLDBA (pg.242w@gmail. com) writes:[color=blue]
> I agree with Erland, but instead of Temp tables use variable tables....
> (@Tablename instead of #Tablename). Same as temp tables but variable
> table will be released to the memory as soon as the transaction is
> complete..... Not sure of the amount of data you are going to put in so
> this is more effiecent..![/color]

Table variable are unrelated to transactions. In fact, if your
transaction is rolled back, your table variable is not affected.

Table variables exists for a certain scope only, and is not visible
for other scopes, just like plain variables.

Whether to use table variables to temp tables is by far not a given
questions. Table variables do not have statistics, which means that
they cannot cause recomilation because of changed statistics (or any
other reason for that matter). This can be good, since recompiles can
be expensive. It can also be bad, because that recompilation is
necessary to get a good query plan for the rest of the procedure.

My recommendation is to start with temp tables, but if you get
performance problems that are due to recompilation, try a table
variable insttead. In triggers, though, I always use table variables
to save inserted/deleted in though.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

Stored Procedures - Help

Stored Procedures - Help

Comment

Comment

Comment

Comment

Comment

Comment

Comment