|
You can do something like:
Create Proc up_Maintain (@mode char(1))<br />
if @mode = 'a'<br />
begin<br />
....code to add<br />
return<br />
end<br />
if @code = 'd'<br />
begin<br />
....code to delete<br />
return<br />
end<br />
begin<br />
....code to update<br />
return<br />
end
However this is bad practice as the optimiser will optimise for the first time the proc is run. An accepatable alternative is to have a proc like this, but keep the add/delete/update procs and just call thme from the controlling proc, but you gain nothing.
As far as I can see there is nothing to be gained from merging the procs together, but there are potential performance issues, so they should be kept independantly.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank You For Your Response.
|
|
|
|
|
We can use this procedure in c# application?
|
|
|
|
|
I have return the code in c# application using individual store procedue.
How to write the application code using Multiple query with single stored procedure?
public DataSet GetDayHours(string strSingleDay)
{
//Get the Day,OpenHours and CloseHours from Database
string GetConnection = Connection.GetConnection();
SqlConnection strConnectionString = new SqlConnection();
strConnectionString.ConnectionString = GetConnection;
strConnectionString.Open();
SqlCommand cmdsearch = new SqlCommand("SP_SelectOfficeOpenCloseHours", strConnectionString);
cmdsearch.CommandType = CommandType.StoredProcedure;
cmdsearch.Parameters.Add(new SqlParameter("@DayOfTheWeek", strSingleDay));
SqlDataAdapter daAdapter = new SqlDataAdapter(cmdsearch);
daAdapter.Fill(dsGetDayHours);
}
|
|
|
|
|
So you don't want to have multple actions in a single stored prcoedure? Please clarify what you do want to achieve.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi
how to get list of all tables that exist in my sql server instance ?
i'm using this scrpit :
select * from sys.tables
but this command return only tables that exist in Current Database.
thanks
|
|
|
|
|
Check my reply to you on C# forum.
I Love T-SQL
|
|
|
|
|
I have a nasty little job where I have a CSV table with 50+ cols which will grow (1-2 per year)
The last 44+ cols are in sets of 3, where AttibuteName changes but the date fields do not.
AttibuteName, Start_Date, End_Date
Therefore the columns are not unique and their number is not stable.
At this point SSIS seems to have a serious issue, however this could be resolved if the import method created cols based on the cols in the source file. I'm quite happy to have the labels in the 1st data row and all data to be varchar as I can deal with this in TSQL but dynamically mapping the new columns is the issue.
Ideally I would like to drop the table(no problem)
Bulk copy into a new table based on the source file
Rename the destination file (no problem)
Process the datavia stored proc (no problem)
I have written an app which does exactly what I want but it is a manual thing and I really want to automate this. Having told the boss that writing ETL apps for each module is wrong, I need to clean this one up.
TIA
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Customer can enrol several operation.
Each operation can be enroled by many Customer.
Customer own Operations and not own operations are displaied.
And When Customer will use a operation ,check it that has enroled.
how to design the tables to display these relation ?
I want to know the best desgin.
|
|
|
|
|
Hi
This is nothing ADO.NET but SQL... (not quite sure if this is right forum). I am new to Sybase and today using sqsh (http://www.sqsh.org/[^]) not sure how to "clear screen".
Anyone familiar with this?
Thanks!!
devy
|
|
|
|
|
May be The title Of the Question Sounds Strange But I Will Explain What I mean ...
I'm Analysing Web Application And I am Very confused
The Problem Is That I didn't use T-sql in Complex Scenario so I don't Know If the T-Sql
Has the Ability To manipulate Date Like .Net Languages In Easy Way ??
Of course I Know That There are loops, conditions , interact With Variables , Builtin Methods
And Etc......
But Is it Easy To Develop , Update .........
Thanks  !
You have To Search About The Truth Of Your Life
Why Are you Here In Life ?
|
|
|
|
|
I've seen the best of both worlds. Stored procedures so complex, thousands of lines long and splattered with dynamic sql's and temp tables and numerous loops. And also BLL's having thousands of procedures. In the end, I guess the call is yours, if you're the sole developer. If you're in a team, you might want to leverage your team's skillset.
Developing Business Logic in T-SQL is not for the faint hearted, and is usually lengthier and more complex than a similar implementation in .NET. And T-SQL is pretty limited when it comes to inbuilt functions and constructs. Nowhere near to the .NET libraries, that's for sure. A traditionalist would put his BLL's in the application and use the backend just for DML's.
In the end, it's your call. If your Business Logic is very complex, stick to .NET, else if it's just a few checks and conditions, T-SQL would do just fine.
Alternately if your database server is much more powerful than your web server, you also have the option of using CLR Stored Procedures.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Please don't cross post. I guess this is where it belongs, as your question had nothing to do with VB.
SQL is really easy. Much more so than VB. But, it can get complex, it depends on how complex you need to get, to get what you need. Buy a book.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
hi all
in my table i had a colum contain dattime data the formating date
like this "13/12/2007 09:16:41 "
when i make a select statement by where clause and between condition
as
select * from MessageAccepted where AcceptedDateTime between '22/10/2007'and '23/10/2007'
SQL gives me this error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
is there is a way to convert the datetime datatype and how???
or how can i solve this problem
please i need your help
thanks for all
Kareem Elhosseny
|
|
|
|
|
Convert on DateTime datatype as:
select * from MessageAccepted where AcceptedDateTime between cast('22/10/2007' as DateTime) and Cast('23/10/2007' as DateTime)
I Love T-SQL
|
|
|
|
|
thank's for your effort but i use it before and SQL gives me the same error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Kareem Elhosseny
|
|
|
|
|
Post your data how they are stored in table, and is datatype of column DateTime or varchar?
I Love T-SQL
modified on Sunday, April 20, 2008 4:07 AM
|
|
|
|
|
Dear Blue_Boy
the datatype in my table is Datetime and the data stored as
this format
23/10/2007 01:48:57 PM
Kareem Elhosseny
|
|
|
|
|
this should works for you...
<br />
select convert(varchar(15),AcceptedDateTime,103) from table1 <br />
where convert(varchar(15),AcceptedDateTime,103) >='20/10/2007' and convert(varchar(15),AcceptedDateTime,103)<='21/10/2007'
I Love T-SQL
modified on Sunday, April 20, 2008 7:41 AM
|
|
|
|
|
thank's alot it works but please could u tell me why we don't use Between
& what's the error when we use it & what is the error Causer?
thank's again for your help,time,and your attention
Kareem Elhosseny
|
|
|
|
|
I guess it should works and with between clause.
You are welcome anytime, my pleasure to help you...
select convert(varchar(15),AcceptedDateTime,103) from table1 <br />
where convert(varchar(15),AcceptedDateTime,103) between '20/10/2007' and '21/10/2007'
I Love T-SQL
|
|
|
|
|
thank's God it workes by Between
select * from MessageAccepted where convert(varchar(10),AcceptedDateTime,103) between '22/10/2007'and '23/10/2007'
but i have a Question
if i made the query as above it return 3 record
and when i change in convert method just only the varchar size to 8 as example its return to me 2 record
can u tell me the reasion please
Kareem Elhosseny
|
|
|
|
|
Why you need to change varchar length? Can't be length 15 or you need length 8?
I Love T-SQL
|
|
|
|
|
no i just try but it work without datatype size because its an optional parametr in convert method
here is my account kareem_elhosseny@hotmail.com
you can add me in your contact list
i hope to be friends to exchange experiance
thank's alot my brother
Kareem Elhosseny
|
|
|
|
|
Yes, you can let it without specify size of varchar, but it is more usable if you specify size.
My add is s_voca@hotmail.com
I Love T-SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
