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
ADVERTISEMENT
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
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
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 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?
Many thanks
Quish
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jul 23, 2005
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!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
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.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Jan 31, 2007
Hi,
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.
View 13 Replies
View Related
Nov 6, 2007
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?
Thanks,
Jason
View 16 Replies
View Related
Sep 26, 2006
I am trying to read a SP.sql file where i want to write all the procedures to create them in the database
CREATE PROCEDURE SP_1
AS
BEGIN
DELETE FROM tb1
END
CREATE PROCEDURE SP_2
AS
BEGIN
DELETE FROM tb2
END
if there is only one procedure in the file it works, with > 1 procedure it fails
inserting GO between doesnt work
how is it possible to run a sql file with many stored procedures in it from a server langages ?
thank you
View 8 Replies
View Related
Mar 22, 2008
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
View 11 Replies
View Related
Apr 29, 2008
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'
View 1 Replies
View Related
Aug 24, 2006
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.
Diego Sierra
View 4 Replies
View Related
Jun 13, 2007
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?
View 3 Replies
View Related
May 13, 2008
Greetings:
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?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
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"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
