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 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.
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.
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.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
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.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
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
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
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.
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
