hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????
ok...i give up.....
can this be done? (sql server 2000)
all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.
here's my current working file output code:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P'
i'd like it to be something like this:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P'
but nothing seems to work.
I have an existing store proc with insert and update statements, I want to add a condition at the top of the proc with conditions like;
declare @dayofweek int
set datefirst 1
select @dayofweek = datepart(dw,getdate())
--select @dayofweek
if @dayofweek = 1
[Code] ...
If the condition meets with either of the above 2 conditions, than have to run the actual store proc.
My actual Storeproc in which I need to incorp the above conditions is:
CREATE PROCEDURE [dbo].[Load_Product]
@FileDate date = NULL
AS
BEGIN
DECLARE @EventText varchar(500),
@Rows int = 0,
@RowsTotal int = 0,
[Code] .....
i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like
INSERT INTO table1 if id = @id or order = @fdfdfd.
i think it will return no of rows affected with insert statement. am i right?
any help will be appreciated.
Hi,
I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales".
So far I have created the inner proc
CREATE PROCEDURE spSales@userID intASSET NOCOUNT ON
SELECT SalesOppID
FROM Sales_Opportunities
WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped')
AND OppOwnerUserID = @userIDGO
This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as:
CREATE PROCEDURE spAssociate(@userID int,
@containerType varchar(100),
@associateType varchar(100))AS SET NOCOUNT ON
SELECT AssociateID
FROM AppRelations
WHERE ContainerType=@containerType
AND ContainerID IN (EXECUTE spSales @userID)
AND AssociateType=@associateType GO
I get an error "incorrect syntax near execute".
How can I use the results from the inner proc for the WHERE condition in my outer proc?
Any help is greatly appreciated. Thanks!
Hello
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField
FROM myTable
WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
Kind regards
Jens Ivar
I want to write a Stored Procedure which can search based on multiple condition and in default is select all.
Example :
User Name [ ALL ] Textbox
Project Name [ ALL ^] dropdown
ID [ ALL ^] dropdown
-----------------------------
Display On Gridview(Default display all records)
select one condition search one and so on.
I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?
View 5 Replies View RelatedHi,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
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!
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
Hi, where do you write an SQL stored procedure?
View 1 Replies View Related 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
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!
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...
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.
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?
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
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 RelatedCan 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
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?
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?
Hi,
I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-)
flat file source --> conditional split (if Column0 contains a certain string send to error file)
In the Conditional Split Transformation Editor I have the following for "condition":
FINDSTRING( "my search string", [Column0] ,1 )
TITLE: Microsoft Visual Studio
------------------------------
Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean.
Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid.
Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)".
As you can see I want it to search Column0. Can someone please point out my error.
Thanks
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 RelatedI 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.
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 RelatedIs 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
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.
hi,i am a learner of ms -sql server 2000, i had a doubt in storedproceduressuppose i have a data base having 20 tables, all the tables have acolumn named--DATEcan we write a store procdure to find out the data ---i mean i wantthe data enteredbetween two days ---- if i call the stored procedure in any one of thetable i need to get the answerpls help me how to write the stored proceduresatishkumar.g
View 2 Replies View RelatedHello, 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
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
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...
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