MS SQL Server :: Creating Multiple Stored Procedures At Once

Creating Multiple Stored Procedures In One Go.

Feb 25, 2008

I have 50-60 stored procedures in my application and these are stored in different files as per file one stored procedudre.

I want to deploy my applicaiton. I want to create a batch or Single SQL file to create all stored precodures in on go.

My objective is to ensure that No stored procedure has left while deploying the applicaiton.

thanks.

View 3 Replies View Related

Creating Multiple Stored Procedures At Once

May 1, 2008

Hey

I am creating a database application that is accessed through a .NET front end.
What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.

When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me the most bother is

��Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134
Incorrect syntax near the keyword 'procedure'.��

What does this mean and why can��t I run more than once Stored Procedure at once?

Many thanks

Quish

View 9 Replies View Related

Stored Procedures Are Not Creating

Dec 4, 2001

I want to create a replication on PUBS database between the servers "DENVER" and "VANCOUVER". I created a publication on "DENVER" using SALES table of PUBS database. In the properties of publication, I selected all the options of "Subscription creation and synchronization" under "Subscription options".

Then I created a pull subscription on "VANCOUVER" using PUBS database. As data of SALES table is same in both servers and also I want to use subscribers schema and data, I chose the option "No, the subscriber already has the schema and data" for initializing subscription. Now the problem is when I do updations in sales table, subscription is returning error "Could not find stored procedure 'sp_MSupd_sales'". I was found that subscription is not creating any of the stored procedure that is needed for subscribing from publication. Please help me to solve this problem.

Thanking You all,

Vishnu

View 1 Replies View Related

Creating Stored Procedures

Apr 23, 2008

Hi,All,I want to create a stored procedure,it should accept two input parameters one is General variable and other one is Table name.
How to pass table name as a input parameter.
I have created following one,but giving error.

CREATE PROC SP_VOTERSLIPS @BOOTHID VARCHAR(14),@TABLENAME VARCHAR(255)
AS
BEGIN
SELECT V.BOOTHID,B.BOOTHNAME,B.BOOTHADDR1,B.BOOTHADDR2,B.ENGBOOTHADDR1,B.ENGBOOTHADDR2,C.CONSSTUTIONNAME,V.SNO,V.HOUSENO,V.VOTERNAME,
V.GUARDIANTITLE,V.GUARDIANNAME,V.GENDER,V.AGE,V.VOTERIDNUMBER FROM @TABLENAME V LEFT JOIN TBLBOOTH B ON (B.BOOTHID=V.BOOTHID) LEFT JOIN
TBLCONSTTUTION C ON (C.CONSSTUTIONID=V.CONSSTUTIONID) WHERE B.BOOTHID=@BOOTHID
END

Error is :Must declare the variable '@TABLENAME'.

Pls Help me,it is urgent.

View 5 Replies View Related

Creating Stored Procedures In Sql Server

Jul 2, 2007

I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it. However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster". Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didntDim comm As New SqlCommandDim strsql As StringDim strconn As String strsql = "sp_Roster" strconn = "server=Home; user=sa; pwd=juwar74; database=Book;" With comm .Connection = New SqlConnection(strconn) .CommandText = strsql .CommandType = CommandType.StoredProcedure With .Parameters.Add("TeacherID", SqlDbType.Char) .Value = "DawsMark@aol.com" End With With .Parameters.Add("ClassID", SqlDbType.Int) .Value = CInt(classid) End With With .Parameters.Add("sID", SqlDbType.Int) .Value = ssID End With With .Parameters.Add("sLastName", SqlDbType.Char) .Value = lastname End With With .Parameters.Add("sFirstName", SqlDbType.Char) .Value = firstname End With With .Parameters.Add("sMiddleName", SqlDbType.Char) .Value = middlename End With With .Parameters.Add("Student", SqlDbType.Char) .Value = fullname End With With .Parameters.Add("Password", SqlDbType.Char) .Value = password End With .Connection.Open() .ExecuteNonQuery() With comm.Connection If .State = ConnectionState.Open Then .Close() End If End With End With Here is my procedure that I created and that was saved as sp_Roster.sql in sql server CREATE PROCEDURE sp_Roster ASBEGIN SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@TeacherID, @ClassID, @sID, @sLastName, @sFirstName, @sMiddleName, @Student, @Password)ENDGO Why isn't ado.net finding it in sql server. Is the sqlstr correct?

View 2 Replies View Related

Creating Charts Using Stored Procedures

Jul 21, 2006

Hi all,

I want to create charts(pie/bar) using stored procedures for the table record values in sql server 2005 express edition.

plz help me for the above topic

Thanks & Regards,
Srinivas

View 6 Replies View Related

Creating SQL Server Stored Procedures In Deployment

