|
You can also use ELSE as well
<br />
create proc myProc @Account int AS<br />
<br />
-- Select ALL RECORDS<br />
if @Account = 0 <br />
BEGIN<br />
SELECT * FROM Accounts<br />
END<br />
ELSE<br />
-- SELECT SINGLE ACCOUNT<br />
BEGIN<br />
SELECT * FROM Account<br />
WHERE ID = @Account<br />
END<br />
<br />
Hope tihs helps
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Here I use Access to build up a database with a time/date field.
How can I query the database according to the time/date field, that is
SELECT * FROM [DATABASE] WHERE TIMEDATE < 20:20? Can time/date field be operated with > = and < ?Thanks
|
|
|
|
|
Select from [database.table] where [table.field] < '20;20 00:00'
Got the idea
Glen Harvy
|
|
|
|
|
Sorry, not very clear.Factually I have two question to ask:
1. how to input the date-time format: "1987-12-29 12:23:45" into Access DB with InputMask?
2. if I want to query the record that has the date-time before "1987-12-29 12:23:45" and after "1986-10-15 23:25:26", how do I write the sql string?Thanks
|
|
|
|
|
zouchao1112 wrote: 1. how to input the date-time format: "1987-12-29 12:23:45" into Access DB with InputMask?
UPDATE [tablename] SET [datetimeField] = "1987-12-29 12:23:45" WHERE [record.ID] = [record.needing.update.ID]
zouchao1112 wrote: if I want to query the record that has the date-time before "1987-12-29 12:23:45" and after "1986-10-15 23:25:26", how do I write the sql string?
SELECT [record.id] FROM [tablename] WHERE [record.datetimeField] < "1987-12-29 12:23:45" AND [record.datetimeField] > "1986-10-15 23:25:26".
Glen Harvy
-- modified at 17:54 Friday 7th July, 2006
|
|
|
|
|
So you mean the format "1987-12-29 12:23:45" can be recognized as DATE automatically?Thanks very much
|
|
|
|
|
Hello,
I'm using SQL Server 2005. I have a column named "DATA" with ntext datatype that stores xml data which contains these tags:
<message>Text Free Meal
<multipleaction>
<caction>
<message>Thank you for participating. Just present this [MCCode] to claim your free meal.
How do I use sql select statement to access <message> and return the string
"Thank you for participating..." as a result?
Thank you. :->
|
|
|
|
|
|
I'm having a couple of problems with SQL Queries. I am running a Query
SELECT * from [DATABASE] WHERE DES='My' And i get this error: The data types text and varchar are incompatible in the equal to operator.
What data type Should i set my column to so i can make it searchable?
Don't be overcome by evil, but overcome evil with good
-- modified at 20:23 Thursday 6th July, 2006
|
|
|
|
|
Nevermind i got it. i changed it to varchar(max) and it worked fine.
Don't be overcome by evil, but overcome evil with good
|
|
|
|
|
Hi,
I have 2 tables tbl_Member and tbl_Community, they are related by primary/foreign keys.
I access them using SPs. So I created TableAdapters for SPs which return rows from these tables (only). It generated typed MemberRow and ComunityRow for me.
Now I have created SP which joins these 2 tables and returns me all columns from tbl_Member and one from tbl_Community.
I cannot add this SP as a query to MemberTableAdapter which I use to get records from tbl_Membe because this new field (from tbl_Community) does not fit into the existing XML schema.
How can I use this SP ? I would probably need more queries which return columns from joined tables.
How do you "typify" such queries ? It seems like the same problem would have arised if I created custom business objects and not used typed DataSets, I could have Member and Community objects but not something in between.
Thanks a lot,
Slava
|
|
|
|
|
Hi,
How to find the number of databases in Oracle database?
Regards,
Uma
|
|
|
|
|
What are you trying to do?
|
|
|
|
|
simply i created the following stored procedure and it gives me the following error:
Server: Msg 217, Level 16, State 1, Procedure SelectRecipient, Line 6
Maximum stored procedure, function, trigger, or view nesting level exceeded
the code is te following:
CREATE PROCEDURE SelectRecipient
AS
DECLARE @mail nvarchar(4000)
Select Mail FROM Member
select Mail from Member
exec @mail = SelectRecipient
select @mail
GO
please i need to know how to resolve the problem and how to retrieve the @mail value in my C# application
karim kamal
|
|
|
|
|
|
please help by sending the correct syntax for this procedure i tried to set an exit condition in many ways but in vain
|
|
|
|
|
Your code:
CREATE PROCEDURE SelectRecipient
AS
DECLARE @mail nvarchar(4000)
Select Mail FROM Member
select Mail from Member
exec @mail = SelectRecipient
select @mail
GO
I'm not sure what you are trying to do. It looks like you only want one value, but you are not passing in any parameters so there is nothing to filter the result set.
Suggested change:
CREATE PROCEDURE SelectRecipient
AS
SELECT Mail from Member
GO
From C#
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandTypeStoredProcedure;
cmd.CommandText = "SelectRecipient";
cmd.Connection = myConnection;
SqlReader reader = cmd.ExecuteReader()
while(reader.Read())
{
string mail = reader.GetString(0);
}
The above will retrieve all mail in the table. (Which I don't think is what you want - but it is the best I can help you with given the information you have supplied)
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
Dear Colin, really i appreciate ur effort with me ,this solution in fact exactly is what i want to do , my fault which i deiscovered lately is i'm trying to send multiple values with one parameter,u r code works with me very well now, with all my thanks, bless u
karim kamal ( developers are dime a dozen but good developers are weighted by GOLD)
|
|
|
|
|
This what you are trying to do
CREATE PROCEDURE SelectRecipient
@Recipient varchar(40)
AS
DECLARE @mail nvarchar(4000)
SET @mail = ( Select Mail FROM Member
Where Member = @Recipient )
select @mail
GO
run this as
exec SelectRecipient 'fkerrigan'
will produce
frank@nospam.com
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
Yes you are quite correct (as always). But I was trying to stick to the orginal code and theme.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
About 5 months ago when I was designing a database for a client, a co-worker wrote a number of CLR stored procedures that augment functionality of my database. The database was delivered to the client, and we have since found out that one of the stored procedures has a habit of throwing a DivideByZero exception in ceratin cases. I have fixed the code and redeployed it locally, but I'm having problems understanding how I get it to the client when I don't have direct deployment access to their SQL server. Do I need to copy a .dll and manually move it to the client, or is there a way I need to set things up(even if only temporarily) so that I can deploy the new CLR code to the remove server?
Thanks in advance.
-- modified at 14:14 Wednesday 5th July, 2006
Forgot to mention, I've generated scripts using these instructions: http://msdn2.microsoft.com/en-us/library/ms345099.aspx[^], but they refuse to let me drop and then add the CLR's (as I do with other items). I'm using all of the defaults in the wizard, is there something I can check to remove the constraint check briefly so I can remove something and add a new version in its place?
Alternately, can I alter the statement that is being generated?
ALTER ASSEMBLY [Assembly Name]
ADD FILE FROM (huge hex code)
AS N'filename.cs'
Should the Add be modify?
|
|
|
|
|
Figured out my issue. Since I had a number of stored procedures and functions, I needed to perform a massive drop and add (drop sp, drop fun, drop assem, add assem, add fun, add sp), but that appears to have successfully transfered them over. The msdn link above is really helpful for generating those files, but I think they probably need to include a paragraph or so about dealing with dependencies.
|
|
|
|
|
Hi,
I have a table like this:
Param Cat Payee Amt1 Amt2
AS x1 y1 23 34
AS x1 y2 43 53
BJ x1 y1 53 33
I want to select row 1 and row 3.
For just row1, I write,
“SELECT * FROM Tab1 WHERE Param=’AS’ AND Cat=’x1’ AND Payee=’y1’”
But how do write the sql if I want both the 1st row and the 3rd row. They both differ in only the Param name.
Please can you tell me.
Thanks,
Tara
Fortitudine Vincimus!
|
|
|
|
|