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


ADVERTISEMENT

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

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

Inserting Into Tables Using Stored Procedure

Dec 7, 2007

I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
 
 

View 4 Replies View Related

How To Search In 2 Tables By Using SQL Stored Procedure??

Mar 7, 2008

Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you

View 4 Replies View Related

Stored Procedure Checking 2 Tables

May 12, 2004

ok, ill lay it out like this, and try not to be too confusing.

I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,


i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item


so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.

well i tried not to be confusing, but i think i failed

View 1 Replies View Related

Using Temporary Tables Within The Stored Procedure

Nov 15, 2006

Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu

View 1 Replies View Related

Temp Tables In Stored Procedure.

Oct 22, 2007

Hi

I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.

I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.

Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.

Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.


Thanks

View 13 Replies View Related

Joining Two Tables In Stored Procedure

Apr 28, 2008

Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.


INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma

211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746

1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35

1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55

Table2
(NEW DATA)
SYMBOL LAST TRADE






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20

OUTPUT DATA:
(OLD DATA) (NEW DATA)










as
SYMBOL CATEGORY SHARES Prev.Close Last Trade






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma

211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746

1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55

1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20




View 1 Replies View Related

Creation Date For Tables

Feb 17, 2004

Hi,

This is my first time posting in this forum. I am very green in DB2 admin so I would really appreciate your help.


The company had a data warehouse on DB2 7.1 on a sun solaries 8

I want to know how to retreive creation date from any table


Thanks

View 2 Replies View Related

Inserting Simultaneous Tables Using Stored Procedure

Feb 8, 2007

I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReasonâ€? and “@WIPâ€?.â€? I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.

I made up the two tables just for testing, they are:
tbltest


ECID – int (PK)

View 2 Replies View Related

Linking Tables In Data Set Or Stored Procedure

Mar 10, 2008

Im currently in the process of developing a new system in ASP.net. The system uses multiple tables from a SQL database. To link information from say the products table to the suppliers table a unique key from the suppliers table is stored in the products table to show which products each supplier has bought...simple so far.

From this I can then pull out any of the information relating to that product instead of just displaying the ID. Currently this is done by creating a stored procedure and then dragging the stored procedure onto the dataset layer to create the data adapter.

Im not 100% this is the best way to be doing this. Is it possible to simply just link the tables in the dataset layer to display a string from another table instead of an ID referencing number?!

any help on this would be much appreciated as I cant really continue untill I have sorted out the data structure problems.Thanks in advancedmowfo 

View 2 Replies View Related

Insert Stored Procedure For Related Tables

Feb 24, 2005

I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem

I need to copy Quote - QuoteDetail records to Order - OrderItem tables

I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @@Identity.

I need to insert the QuoteDetail records into the OrderItem table using the new OrderID

Thank you for your help.

View 4 Replies View Related

Querying Temporary Tables From A Stored Procedure

Jun 7, 2005

What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.

I am currently using SQL Server 2000 backend.  Each one of these queries are
pretty complex so I created each query as a Stored Procedure.  These queries are
dynamic by each user, so the results will never be the same globally.

What I have done so far was created a master stored procedure passing the
current user's username as a parameter.  Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory.  Each of the sub stored procedures all
have the same columns.  After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable.  I then bind the DataTable to a
Repeater.DataSource.

Problem: When the page is rendered, it returns nothing.  I
tested the master SP in the data environment and it works fine.

Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource?  Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?

Here is an example of the code from the master SP:


CREATE PROCEDURE dbo.TM_getAlerts      @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO

It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled.  I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly.  But the query in one of the
sub SP is a normal but complex select.

View 5 Replies View Related

How To Get Data From All Related Tables Using Stored Procedure

Sep 16, 2005

I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders  and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.

View 1 Replies View Related

Bind To Multiple Tables From Stored Procedure

Dec 4, 2005

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

View 2 Replies View Related

Stored Procedure Referencing Moved Tables

Mar 12, 2001

Hi everyone

I am totally confused, please help me. I am new to this

I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are

1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.

2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)

Any reply is appreciated

View 2 Replies View Related

Stored Procedure To List Values From Different Tables

Jan 19, 2006

I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6

View 8 Replies View Related

Is There A System Stored Procedure That Scripts Tables?

Apr 25, 2007

Hello

I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.

Thanks a lot.

View 4 Replies View Related

