How To Write One Stored Procedure For A (same Column )present In 20 Tables
Mar 8, 2006
hi,
i am a learner of ms -sql server 2000, i had a doubt in stored
procedures
suppose i have a data base having 20 tables, all the tables have a
column named--DATE
can we write a store procdure to find out the data ---i mean i want
the data entered
between two days ---- if i call the stored procedure in any one of the
table i need to get the answer
pls help me how to write the stored procedure
satishkumar.g
View 2 Replies
ADVERTISEMENT
Mar 28, 1999
When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?
Any info greatly appreciated!!
Thanks,
Jay
View 1 Replies
View Related
Jul 15, 2014
There are two tables in my database HotelList_Temp, AsiaHotelChildrenAges_Temp
I need to write a procedure to upload the xml files into above tables .xmlfile is in my pc
xml to table column mapping are given below
<HOTEL>
<SEQNO>1</SEQNO>
<GRADE>5</GRADE> --StarRating
<PRODUCT>H</PRODUCT>
<CITYAREA>CKOWM</CITYAREA> --CityLocation
<CITY>HKG</CITY> --ResortCode
<CITYAREANAME>Mong Kok </CITYAREANAME> --ResortName
[code]....
View 9 Replies
View Related
May 10, 2007
Hi,I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.The stored procedure accepts 2 parameters like this:Create proc [dbo].[spGetPeerFromCode]( @FromCode VARCHAR(50)) (@WebVersionString VARCHAR(50))as please assist.
View 1 Replies
View Related
Jan 28, 2008
Hi all,
I want to write a stored procedure for the following,
The table have the following field,
CategoryID, CategoryName, Parent_categoryID
123 Kids Dresses 0
321 Kids Boys 123
322 Kids Baby 123
401 Kids Boys school Uniform 321
501 Kids Boys Formals 321
541 Kids Baby school Uniform 322
542 Kids Baby Formals 322
601 Household Textile 0
602 Bathrobe 601
603 Carpet 601
604 Table Cloth 601
From the above example the categoryID acts as a parent_categoryID for some products,
when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
For example when i pass categoryID as 123 it should return the following subcategoryIDs
321,322,401,501,541,542 because these are all subcategory of categoryID 123.
How to write stored procedure for this?
Thanks!
View 2 Replies
View Related
Mar 21, 2007
Hai Friends,
I heard that stored procedures can be written using two servers
how to write stored procedures by using two servers
CAN ANYONE GIVE INFORMATION ABT THIS
Malathi Rao
View 4 Replies
View Related
Mar 21, 2008
Hi, where do you write an SQL stored procedure?
View 1 Replies
View Related
Aug 23, 2007
How to write stored procedure with two parametershow to check those variables containing values or empty in SP
if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one
Select * from orders plz write this SP for me thanks
View 3 Replies
View Related
Jan 28, 2008
Hi all,
I want to write a stored procedure for the following,
The table have the following field,
CategoryID, CategoryName, Parent_categoryID
123 Kids Dresses 0
321 Kids Boys 123
322 Kids Baby 123
401 Kids Boys school Uniform 321
501 Kids Boys Formals 321
541 Kids Baby school Uniform 322
542 Kids Baby Formals 322
601 Household Textile 0
602 Bathrobe 601
603 Carpet 601
604 Table Cloth 601
From the above example the categoryID acts as a parent_categoryID for some products,
when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
For example when i pass categoryID as 123 it should return the following subcategoryIDs
321,322,401,501,541,542 because these are all subcategory of categoryID 123.
How to write stored procedure for this?
Thanks!
View 6 Replies
View Related
Feb 7, 2008
Hai friends,
The project i'm working on is an asp.net project with SQL Server 2000 as the database tool.
I've a listbox (multiline selection) in my form, whose selected values will be concatenated and sent to the server as comma separated numbers (Fro ex: 34,67,23,60).Also, the column in the table at the back end contains comma separated numbers (For ex: 1,3,7,34,23).
What i need to do is -
1. Select the rows in the table where the latter example has atleast one value of the former example (In the above ex: 34 and 23).
2. Check the text value of these numbers in another table (master table) and populate the text value of these numbers in a comma separated format in a grid in the front end.
I've programmed a procedure for this. but it takes more than 2 minutes to return the result. Also the table has over 20,000 rows and performance should be kept in mind.
Suggessions on using the front-end (asp.net 2.0) concepts would also be a good help.
Anybody's helping thought would be greatly appreciated...
Thanks lot...
View 2 Replies
View Related
Jun 25, 2007
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
Thank you.
View 1 Replies
View Related
Sep 20, 2007
hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????
View 5 Replies
View Related
Apr 6, 2006
Could you write a special stored procedure for me in SQL 2005?
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= SportTagName= NewTagName= Health
I hope there is a stored procedure which can join all TagName into a single string and return,it means when I pass @IDTopic to the stored procedure, it return "Sport,New,Health"
How can write the stored procedure?
View 2 Replies
View Related
Mar 28, 2004
Hi All,
I need some help from you experts.
I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml.
I should be able to give this as a job to exectue daily ,etc.
Can we do this using stored procedures in SQLServer.
Pls paste some sample code, if any or direct me to any url with better info.
Thanks in advance
View 11 Replies
View Related
Oct 19, 2011
I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?
View 6 Replies
View Related
Sep 22, 2006
Can you, and if yes, how do you, write an xml file using a stored procedure. For example the sp below writes this new record to a sql table. How would I change it to write it to an xml file ?
CREATE Procedure [spOB_AddtblOB_Properties]
@strPropertyRef varchar (100),
@strRouteNo numeric,
@strAdd1 nvarchar(1000),
@strPostcode nvarchar(10)
as
Insert into tblOB_Properties (
PR_PropertyRef,
PR_RouteNo,
PR_Add1,
PR_Postcode)
values(
@strPropertyRef,
@strRouteNo,
@strAdd1,
@strPostcode)
GO
View 1 Replies
View Related
Dec 5, 2006
hi,
I need to write a clr stored prodeure.i have to call that stored procedure from my prediction query.My Application is going to make use of that query to get the prediction output.
Is it possible to write clr stored procedure with out using adomd server.How?
For example:
In My Assembly i am having a function PredictPerformance(),Which is having my DMX Query.I need to execute that function from my analysis service by calling like this,
select aly. PredictPerformance() FROM [Model].
how to do this?
View 1 Replies
View Related
Aug 19, 2007
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as
TagName= http://www.hothelpdesk.com
TagName= http://www.hellocw.com
TagName= http://www.supercoolbookmark.com
I hope there is a stored procedure which can join all TagName into a single string and return,
it means when I pass @IDTopic to the stored procedure, it return "http://www.hothelpdesk.com, http://www.hellocw.com, http://www.supercoolbookmark.com"
How can write the stored procedure?
View 1 Replies
View Related
Feb 28, 2008
Hi i m using vwd2005 express and sql express.1. I want to write stored procedure in sql express using sql server management studio express. I m not sure where to write and how to execute the stored procedure in sql server management studio express. Can any one explain the steps required to achieve this? 2.And what is the difference between creating database using:- a. right click the project in solution explorer-> add new item->SQl database(creating .mdf file)and b.clicking a database explorer->data connection->Add connection .... thanks. jack.
View 4 Replies
View Related
Mar 8, 2005
I have a Orders and OrderDetails table having the columns listed below:
Orders (OrderID, OrderDate, CustomerID, ...)
OrderDetails (OrderID, ProductID, UnitPrice, Quantity, ...)
Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are:
1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what?
2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year?
3. how to stop processing for the rest of the year when last order month is done?
Any suggestion would be appreciated. Thanks.
View 1 Replies
View Related
Sep 13, 2005
I can get following data information from table1Name DescriptionA AviationA01 Aviation GeneralCA01 CapitalCA01-006 NEW CTB SignageA02 CapitalCA05-005 East End RoadwayCA05-006 Modify RoadHow do I write stored procedure to get following results based on the data from table1Code Name Description Level 1 A Aviation 1 1.01 A01 Aviation General 2 1.01.01 CA01 Capital 3 1.01.01.006 CA01-006 NEW CTB Signage 4 1.02 A02 Capital 2 1.02.01 CA05-005 East End Roadway 3 1.02.01.006 CA05-006 Modify Road 4Thank a lot!
View 1 Replies
View Related
Mar 14, 2014
Is it possible to write a stored procedure that will be used registered server?
For example I have registered server named 'Test Servers' and I want to use it in stored procedure
View 3 Replies
View Related
Sep 25, 2015
I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.
I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT
[Code] ....
i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement.
View 7 Replies
View Related
Feb 25, 2008
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved.
John Smith
- Jacob Jones
- Lisa Thompson
- Samuel Barber
- Paul Smith
- John Jackson
Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber.
Here are the tables:
Users holds the user_id, first_name, last_name, and reports_to_user_id.
User_Roles holds the user_id, role_type_id
Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest.
Thanks very much in advance,
Saied
View 12 Replies
View Related
Aug 4, 2015
I am having table on which i want to fire a query on.
table structure is as follows :
Table1
Table1ID bigint PK
City nvarchar(10)
FirstName nvarchar(10)
LastName nvarchar(10)
MiddleName nvarchar(10)
State nvarchar(10)
FirstName nvarchar(10)
LastName nvarchar(10)
LLDCode nvarchar(20)
MMDCode nvarchar(20)
LastModified datetime
CreatedDateTime datetime
LastUpdatedDateTime datetime
SSN# nvarchar(20)
I have to write a parameterised stored procedure where multiple values will be pass as input to SP.
E:g: 1) SSN# if no record found by SSN# then 2) Find by LLDCode OR MMDCode
This logic can be implemented using UNION to join output of 2 queries and selecting by LastUpdatedDateTime this query will return the Table1ID.
Now we will fire the query on table2 where this Table1ID as FK in Table2 and we will return the filed SSXML from Table2.How to do this ?
Table2 structure.
Table2ID PK
Table1ID FK
Name
Department
SSXML
View 9 Replies
View Related
May 12, 2007
Hi frdz, I m creating my web-application in asp.net with C# 2005 and using sql server 2005. I have created the stored procedure for the insert,update. I want to know how to write the mathematical calculations in the stored procedure.. Pls tell me from the below stored procedure were i m making the mistake ?? As the discount and the total amount are not calculated by itself....and stored in the database How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers.... ALTER PROCEDURE CalculationStoredProcedure
@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)
as
begin
set nocount on
if @opbal IS NULL OR @opbal = 0
begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end
select @accountid = isnull(max(accountid),0) + 1 from accountmaster
select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster
begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode
)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)
end
set nocount off
end
Thanxs in adv...
View 7 Replies
View Related
Feb 3, 2006
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
View 7 Replies
View Related
Jan 23, 2015
I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
View 2 Replies
View Related
Dec 20, 2012
There are two tables A and B where asset tags are present, but in one table in rows and in another in column wise.
for eg
ASSet Tag
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572
SR-072009-00020571
SR-072009-00020585
HH-092009-00038342
Table B
field 1 -->Asset TAG
Record 1-->SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617
field 2 --> Material code
REcord 1-->121
REcord 2-->123
What is the query so that asset tag of A matches with each and every asset tag table of B and output comes as
Output
Asset TAg -------- MAterial Code
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572 ------121
SR-072009-00020571 -------121
SR-072009-00020585
View 5 Replies
View Related
Jan 18, 2008
Hi,
Can anyone help me?
when I am inserting a record to a dataset I am getting Column 'FieldID' is constrained to be unique. Value '0' is already present.
I have 2 fields in table like FieldID,FieldName where FieldID id is Primary key and identity column
I am not inserting any value to FieldID as it is identity column.
DataRow newDR= dbDataSet.Tables[strTable].NewRow();for (int i = 0; i < e.Row.Cells.Count; i++)
{ if ( e.Row.Cells[i].Key != "FieldID" )
{if (e.Row.Cells[i].Value != null)
{
newDR[e.Row.Cells[i].Key] = e.Row.Cells[i].Value;dbDataSet.Tables[ strTable].Rows.Add(newDR);
}
}
Some times it is woking fine with out any error............
thanks..
View 3 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Dec 7, 2007
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
View 4 Replies
View Related
Mar 7, 2008
Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
View 4 Replies
View Related
