How Can Control Transactions For Creating Stored Procedure ?

Apr 30, 2007

I create StringBuilder type for
concating string to create a lot of stored procedure at once
 
However When I use this command
 
BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////
           -------------------- This any command

--///////////////////////////////////////////////////////////
    --COMMIT TRAN
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
 
 
on any command 
If I use
Create a lot of Tables
such as

 
BEGIN TRANSACTION

BEGIN TRY

--////////////////////// SQL COMMAND /////////////////////////
CREATE TABLE [dbo].[Table1](
     Column1 Int ,
     Column2 varchar(50) NULL
 ) ON [PRIMARY]

CREATE TABLE [dbo].[Table2](

     Column1 Int ,

     Column2 varchar(50) NULL

 ) ON [PRIMARY]


CREATE TABLE [dbo].[Table3](


     Column1 Int ,


     Column2 varchar(50) NULL


 ) ON [PRIMARY]


--///////////////////////////////////////////////////////////

    --COMMIT TRAN

END TRY



BEGIN CATCH

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH



IF @@TRANCOUNT > 0

    COMMIT TRANSACTION;
 
 
It correctly works.
 
But if I need create a lot of Stored procedure
 
as the following code :
 


 
BEGIN TRANSACTION

BEGIN TRY

--////////////////////// SQL COMMAND /////////////////////////

CREATE PROCEDURE [dbo].[DeleteItem1]

    @ProcId        Int,

    @RowVersion        Int

AS

BEGIN

    DELETE FROM [dbo].[ItemProcurement]

    WHERE

        [ProcId]    =    @ProcId    AND

        [RowVersion]    =    @RowVersion   

END
 
CREATE PROCEDURE [dbo].[DeleteItem2]


    @ProcId        Int


AS


BEGIN


    DELETE FROM [dbo].[ItemProcurement]


    WHERE


        [ProcId]    =    @ProcId 


END



CREATE PROCEDURE [dbo].[DeleteItem3]



    @ProcId        Int



AS



BEGIN



    DELETE FROM [dbo].[ItemProcurement]



    WHERE



        [ProcId]    =    @ProcId 



END



--///////////////////////////////////////////////////////////

    --COMMIT TRAN

END TRY



BEGIN CATCH

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH



IF @@TRANCOUNT > 0

    COMMIT TRANSACTION;

It occurs Error ?????
 
 
Please help me
How should I solve them ?
 
 


 

View 1 Replies


ADVERTISEMENT

Stored Procedure And Transactions

May 8, 2007

Good afternoon,
I was checking how Microsoft was doing something on stored procedures and I've noticed something interesting (or not) in their AdventureWorks sample database. I've noticed that their stored procedures are encapsulated in transactions such as this:BEGIN TRY
BEGIN TRANSACTION; -- <SOME SQL HERE>
END TRY
BEGIN CATCH
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END

EXECUTE [dbo].[uspLogError];
END CATCH;
 For the more I can understand what they are doing here I do have a question regarding the way SQL Server executes this procedure and the eventual overhead of it. I'm saying this because, as far as I know, when we execute a stored procedure it runs as a whole transaction itself. Not being very proficent with transactions, I find myself with the question: Isn't this a double transaction? If so, isn't this causing unecessary overhead on the database server, consequently in the database itself? Or is this pretty much the only way to do error handling without a roundtrip to the caller (e.g. Data Access Layer)
I'm trying to learn more about transactions and error handling using SQL Server so every help is valuable.
Best regards,DBA

View 2 Replies View Related

Transactions Within A Stored Procedure

Feb 7, 2006

Ive never used transactions before but i would like to start using them within stored procedures.

I have some questions about them and how roll backs function.

i have had a go a writing a basic procedure with a transaction

** updated {with Code wrap }


Code:

CREATE PROCEDURE TransferMoney
(
@Account1 Int,
@Account2 Int,
@myValue Numeric(18),
@myValue2 Numeric(18)
)
AS


-- STEP 1: Start the transaction

BEGIN TRANSACTION

-- STEP 2 & 3: Issue the Insert statement, checking @@ERROR after each statement