Sep 20, 2007

Hey guys,I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code. I would like to create the stored procedure inside that catch block. I just don't know how.The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

View 1 Replies View Related

Creating Extended Stored Procedures In C Sharp ?

Apr 26, 2004

Is it possible to create an extended stored procedure in C Sharp.
This is for Sql Server 2000.

Books online mentions that you have to use c / c++ to create an extended stored procedure.
However have Microsoft added any support so that the same thing can be done through a simpler language like C Sharp.

Thanks,
Alok.

View 1 Replies View Related

Creating Stored Procedures In SQL Server 2000

Dec 12, 2006

sivanantham writes "How to create Stored Procedures in SQL Server 2000?

also give me samples with Cursors and all

Thanks
Sivanantham"

View 4 Replies View Related

Creating Stored Procedures Based On Variables

Oct 5, 2006

Hi There,

I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?

Thanks

View 1 Replies View Related

Error When Creating Script To Copy Stored Procedures

Jan 16, 2007

Our DB has around 30 SProcs - I need to move them into a script, so that it can be easily added to another server. The way I'm creating the script is to highlight all the SProcs, then copy - in my notepad screen, I paste, which gives me one script, which includes all the individual creation scripts for the Sprocs.
However, I'm getting an error when I create the script -
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'SP_MySproc'. The stored procedure will still be created.
So - what's a good, and/or easy way to structure the script, so that I can easily find WHERE to put Which SProc Script, in the list?

View 4 Replies View Related

SSMS-EE: Creating And Using Stored Procedures - Error Messages 111, 156, && 102

May 10, 2006

Hi all,

I tried to use the SSMS-EE Query to execute the following code statements:

--- SQLQuery.sql---

USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO

//////////////////////////////////////////////////////////////////////////////////////////////////

I got the following error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang

View 1 Replies View Related

SSMS-EE: Creating And Using Stored Procedures - Error Messages 111, 156, && 102

May 11, 2006

Hi all,

I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:

--- SQLQuery.sql---

USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO

//////////////////////////////////////////////////////////////////////////////////////////////////

I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang

View 6 Replies View Related

SSMS-EE: Creating And Using Stored Procedures - Error Messages 111, 156, && 102

May 10, 2006

Hi all,

I tried to use the SSMS-EE Query to execute the following code statements:

--- SQLQuery.sql---

USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO

//////////////////////////////////////////////////////////////////////////////////////////////////

I got the following error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang

View 1 Replies View Related

SSMS-EE: Creating And Using Stored Procedures - Error Messages 111, 156, && 102

May 11, 2006

Hi all,

I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:

--- SQLQuery.sql---

USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO

//////////////////////////////////////////////////////////////////////////////////////////////////

I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang

View 3 Replies View Related

SSMS-EE: Creating And Using Stored Procedures - T-SQL Error Messages 111, 156, && 102

May 10, 2006

Hi all,

I tried to use the SQL Server Management Studio-Express Edition to execute the following code statements:

--- SQLQuery.sql---

USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO

//////////////////////////////////////////////////////////////////////////////////////////////////

I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang

View 3 Replies View Related

SSMS-EE: Creating And Using Stored Procedures - Error Messages 111, 156, && 102

May 10, 2006

Hi all,

I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:

--- SQLQuery.sql---

USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO

//////////////////////////////////////////////////////////////////////////////////////////////////

I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang

View 1 Replies View Related

MSSQL 7 Replication - Creating Of Standard Sp_MS... Stored Procedures

Aug 15, 2001

I am running MS SQL Server 7 with SP3 installed and am having some problems getting replication to work correctly. I want to replicate all tables from one database to another database on the same server. I can setup replication, add publications and add subscriptions without any problem and all goes well until the replication process starts when the job aborts because it cannot find the sp_MSins..., sp_MSupd... or the sp_MSdel... stored procedures. I have used the New Publication wizard to create a Transactional publication, published all tables and allowed the wizard to use the default stored procedures which it says will be created when the subscribers are initialized but although I have created push subcriptions the stored procedures are never created and hence the replication fails whenever the job runs.

I have SA access to the databases concerned and as far as I am aware the jobs are running as my login - can anybody help me out as this is beginning to drive me nuts.

Thanks

Chris

View 1 Replies View Related

Creating CSV Files Using BCP && Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@

Jan 24, 2008

Hi all,

I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:

--scBCPcLabResults.sql--

declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".

=========================================================================================
--scBCPcLabResults.sql--

declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".

===================================================================

I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.

Thanks in advance,
Scott Chang

View 3 Replies View Related

Multiple Stored Procedures And Transactions

Dec 7, 2007

I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?
In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.
To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.
To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.
I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.
I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.
Thank

View 4 Replies View Related

Stored Procedures Multiple EXECs

Apr 13, 2008

My stored procedure looks like this:
ALTER PROCEDURE dbo.Calculate
AS
BEGIN
SET NOCOUNT ON
declare @lastUpdate varchar(15)
declare @sql nvarchar(4000)
declare LastUpdate cursor for select updateID from currentUpdate
OPEN lastUpdate
FETCH NEXT FROM LastUpdate INTO @lastUpdate
SELECT @sql = 'INSERT INTO A ...WHERE ..' + @lastUpdate
EXEC(@sql)
SELECT @sql = 'INSERT INTO B ...WHERE...' + @lastUpdate
EXEC(@sql)
SELECT @sql = 'UPDATE ...WHERE...' + @lastUpdate
EXEC(@sql)
CLOSE LastUpdate
DEALLOCATE LastUpdate
END

When I run this stored procedure from my web page, only the first sql is executed and then I get a timeout message.
When I execute the stored procedure manually on the server, all the sqls are executed correctly.
How can I solve this problem?
Thank You.

View 1 Replies View Related

Editing Multiple Stored Procedures

Jan 5, 2004

Am i the only one who is forced to open multiple instances of SQL Server Enterprise Manager in order to edit more than one stored procedure at a time? A lot of times i'll be comparing two sprocs or writing one based off of a "template" i wrote earlier. But i have to open two instances and alt-tab between them. Is there an easier way? (aside from pasting it into a notepad and using that)

View 5 Replies View Related

Stored Procedures And Multiple Tables

Mar 2, 2006

Hi,
I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table.
i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates.
As I know nothing about stored procedures I'm not even sure how to go about this??
Regards.
Peter.

View 2 Replies View Related

How To Edit Multiple Stored Procedures ?

Apr 27, 2007

I have two questions:

1) Is it possible to rename a SQL table –or- copy the content of a table into a new table ?

2) How to replace the renamed or replaced table name inside the code of ALL Stored Procedures that references it ?

View 3 Replies View Related

Multiple Stored Procedures ,same Tables

Sep 3, 2007

Hi,

I am using multiple stored procedures which are using same set of tables at a time .
As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.
My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins
where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you
Raj Deep.A

View 4 Replies View Related

Using Multiple Stored Procedures In One Report

Aug 11, 2005

Hi all,

View 9 Replies View Related

Multiple Stored Procedures,same Set Of Tables

Sep 3, 2007

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

View 2 Replies View Related

Transaction Across Multiple Stored Procedures...Posssible?

May 12, 2008

Hello All,I can not figure out how to accomplish the task that I want. So, please help.I have two tables, Parent and Child tables. For both Tables I have stored procedures for inserting records. I simply pass parameters for field values to stored proecdures and Stored procedures simply insert record with those values. I am first inserting record in Parent table and then in child table via calling their stored procedures one by one.Now, what I want to do is, after inserting record in parent table successfully, If record can not be inserted in child table successfully then I want to rollback, I also want to undo the insertion operation done in previous parent table's stored procedure call. The problem is, I can not span a Transaction across multiple stored procedure. If I begin a Transaction in a stored procedure for a Parent table, then I have to either roll back or commit that transaction in the same stored procedure. How can I span a transaction across multiple stored procedures, so that I can rollback in the Child Table's stored procedure in such a way that the Record Insertion of the Parent table can be cancelled. What to do...? Please help, it's urgent. Thanks to all.

View 4 Replies View Related

Multiple Stored Procedures In A Single SqlScript, Is That Possible?

Jun 20, 2007

Hello.

I have my database and i generated the script that creates the entire database. My database has some stored procedures.

When i create the script without the stored procedures, everything works fine when i try to reinstall the database. But if i create the script with the stored procedures, i get the following error message when i try to reinstall the database:

Code:

System.Data.SqlClient.SqlException: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@time1".

as i understand, i must have 1 storedprocedure per script. I tried separating one stored procedure per script and everything works fine, the problem here is that i have many many stored procedures.
Now, is there a way to have multiple stored procedures in one single sqlscript?

Do i really need to separate my stored procedures in one script per stored procedure or is there any way to put them all in one single script?

THANKS FOR YOUR HELP!!

View 1 Replies View Related

Search & Replace In Multiple Stored Procedures

Aug 31, 2007

Hi all,

Does anyone know how to do the search and replace in multiple stored procedures using SQL 2005 Management Studio?
Any help will be appreciated.

Thanks!

View 3 Replies View Related

How To Return Multiple Values From Stored Procedures

Jun 22, 2007

How to return multiple values from stored procedures to reports in sql server 2005

View 5 Replies View Related

Multiple Stored Procedures,single Report

Sep 3, 2007

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

View 1 Replies View Related