Stored Procedures - Help

[pkruti]

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?
CREATE PROCEDURE sp_YesNo AS
IF EXISTS (SELECT * FROM yesno_holding )
DROP 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 ConstructionSurvey where sche=1)
set @scheno = (select count(*) from ConstructionSurvey where sche=2)
set @schemb =(select count (*) from ConstructionSurvey where sche =3)
set @scheother = (select count(*) from ConstructionSurvey where not
(sche in (1,2,3)))
set @howyes = (select count(*) from ConstructionSurvey where howwarr=1)
set @howno = (select count(*) from ConstructionSurvey where howwarr=2)
set @howmb =(select count (*) from ConstructionSurvey where howwarr =3)
set @howother = (select count(*) from ConstructionSurvey where not
(howwarr in (1,2,3)))
set @paaexplyes = (select count(*) from ConstructionSurvey where
paaexpl=1)
set @paaexplno = (select count(*) from ConstructionSurvey where
paaexpl=2)
set @paaexplmb =(select count (*) from ConstructionSurvey where paaexpl
=3)
set @paaexplother = (select count(*) from ConstructionSurvey where not
(paaexpl in (1,2,3)))
GO

[getinked]

you need to CREATE TABLE ;)

[pkruti]

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 ConstructionSurvey where sche=1)
set @scheno = (select count(*) from ConstructionSurvey where sche=2)
set @schemb =(select count (*) from ConstructionSurvey where sche =3)
set @scheother = (select count(*) from ConstructionSurvey where not
(sche in (1,2,3)))

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

set @paaexplyes = (select count(*) from ConstructionSurvey where
paaexpl=1)
set @paaexplno = (select count(*) from ConstructionSurvey where
paaexpl=2)
set @paaexplmb =(select count (*) from ConstructionSurvey where paaexpl
=3)
set @paaexplother = (select count(*) from ConstructionSurvey where not
(paaexpl in (1,2,3)))
getinked wrote:

you need to CREATE TABLE ;)

[getinked]

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > } [ ,...n ]
)

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

< column_definition > ::= column_name data_type
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ 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_expression )
}

< table_constraint > ::= [ 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_conditions )
}

[getinked]

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]

[posted and mailed, please reply in news]

(pk****@hotmail.com) writes:

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?
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.
CREATE PROCEDURE sp_YesNo AS

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, es****@sommarskog.se

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

[SQLDBA]

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]

SQLDBA (pg*****@gmail.com) writes:

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

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, es****@sommarskog.se

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