Stored Procedure Insert - Multiple Tables

Jan 26, 2012

How to insert data into 2 tables in a stored proc.

Scenario is:

Table 1 insert is generated and the primary key is created. This key is an identity column and is the only thing that makes the row unique.

Table 2 needs an insert but one of the columns that is needed is the newly created column 1 primary key.

How do I know what the new rows primary key value is from Table 1

View 3 Replies View Related

SQL 2012 :: Using Stored Procedure To Truncate Tables?

Jun 30, 2014

I was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.

Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'

Of course there are different names, just changed for an example.

Here is the code in the Stored Procedure.

CREATE procedure [dbo].[uspTruncateTable]
@nDatabase varchar(255),
@nOwner varchar(255),
@ntable varchar(255)
as
declare @sqlString nvarchar(max)
set @sqlString = 'Truncate Table ' + @nDatabase + '.' + @nOwner + '.' + @ntable
exec (@sqlString)

This just seems like an unneeded step, just code the 'truncate table dbname.dbo.tblname'.

View 5 Replies View Related

Update Values In Two Tables Via Stored Procedure

Apr 19, 2014

I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes

Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated

Here is the SQL code :

ALTER procedure [dbo].[UpdateEmp]
@EmpName nvarchar(100),
@Nationality nvarchar(30),
@Passport nvarchar(20),
@ContractDate date,
@HealthDate date

[Code] ......

View 4 Replies View Related

Biztalk 2004, One Stored Procedure For 2 Tables

Dec 15, 2006

Hi,

I have an orchestration that recieves a pricelist flat file and it must insert into 2 tables - article, articlePrice(for each LotSize) into sql server using a sql adapter. I also want it to check if a record exists for that lotSize and update it if it does. The records are indentified with a uniqueidentifier. So I have 3 if statements:
1 - if (<the article does not exist(checks SupplierArticleNumber)>)
<insert a record into article table>
<insert a record into articlePrice table>

2 - else if(<the article exists but not for that lotSize>)
<insert a new record in articlePrice table>)

3 - else
update the corresponding ArticlePrice row.


My problem is in writing the sp, first it checks if the article exists, if it does then it creates a row in the article table and then one into the articlePrice table. My question is how do I get a hold on the "articleGUID"(pk - uniqueidentifier) that was created on the first table on insertion in order to create the row in the second table(fk)?

And is this possible with one stored procedure? If not does anyone know in Biztalk what other way to do this?

I am new to both Biztalk and sql, so I don't really know what I am doing? I would really appreciate any suggestions!

pp

View 2 Replies View Related

Stored Procedure To Create A New Database With Tables

Feb 28, 2007

I want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.

View 6 Replies View Related

Call Stored Procedure And Populate Sql Ce Tables

Apr 19, 2007

I'm coding in a black hole that has a moving target in it.



Ok, I now need to call an stored procedure and with the results from the stored procedure I need to populate my tables on my handheld.

Any ideas on how I can do this? I was using inline sql "select col1, col2, from table1" and doing my rda pull from that, but now I need to call stored procedures to do this and one sp takes a parameter. How can I accomplish this on handheld ?

View 1 Replies View Related

Creation Triggres On System Tables

Jul 23, 2001

Hi evebodody
How to get arround and create Update trigger on system table(syscomments)
I am getting permission Error while trying to create it even I logged in as "SA" ?

I wanted to trace SESSION_USER and HOST_NAME on any changes
and save it to "Log Table"

View 7 Replies View Related

How To Insert Data Into Two Tables Simulatneously, Using Stored Procedure?

Jul 4, 2007

 
Hi all,I have heard that we must insert into two tables simultaneously when there is a ONE-TO-ONE relationship.
Can anyone tell me how insert into two tables at the same time, using SP?
Thanks
Tomy

View 1 Replies View Related

Stored Procedure For Deleting Multiple Tables/rows

Jul 24, 2007

Hi,
I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID].
I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03.
I have come up with something like that but it does not seems to be correct.
CREATE PROCEDURE [sp_delete_test01_1] (@id [int])
AS
DELETE [test01] DELETE [test02] DELETE [test03]
WHERE  ( [id] = @id)GO
Your advise please. Many Thanks.

View 4 Replies View Related

Effect Of A SELECT Stored Procedure On Tables Or Database

Jul 25, 2007

Hi all,
I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.
For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed? 

View 4 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related







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