Insert Into Bank (Balance) Values (@MyValue)
WHERE AccountID = @Account1

-- Rollback the transaction if there were any errors

IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
Return
END


Insert Into Bank (Balance) Values (@MyValue2)
WHERE AccountID = @Account2

-- Rollback the transaction if there were any errors

IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
Return
END

-- STEP 4: If we reach this point, the commands completed successfully
-- Commit the transaction....

COMMIT




now have i used the
IF @@ERROR <> 0 correctly?


i want this transaction to only happen if the if both statement are true, stopping data insertion Errors when transfering from one account and adding to another.

any thoughts you have will be fantastic

View 7 Replies View Related

Stored Procedure Version Control

Sep 13, 2001

Is there anyone out there who has successfully setup version control of stored procedures with Source Safe and Visual Interdev?

View 1 Replies View Related

Stored Procedure Source Control

Jul 20, 2005

Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl

View 1 Replies View Related

Control Output Of Stored Procedure

Feb 19, 2008

I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.


CREATE PROC proc_Emp

@Sel_Country
AS
BEGIN


SELECT DISTINCT employee, hrid
FROM emp_table
WHERE country = @Sel_Country


@SEND_RESULTS =
CASE WHEN row_count = 0 THEN

SELECT 'No Country', 4444444444
ELSE

(send original results)
END


END

I need to:



Query for the employee list

If the row count is zero/null then return fixed values

Else return the orginal data from Step 1.
I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.

Rob

View 7 Replies View Related

Control The Output Of Stored Procedure

Nov 14, 2007



Hi all,
I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row.
The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.

Is there any way i could do this?


Exmaple:
In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!

Procedure dbo.AddressInsert(....)
as
BEGIN

Insert INTO Adress ....

SELECT * FROM AdressView WHERE AddressID = @@identity
END

Procedure dbo.CustomerInsert(...)
as
begin
-- transcation begin...

EXEC dbo.AddressInsert

INSERT INTO Customer....

-- end transcation

SELECT * FROM Customer WHERE CustomerID = @@identity

end

View 4 Replies View Related

Assigning A Stored Procedure Return Value To A Control

Jan 11, 2008

Hi, I have an app where I am calling a stored procedure that runs and returns an integer.  I would like to assign this integer to a control on the page.  Here is a sample of code and below I will give the errors: Protected Sub NewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewButton.Click        Dim tempconnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString)        Dim tempproc As New Data.SqlClient.SqlCommand("SP_FetchProviders", tempconnection)        Dim tempparam As New Data.SqlClient.SqlParameter        tempproc.CommandType = Data.CommandType.StoredProcedure        tempproc.Parameters.Add(New SqlParameter("@Agency_ID", Data.SqlDbType.Int))        tempproc.Parameters("@Agency_ID").Value = "8" 'Using a number just to test, will be populated by a session variable later        tempparam = tempproc.CreateParameter()        tempparam.ParameterName = "@OutValue"        tempparam.Direction = Data.ParameterDirection.ReturnValue        tempparam.SqlDbType = Data.SqlDbType.Int        tempconnection.Open()        tempproc.ExecuteNonQuery()        Dim labelfill As String        labelfill = tempproc.Parameters("@OutValue").Value        tempconnection.Close()        CType(Me.DetailsView1.FindControl("OmbudINSDDL"), DropDownList).Items.Add(labelfill) 'This is inside of a detailsview templatefield    End Sub Here is the stored procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER  procedure [dbo].[SP_FetchProviders]    @Agency_ID     intas    Select Name, ProviderSubUnit_ID    From ProviderSubunit a join Ombudsman b on a.contractproviders_id=b.contractproviders_id    Where b.agency_id = @Agency_ID            Order by Name  This takes the @Agency_ID as input variable, and has returns an integer (according to SQL management studio).  When I try to run my code I get the error that an sqlparameter with parametername @OutValue is not contained by this sqlparametercollection.  This may be a simple question, but does the outvalue have to be declared in the stored procedure?  If so, can you provide the right syntax?  If not, can you offer a suggestion to populate the dropdownlist with the returned value?  Thanks in advance. 

View 3 Replies View Related

