Procedure Creation

Nov 27, 2005

I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record

this is the cust table created
create table CUSTOMER (
CUSTOMER_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256));

I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...

any clues?

View 4 Replies


ADVERTISEMENT

Procedure Creation

Nov 27, 2005

i was asked to post this here..

_----------------------------------------


procedure creation
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record

this is the cust table created
create table CUSTOMER (
CUSTOMER_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256));

I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...

any clues?
i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.

create table CUSTOMER
(
CUSTOMER_ID int identity(1000,1),
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256)
)

But while writing insert statement didnt specify this column.

Like

Insert into CUSTOMER (NAME ,ADDRESS ,CITY ,STATE ,ZIP_CODE ,AREA_CODE ,PHONE_NUMBER,SALESPERSON_ID , CREDIT_LIMIT ,COMMENTS )
values('xoxo','planet','earth','jupiter','1234','2 13',31231,231,1231,'1231')


not working.....!!?!?!

View 2 Replies View Related

Stored Procedure Creation

Mar 5, 2007

I have created a database and I a table. Now I want to create a stored procedure.
I go to Programability=>Stored Procedures in my database and press right click=>New Stored Procedure. After I write the procedure I don't understand how to save it inside the datebase and execute it to preview the results in the Management Studio Express.

View 4 Replies View Related

View Creation Using Stored Procedure

Apr 21, 2001

Hi

I need to create a view using a stored procedure .

The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.

I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .


Sujit

View 1 Replies View Related

Stored Procedure Creation Date

Jun 14, 2001

Is there any system stored procedure or any table that contains the creation date of
stored procedures in one database . I really want to know wich is the last stored procedure
created in a database.
Thanks.

View 1 Replies View Related

Creation Of Two Tables In A Stored Procedure

Oct 17, 2006

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


View 2 Replies View Related

Trigger Procedure Execution On Database Creation.

Feb 2, 2006

Hello,

Is there a way to trigger the execution of a procedure when a database is created ? We're using this 3rd party system in which you only specify the database server. Whenever the users create a new "Storage Area", the system actually creates a new database.

I need to handle backups for that system so I want to create a procedure that will generate a backup device and backup jobs automatically whenever a database is created.

I will also need 2 procedures to handle database renaming and deletion. I searched around but can't find a way to trigger the execution of my procedures.

View 4 Replies View Related

Stored Procedure Creation,Invalid Object Name

May 25, 2008



I am trying to create a new stored procedure in Sql server managment studio express in a database.
I am getting an error message saying

Invalid object name 'Consumer_delete'.

Can you please tell why I am getting this error message?? Also , I need to make sure that the created procedure appears in the list of database objects after execution.
Thanks for your help



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE Consumer_delete

@ConsumerID int,

@BusinessId int

AS

BEGIN

DECLARE @intError int

DECLARE @ConsBusinessID int

SET NOCOUNT ON

SELECT @ConsBusinessID = CONSUMERBUSINESS.[ID]

FROM CONSUMERBUSINESS

WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId

DELETE FROM CONSUMERBUSINESS

WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId



DELETE FROM CUSTOMERREMINDER

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM NOTE

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM VISIT

WHERE ConsumerBusinessID = @ConsBusinessID



--ERROR HANDLING--------

SET @intError = @@ERROR

IF @intError <> 0

GOTO ExitError

RETURN 0

ExitError:

RETURN @intError

END

View 3 Replies View Related

Need A Help For Executing Script File Having Creation Of Stored Procedure From C#

Jan 9, 2008

 Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex()  method reads the script file from starting to end and returns script text.  My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime,  @prmEndDate datetime,@prmEvent char(1)ASBEGIN    SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth             WHEN 'D' THEN DateOfDeath             WHEN 'M' THEN DateOfMarriage         END ) as EventDate    from dbo.Parishioner P    left outer join dbo.Sex S on P.SexId = S.SexId    where (CASE @prmEvent WHEN 'B' THEN DateOfBirth             WHEN 'D' THEN DateOfDeath             WHEN 'M' THEN DateOfMarriage         END )    between @prmStartDate and @prmEndDate   END  But when i executing script file  having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm 

View 1 Replies View Related

Quick Question About Checking For Existing Procedure Before Creation.

Feb 27, 2008

Hey everyone,

I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.



Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END

GO
It's generating a syntax error at PROCEDURE.

Anyone know why this might happen?

The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.




Thanks in advance for any suggestions!

View 5 Replies View Related

Recovery :: Creation Of Store Procedure In AlwaysOn Database Infrastructure

Oct 2, 2015

I would ask if there some precaution on creating stored procedure on a database member of an always on group. I copied the stored procedure on all nodes but I'm not sure if this is enough to make it works in case of failover ?

View 5 Replies View Related

SQL 2012 :: Trace Creation Time Is Different From Time Check Creation

Oct 8, 2014

We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.

for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".

View 0 Replies View Related

SQL Job Creation

Oct 27, 2004

I know this is not SQL forums but still if some body has solutions..then plz help
My backend is SQL.
My Ms Access database is always open and getting updated continously.

I need to create Job for SQL. This job will run every 10-15 min may be.
This job should pick latest data from MS Access and put in my SQL server database
Is it possible to create such kind of Job>

OR

Can my application read this MSAccess database when its open by some other application and getting updated continously?----I tried this in vain!!!!

Regards,
Mani

View 2 Replies View Related

Creation Of An SQL Job

Oct 1, 2002

I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..

IS there a way? I need help.
Thanks

View 3 Replies View Related

Job Creation

Aug 12, 2003

Hello to all!
I have a procedure "rebuild_index" and I would like to create a job running that procedure.
May someone send me a script (template) how to create this job, the most important:
When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script)
Thank you very much for your help!


