473,287 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Stored Procedures - Help

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

Jul 23 '05 #1
7 3962
you need to CREATE TABLE ;)

Jul 23 '05 #2
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 ;)


Jul 23 '05 #3
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 )
}

Jul 23 '05 #4
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

Jul 23 '05 #5
[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
Jul 23 '05 #6
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..!

Jul 23 '05 #7
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
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jarrod Morrison | last post by:
Hi all Im relatively new to using stored procedures and im not sure if it is possible to do what I am trying to do so any help here is greatly appreciated. I am using the variable @MachineName...
2
by: M Wells | last post by:
Hi All, I'm wondering if anyone can tell me if it's possible to search for stored procedures by their contents? I tend to leave very descriptive notes in stored procedures and they're...
7
by: Douglas Buchanan | last post by:
I can't seem to open SQLS2k Stored Procedures in the IDE I am running MDE 2003 Version 7.1.3088 I have a MSDN professional subscription and did a complete install of vs.net Help explains how...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
5
by: Jeff | last post by:
I have question about differences in fenced sql procedures and fenced stored procedures. Do fenced sql procedures take up an extra memory segment when executed? Reason I ask is we have several...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
0
by: vanesa | last post by:
Hi, In a client we must begin to program many stored procedures of DB2. The following doubts arise to me that I raise you to see if you can help me: 1. From a point of view of the programming:...
0
by: Ryan | last post by:
PLEASE HELP!! I am trying to do what I think should be the simplest thing. In Visual Studio 2005, I set up a datagrid and then went ot configure a datasource for it. I made my connection to...
11
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.