ListBox Web Server Control And Stored Procedure

May 15, 2008

Hi, I've a  ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine)        {            ConnectionState currState = connection.State;            if (((connection.State & ConnectionState.Open) != ConnectionState.Open))                connection.Open();            try            {                SqlParameter[] parameters = new SqlParameter[3];                parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo);                parameters[1] = new SqlParameter("@DataInizio", DataInizio);                parameters[2] = new SqlParameter("@DataFine", DataFine);                SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters);                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                return ds;            }            finally            {                if ((currState == ConnectionState.Closed))                    connection.Close();            }        }   How Can I manage a list of values ??Thanks for help me!! 

View 1 Replies View Related

Passing Control Flags To Stored Procedure

Jul 23, 2005

Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure

View 4 Replies View Related

Analysis :: Possible To Use Stored Procedure To Control SSAS Partitions?

Aug 19, 2015

I have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.

Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):

OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.

View 2 Replies View Related

Problem Use Sqldatasource To Access Stored Procedure And Get Data Bind To Label Control

Aug 30, 2007

Hi every experts
I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table.
My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web?
Thanks anyone, who can give me any advice.
Satoshi

View 2 Replies View Related

Can I Use A SqlDataSource Control Exclusively To Pass Data To A Stored Procedure For Execution (insert/update Only)?

Feb 28, 2008

Hi,
I'm reasonably new to ASP.NET 2.0
I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure.  I also want the stored procedure to return an output value.  I do not need to perform a select or a delete.
For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control.  I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures).
I desperately need the syntax to:
a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need).
b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters).
c) syntax on how to set the values for these parameters (again...coming from form controls and session variables)
d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource.
If anybody has sample code or a link or two, I would be most appreciative.
Thank you in advance for any help!

View 5 Replies View Related

Creating A Stored Procedure

Oct 13, 2006

Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?Thank you 

View 3 Replies View Related

Help For Creating Stored Procedure

Apr 3, 2007

ALTER PROCEDURE companyinsert@companyid INT IDENTITY(1,1) NOT NULL,@companyname varchar(20),@address1 varchar(30)ASINSERT INTO companymaster                      ( companyname, address1)VALUES     (@companyname,@address1)i don't want the companyname having the same names are recorded again with the different company id..Can anyone help me and modify my code according it's giving error...in the @companyid.It is being done in sql server 2005 with asp.net C# 2005 

View 1 Replies View Related

Help Creating A Stored Procedure

May 6, 2007

I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..
I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..create table dbo.custom_Profile (
UserId uniqueidentifier not null Primary Key,
IamWeAre nvarchar(50) null,
InterestedIn nvarchar(256) null,
IntroTitle nvarchar(100) null,
TellOthers nvarchar(MAX)null,
MaritalStatus nvarchar(20) null,
BodyType nvarchar(50) null,
Race nvarchar(20) null,
Smoking nvarchar(20) null,
Drinking nvarchar(20) null,
Drugs nvarchar(20) null,
Education nvarchar(256) null)

go 

View 15 Replies View Related

Creating Stored Procedure

Mar 16, 2008

I have a table (displayed in a gridview) of services we provide. I have another table (the logfile) that displays the current status of those services. This GridView displays the service and current status.When a new service is created there is obviously no status yet. This causes a problem because my stored procedure (below) does not display that new service in my GridView. How can I ensure EVERY service is included in my dataset regardless of whether or there is a status?  (and how can I get away from having to group by all the time?)
select s.servicename, s.opr, c.commentid,c.comment, c.etic, t.statusfrom svc_service sinner join svc_comment c on c.serviceid = s.serviceidinner join svc_status t on t.statusid = c.statusidgroup by s.servicename, s.opr, c.commentid, c.comment, c.etic, t.status
 TIA,
Jon

View 4 Replies View Related

Creating Stored Procedure

Jul 8, 2004

I have an inline sql query which i want to convert to a stored procedure.
The query is simplified as follows:

string sql = "SELECT * FROM property_property WHERE Location = " + location;
if(state != null) sql += " AND State = " + state;
if(cost !=null) sql += " AND Cost = " + cost;

The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc

I know some of the basics and managed to convert every other query except this.

Any help would be appreciated

View 3 Replies View Related

Needs Help With Creating A New Stored Procedure

Dec 4, 2005

I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value.
Here's my simple stored procedure to show one record:
CREATE PROCEDURE DisplayCity(@CityID int)AS
SELECT City From City where CityID = @CityIDGO
My code for displaying the City:
Sub ShowCity()
    Dim strConnect As String
    Dim objConnect As SqlConnection
    Dim objCommand As New SqlCommand
    Dim strCityID As String
    Dim City As String
  
 
    'Get connection string from Web.Config
    strConnect = ConfigurationSettings.AppSettings("ConnectionString")
    objConnect = New SqlConnection(strConnect)
 
    objConnect.Open()
 
    'Get incoming City ID
    strCityID = request.params("CityID")  
 
   
    objCommand.Connection = objConnect
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.CommandText = "DisplayCity"
 
    objCommand.Parameters.Add("@CityID", CInt(strCityID))
   
   
    'Display SubCategory
    City = "" & objcommand.ExecuteScalar().ToString()
    lblCity.Text = City
     
      lblChosenCity.Text = City
  
    objConnect.Close()
 
End Sub

Here's the code I'd like to get help with changing into a stored procedure:
Sub BindDataList()
 
             Dim strConnect As String
             Dim objConnect As New System.Data.SqlClient.SQLConnection
             Dim objCommand As New System.Data.SqlClient.SQLCommand
             Dim strSQL As String
             Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter()
             Dim dtsAdvertiser As New DataSet()
             Dim strCatID As String
             Dim strCityID As String
             Dim SubCategory As String
             Dim SubCategoryID As String
             Dim BusinessName As String
             Dim City As String
 
            
             'Get connection string from Web.Config
             strConnect = ConfigurationSettings.AppSettings("ConnectionString")
 
             objConnect = New System.Data.SqlClient.SQLConnection(strConnect)
    
             objConnect.Open()
                   
                   'Get incoming querystring values
              strCatID = request.params("CatID")
              strCityID = request.params("CityID")
             
             
 
             'Start SQL statement
             strSQL = "select * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City"
             strSQL = strSQL & " where Advertiser.CategoryID=Categories.CategoryID"
             strSQL = strSQL & " and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID"
             strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID"
             strSQL = strSQL & " and Advertiser.CountyID=County.CountyID"
             strSQL = strSQL & " and Advertiser.CityID=City.CityID"
             strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID = '" & strCatID & "'"
             strSQL = strSQL & " and Advertiser.CityID = '" & strCityID & "'"
             strSQL = strSQL & " and Approve=1"
           strSQL = strSQL & " Order By ListingType, BusinessName,City"
 
  
 
 
        'Set the Command Object properties
        objCommand.Connection = objConnect
        objCommand.CommandType = CommandType.Text
        objCommand.CommandText = strSQL
 
        'Create a new DataAdapter object
        dtaAdvertiser.SelectCommand = objCommand
 
        'Get the data from the database and
        'put it into a DataTable object named dttAdvertiser in the DataSet object
        dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")
       
        'If no records were found in the category,
        'display that message and don't bind the DataGrid
                 if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then
 
                         lblNoItemsFound.Visible = True
                         lblNoItemsFound.Text = "Sorry, no listings were found!"
                                                                   
                 else
 
        'Set the DataSource property of the DataGrid
        dtlAdvertiser.DataSource = dtsAdvertiser 
 
      'Set module level variable for page title display
        BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName")
        SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory")
        SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID")
        City = dtsAdvertiser.Tables(0).Rows(0).Item("City")
  
        'Bind all the controls on the page
        dtlAdvertiser.DataBind()    
 
             
      
        end if
       
        objCommand.ExecuteNonQuery()
       
        'this is the way to close commands
        objCommand.Connection.Close()
       
        objConnect.Close()
 
End Sub   


 
 

View 4 Replies View Related

Creating SP With Stored Procedure ?

Jun 19, 2000

How can I create a Stored Procedure name within stored procedure ?

Example:


CREATE PROCEDURE A_1000
AS
..............
..............
..............
create procedure B_2000
..............
..............
..............

Is that possible ?

I'm trying to create a SP within the SP.

Thankx for ur time

Thankx a lot

View 1 Replies View Related

Creating A Stored Procedure

Jun 11, 2007

I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report and I have never done a stored procedure and I was wondering if it would be ease to do?

FirstName..LastName.....StartedDate
Randy......Simpson......5/4/2007 10:00:00 PM
Steve......Rowe.........5/2/2007 10:00:00 PM
Eric.......Dickerson....5/4/2007 10:00:00 PM
Gloria.....Sanches......5/1/2007 12:00:29 AM
Andres.....Marcelino....5/1/2007 12:06:31 AM
katie......ryan.........6/4/2007 12:08:35 AM
Denise.....River........6/4/2007 12:27:14 AM
Kellog.....Stover.......6/5/2007 12:37:20 AM
Glenn......Sanders......6/1/2007 12:42:40 AM

View 3 Replies View Related

Creating A Stored Procedure

Jul 20, 2005

Im trying to create a stored procedure that selects everything from afunction name that im passing in through a parameter..create procedure SP_selectall(@functionname varchar(25))asselect * from @functionamegoI keep getting this error:Server: Msg 137, Level 15, State 2, Procedure SP_selectall, Line 5Must declare the variable '@functioname'.Whats the issue?

View 1 Replies View Related

Need Help In Creating Stored Procedure Insert

Apr 2, 2007

Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert
2
3 @companyid int,
4 @companyname varchar(20),
5 @address1 varchar(30)
6
7 AS
8
9 INSERT INTO companymaster
10 ( companyname, address1)
11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which
was not supplied.

The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....    

View 5 Replies View Related

Having Difficulty Creating A Stored Procedure