create proc p_rebuild_index as
declare @name varchar(100),
@string varchar(200)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch c1 into @name
while @@fetch_status = 0
begin
set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)'
execute (@string)
--print @string
fetch c1 into @name
end
close c1
deallocate c1

GO

View 5 Replies View Related

SDF Creation...

May 22, 2007

How can i create sdf files?
Is there any way to convert a mdb or a xml file to sdf?
The only way i was able to do this was with data port wizard from primeworks, but its not freeware...
Can i populate my sdf database from xml files by writing code...??
Does someone knows??
I have searched everywhere and didnt find anything...
I'm working in VS2005 vb.net and i have installed compact sql...
Thank you!

View 1 Replies View Related

Dabase Creation

Mar 16, 2007

Is it possible to create a database structure in MS-Access and somehow import it into Visual Web Developer 2005 Express or SQL Server Management Studio 2005 Express?

View 1 Replies View Related

Database Creation

Jan 11, 2008

Hi,
I am not sure is this a proper place for my question.
All we know that creating a database can be done as the following.
1) Create a project named DBExample
2) Click Add->New Item.
3) Select SQL Databse and type a name.(Ex:  Database1.mdf) A database is added to the project
4) Then add table and input values.
5) Set the primary key....
My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?
 
Thanks

View 4 Replies View Related

SQL Server Creation

Feb 13, 2005

Hi, What should I type in as the server name if I would want to create a local database, I tried 'local' and 'localhost' but it didn't work.

View 1 Replies View Related

Database Creation

Aug 16, 2005

Hello...
I want to develop a web site having two features
1. Online Shopping2. Forums
Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once.
____________Thanks in advNauman Ahmed

View 1 Replies View Related

DTS Scheduling And Job Creation

Dec 13, 2000

I'm sorry to beat this to death but something odd is happening that I'm not quite sure I understand.

I have a number of DTS packages that when originally created with the wizard were scheduled and the jobs have run fine. Today, I created an import job that runs great but then crashes when I try to schedule it from the wizard. When I manually go in and try to schedule the DTS job, it accepts the input but does not create the job.

Since I've done this before, I'm confused about why I was able to do this previously, but not now. Permissions, etc. have not changed at all and I am the dbo for the database. The DTS package and attempted job scheduling are being done on Windows 2000 Server. SQL-Server version is 7.0.

Anybody have any ideas?

View 2 Replies View Related

Views Creation

Nov 19, 2003

can sum one help with this:

I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables

Thanks a lot.

View 1 Replies View Related

Script Creation

Sep 24, 2007

Hi

I am supposed to give scripts for an installtion at client site.

installing SQL server 2005 Developer edition
some databases already we have
tables
indexes
Stored procedures
jobs


Can u please guide me how to do this?

I am new to this.

Muralidaran r

View 4 Replies View Related

Creation Of Jobs...

Jan 20, 2004

I wanted to created a few jobs in my database.
How do i create it using the Batch files?????


Thanks,
Sandu_Bangalore

View 1 Replies View Related

DTS Creation Issue

Mar 17, 2004

Hi all,

Here is the problem : I can not create a DTS package in SQLServer.
=> Error description : access denied
Environment :
- Windows NT4 sp6 Server, french, logon on as "Administrator".
- SQLServer 7.0 sp4, french, logon as "sa" on the NT Server with Enterprise Manager.
- SQLServer Agent runs under an Administrator account

Why can't i create à single DTS package ? Strange, isn't it ?
Many thanks for your ideas

Bertrand

View 3 Replies View Related

T-sql File Creation

May 30, 2004

Hi all,

Can I create one xml file through transact-sql ( in a stored procedure )?
From where i will get the tutorials ? Please help me ...

I am using the MSSQL Server 8.0 .

thanks
Pinto

View 1 Replies View Related

User Creation

Apr 10, 2008

We normally map a single SQL user to all application users . From performance standpoint and from best practices perspective , what is the preferred method of mapping OS users to SQL users viz one to one OR many to one .

View 6 Replies View Related

Help With ROLE Creation

Jun 16, 2008

I am a promotional DBA and need help with some code a developer wrote that errors out.
He is trying to create a role and to my knowledge there is nothing wrong with the syntax but it gives an incorrect syntax error.

CREATE ROLE 'PPS' IDENTIFIED BY 'stressboy';

error: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ROLE'.

we tried it with ' ' & "" and without either. I'm sure this is an easy one for you all. Thanks.

View 1 Replies View Related

How To Automate DB Creation

Jan 28, 2014

I want to create a form (Infopath) or web page where the user can request for a new database I want the user to fill in the required info (like the size, name columns.....) and ones they're done filling the required info, they will click on a button and that should trigger the database automation script to run and if the database with the requested name is not created, the script should create a database for the user.

View 1 Replies View Related

Table Creation

Dec 20, 2005

Hi,

First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?

Thanks,

Al

View 5 Replies View Related

Dsn Creation Problem

Feb 28, 2006

hi every body,
i have a problem in creating dsn ....i have sql server installed in
a system name RESEARCH.....when i wish to create a dsn to that server
from a system RESEARCH1 i'm unable to create it.......has anyone encountered such an error...plz help
thanks in advance

venkat

Life is Short Make it Sweet

View 1 Replies View Related

Package Creation

May 15, 2006

dipak writes "can i create package (like oracle) in SQL server 2000? if yes how it possible plz help me."

View 1 Replies View Related

Databse Creation

Aug 2, 2007

How to create a database in MS SQLserver 2005 express edition.
Pleae help me to get started.I have learned some SQL commands.
But i want to execute those in SQLserver 2005.But i don't know the procedure for creating a database.please help me on this.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved