i need to create procedures with if statements
i am confused since i did not learn much about it
i need to do the following
a)If the employee is not a manager the procedure should print “Name is Individual Contributor”
b)If the employee is a manager, the procedure should list all the employees that report to that manager.
c)If the supplied EmployeeID does not exist, the procedure should print “Employee with ID NNN does not exist.”
This is the table
CREATE TABLE Employee
(EmployeeID int NOT NULL
CONSTRAINT Employee_EmployeeID_PK PRIMARY KEY
, Name varchar(50) NOT NULL
, SIN char(9) NOT NULL
, LoginID varchar(256) NOT NULL
, ManagerID int NULL
, BirthDate smalldatetime NOT NULL
, MaritalStatus char(1) NOT NULL
, Gender char(1) NOT NULL
, HireDate datetime NOT NULL
, VacationHours smallint NOT NULL
CONSTRAINT Employee_VacationHours_DF DEFAULT ((0))
, SickLeaveHours smallint NOT NULL
CONSTRAINT Employee_SickLeaveHours_DF DEFAULT ((0))
, IsManager bit
, NumReports int
, ModifiedDate datetime NOT NULL
CONSTRAINT Employee_ModifiedDate_DF DEFAULT (getdate())
I am thinking of using IsManager as TRUE or FALSE
but i dont know how to put it together
this is my sad try for now
CREATE PROCEDURE ListOrganizationMC
@employeeID int,
@managerID int,
@isManager bit
AS
select name
from employeemc
if @ismanager = 0
Begin
Print 'Name is Individual Contributor'
end
SET @employeeID = @managerID;
go
im sure the top part of the parameters are wrong
i just want to enter one parameter
I have to create 5 procedures and the study material I have dont explaine procedures enough.
My 5 procedures are as follows:
*sp_ProductInsert - Allows the user to add a record to the PRODUCT and PRICE tables.
*sp_PriceUpdate - Allows the user to change the price of a specified product. The start and end dates (columns) of the price must also be updated. Arguments: ProductID, new price, start date, end date.
*sp_DeleteProduct - Allows the user to delete a specified product. Records related to the product must be removed from the PRICE table but if the product appears on an invoice that is not more than 60days old, it may not be deleted. Argument: Product ID.
*sp_DeleteClient - Allows the user to delete a specified client. Argument: ClientID.
*sp_displayInvoice - Displays the details and the total cost of a specific invoice.
**************************************** Please id appreciate any help!!
my tables that I have:
CLIENT with id, name, address, tel
INVOICE with id, date, clientID
PRODUCT with code, description
PRICE with productCode, startDate, endDate, price ********* I will also be greatful for extra reading regarding procedures. Thanks.
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.
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
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?
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 Analyzer 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?
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?
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.
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.
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?
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?
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
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
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
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
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
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
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.
Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.
I found lots of methods for individually scripting out stored procedures, but I'd like to know if there's a way to do many (like 100 or so) at once. Ideally, I'd like to execute something that would save all the procedures that start with 'dev_' to the file system somewhere, then delete the dev_ SP's from the database and leave the other SPs that actually get used. Is that possible in one step?Â
If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?
The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems.Â
If I group all stroed procs in one that simply calls all of them in the needed sequence:
exec stproc1 exec stproc2 ..... exec stprocn
Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?
I am trying to create a merge publication in sql server 2005 and a merge subscription in sql server 2005 mobile through transact sql using sql server query analyzer.
I am succeeded in creating the publication but not subscription.
I am using sp_addmergepublication and sp_addmergesubscription.
sp_addmergepublication is working fine.
But the problem is in creating sp_addmergesubscription.
Can anyone please guide me how to create subscription to sql server 2005 mobile database using transact sql.
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm Set to: Post Action: results.asp Text Field: Keyword Drop Down: CategoryTable Drop Down: Location UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType FROM Query1 WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail' ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults idSearch (AutoNumber) location (Text) "Want to insert the 'Location' result here" category (Text) "Want to insert the 'CategoryTable' result here" user (Text) "Want to insert the UserName Session Variable result here" result (Text) "Want to insert the 'Keyword' result here"
I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.
Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4 Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.
The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.
What am I doing wrong?
Thanks in advance to any one who can help me with this problem.
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:
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.
We have a required to run multiple procedures in Single Go . And Error Occurred in any Procedure the it will rollback all the changes( Either all Proc run or None)
DECLARE @StartTime DATETIME=getdate(), @EndTime DATETIME=getdate()-1 , @Message VARCHAR(400) Â
Problem Statement is its not capturing the Error Message from Either Proc1 or Proc 2., its Capturing the Flat Message (The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction). How do i capture the Error Occurred in Proc 1 or Proc 2 Â into Log Tables.
I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.