Aug 28, 2007

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am havingdifficulty though. Whenever I press the execute button, here is the error message I get:
Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21Incorrect syntax near 'MarketName'.
Here is the stored procedure. Note that the very first column in named "MarketId" but I did notinclude it in the stored procedure since it should be auto generated.
USE [StockWatch]GO/****** Object:  StoredProcedure [dbo].[MarketCreate]    Script Date: 08/28/2007 15:49:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
CREATE PROCEDURE [dbo].[MarketCreate]
(  @MarketCode   nvarchar(20),  @MarketName   nvarchar(100),  @LastUpdateDate  nvarchar(2),  @MarketDescription  nvarchar(100))
ASINSERT INTO Market(  MarketCode  MarketName  LastUpdateDate  MarketDescription)VALUES(  @MarketCode  @MarketName  @LastUpdateUser  @MarketDescription)

View 2 Replies View Related

Problem Creating Stored Procedure Ni VWD

Jan 3, 2008

I was trying to create a new stored procedure.  It's pretty straight forward using Insert Into.  Here's what I wrote:
CREATE PROCEDURE dbo.InsertPicks@ID varchar,@Race int,@P1 varchar,@P2 varchar,@P3 varchar,@P4 varchar,@P5 varchar,@P6 varchar,@P7 varchar,@P8 varchar,@P9 varchar,@P10 varchar,
INSERT INTO tblPicks(pRace, pPlayer, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)VALUES(@Race,@ID,@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10) RETURN
I first wrote the T-SQL code myself, but when I opened the Query Builder so I could validate it and check it.  It validated and when I executed it, it worked great and inserted a new record into the table.  Yet, when I closed the Query Builder, and tried to save the Stored Procedure (which I assume is how you create it) I get an error that there is a Incorrect Syntax near "Insert".  I see nothing wrong with the syntax, and it worked fine in the Query Builder.  What am I doing wrong?
Thanks,
Bob

View 2 Replies View Related

Creating A Stored Procedure From A File

Mar 23, 2008

Is there a way I can use one SQL server 2005 (full edition not express) Stored procedure that can execute a .SQL file as if it were in the SQL management studio Query window?
 I have the situation where I have some .SQL file which were created with the 'Create to File' option in SQL Management studio.  I then want to send them to a user who can execute them to create a SP on his SQL server without having to run the SQL management tools.
 Regards
Clive

View 8 Replies View Related

Creating A Stored Procedure Not So Simple...?

May 21, 2008

I am using SQL Server Express within visual studio and am needing to create a stored procedure. The proble is that every time I finish and close it then it asks me if I want to save the changes which of course I want to.
But then it always comes up with a message box that says "invalid object name"  followed with the name of the stored procedure.
??? why? I try creating a stored procedure from scratch or copying the code of another stored procedure. It alway gives me that message and does not let me save the stored procedure. I tried changing the default name of "dbo.storedprocedure1" or even leaving it as is and always I get the message that says "invalid object name: dbo.storedprocedure1" and it does not let me save the procedure.
 What am I doing wrong?

View 5 Replies View Related

Problem On Creating Stored Procedure

Feb 2, 2004

The code is as below:

--Drop procedures if they exsit
if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItems]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItems]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItemsByTime]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItemsByTime]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_selectedEventMessage]') AND OBJECTPROPERTY(id,N'IsProcedure')=1)
drop procedure [dbo].[sp_selectedEventMessage]
GO

--Definitions of procedures
USE LanDeskDB
GO

CREATE PROCEDURE sp_PagedItems
(
@QueryVARCHAR(1000),
@Pageint,
@RecsPerPageint,
@startDateVARCHAR(100),
@endDateVARCHAR(100),
@allTimeint,
@flagint
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(2000)
DECLARE @Order VARCHAR(200)
DECLARE @TotalBIGINT

CREATE TABLE #TempTable
(
TempTableID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT
)
IF (@flag = 1)
BEGIN
SET @Order = 'ORDER BY EventDateTime'
END
IF (@flag = 2)
BEGIN
SET @Order = 'ORDER BY SessionID,EventDateTime'
END
IF (@flag = 3)
BEGIN
SET @Order = 'ORDER BY TypeID,EventDateTime'
END
IF (@flag = 4)
BEGIN
SET @Order = 'ORDER BY CategoryNumber,EventDateTime'
END
IF(@allTime <> 1)
BEGIN
IF(LEN(@Query)>1)
BEGIN
SET @Query = @Query+'AND EventDateTime>='''+@startDate+''' AND EventDateTime <= '''+@endDate+''''
END
ELSE
BEGIN
SET @Query = 'WHERE EventDateTime>='''+@startDate+''' AND EventDateTime <= '''+@endDate+''''
END
END
SET @SQL=
'INSERT INTO #TempTable (EventLogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID)'+
'SELECT EventlogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID FROM EventLog '+
@Query+' '+@Order
EXEC (@SQL)


CREATE TABLE #TempTableTwo
(
TempTableTwoID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
TempTableID BIGINT,
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT,
)


DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = @Page * @RecsPerPage+1


INSERT #TempTableTwo

SELECT * FROM #TempTable T
WHERE T.TempTableID >@FirstRec AND T.TempTableID < @LastRec


SELECT TempT.EventLogID AS EventLogID,TempT.EventDateTime AS EventDateTime,
Ma.MachineName AS MachineName,Se.SessionName AS SessionName,Ty.TypeName AS TypeName,
TempT.CategoryNumber AS CategoryNumber,Us.UserName AS UserName,So.SourceName AS SourceName,
TempT.EventID AS EventID
FROM #TempTableTwo TempT,Machines Ma,Types Ty,Sessions Se,Sources So,Users Us
WHERE TempT.MachineID = Ma.MachineID AND TempT.TypeID = Ty.TypeID AND TempT.SessionID = Se.SessionID
AND TempT.SourceID = So.SourceID AND TempT.UserID = Us.UserID

SELECT COUNT(*) FROM #TempTable

DROP TABLE #TempTable
DROP TABLE #TempTableTwo
SET NOCOUNT OFF
END
GO

CREATE PROCEDURE sp_PagedItemsByTime
(
@QueryVARCHAR(1000),
@Page int,
@RecsPerPage int,
@startDateVARCHAR(100),
@endDateVARCHAR(100),
@allTime int
)
AS
BEGIN
EXEC sp_PagedItems @Query,@Page,@RecsPerPage,@startDate,@endDate,@allTime,1
END
GO



CREATE PROCEDURE sp_selectedEventMessage
(
@EventLogID int
)
AS

BEGIN

SELECT Ma.MachineName,Ev.EventDateTime,Se.SessionName,Ty.TypeName,So.SourceName,Me.MessageDescription
FROM EventLog Ev,Sessions Se,Types Ty,Sources So,Messages Me,Machines Ma
WHERE Ev.EventLogID = @EventLogID AND Ev.SessionID = Se.SessionID AND Ma.MachineID = Ev.MachineID
AND Ev.TypeID = Ty.TypeID AND Ev.SourceID = So.SourceID AND Ev.MessageID = Me.MessageID

END

GO



I got the error messge as
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItems, Line 107
There is already an object named 'sp_PagedItems' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItemsByTime, Line 13
There is already an object named 'sp_PagedItemsByTime' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_selectedEventMessage, Line 12
There is already an object named 'sp_selectedEventMessage' in the database.

But I already delete those procedures before I create them. Could anyone give some suggestion?

View 2 Replies View Related

Creating System Stored Procedure In 7.0

Mar 14, 2001

Can I create system stored procedure in sql 7.0?
After I used 'alter' to modify a system sp, it's category
change from 'system' to 'user'. Is there way to change it back?
Thanks a lot!

Xiao

View 1 Replies View Related

Error While Creating A Stored Procedure

Aug 30, 2012

I am working with SQL Server 2005 Express and have written a query which works well in when run it in the managment console as a select query.

When I try to create a stored procedure out of it, I get the following error:

Code:
Msg 102, Level 15, State 1, Procedure sp_SegmentationList_ByorderID, Line 20 Incorrect syntax near ';'.

Procedure is below:

Code:
USE [ARC_Test]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_SegmentationList_ByorderID]
@OrderID int

[code].....

View 3 Replies View Related

Script For Creating A Stored Procedure???

Jan 14, 2004

Hello,
I'm trying to create a store procedure from an interface which is similiar to Query Analyzer, but I can't get it to work and I don't get an error message...so I'm not sure what error I am receiving. Please see the below sp_get_topics (#1) which is not saving and sp_get_topics (#2 works). Thanks in advance....

--#1--
CREATE PROCEDURE [sp_get_topics] AS
SELECT t_1.Topic, t_1.Message, t_1.Create_Date, t_2.Username
FROM t_1 WITH (READUNCOMMITTED) LEFT OUTER JOIN
t_2 WITH (READUNCOMMITTED) ON t_1.User_ID = t_2.ID
GO

--#2--
CREATE PROCEDURE [sp_get_topics]
AS
SELECT Topic, Message, Create_Date
FROM t_1
GO

View 11 Replies View Related

Creating A View In A Stored Procedure

Apr 2, 2004

Hi Everyone

Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

CREATE PROC upProcName AS

DECLARE @Variable varchar(50)

CREATE VIEW vwName AS

SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @Variable

GO

Any Thoughts ideas would be great

Cheers

View 7 Replies View Related

Creating A Stored Procedure In Another Database

Feb 27, 2008

From within a stored procedure in an existing database I'm creating a new database from the scratch.

In the query window if I write the statement:
CREATE DATABASE [tomasdb]

USE [tomasdb]
GO
CREATE PROCEDURE TEST1
AS
BEGIN
DECLARE @something varchar(12)
set @something = '123'
END

everything works ok and the database is created and the stored procedure Test1 is created within it.
However, if I want to do the same from another stored procedure (in another database), i.e. I write

EXEC('
CREATE DATABASE [tomasdb]
USE [tomasdb]
CREATE PROCEDURE TEST1
AS
BEGIN
DECLARE @something varchar(12)
set @something = ''123''
END
')

the problem I get is that I'm not allowed to put USE to change the execution context into [tomasdb] before the CREATE PROCEDURE statement.
My question is: how can I change the context in this situation / or / can I create the stored procedure in the [tomasdb] database somehow from outside (another context).

Thanks,
Tomas

View 4 Replies View Related







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