Help optmizing a stored proc

I have this

CREATE PROCEDURE dbo.cmsGetTaskOrdersAndFunding2
(
@.FundingDate SMALLDATETIME,
@.BillingContractID INT, -- null for all contracts
@.Filter BIT = NULL

)
AS
-- get list of taskorders with their respective fundingtotals as of
specified date
IF @.Filter IS NULL
BEGIN
SELECT TO1.TaskOrderID
FROM TaskOrder TO1
LEFT OUTER JOIN
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
END
ELSE
BEGIN
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID) AND TO1.Retired <> @.Filter
END

RETURN
GO
------
Is there a less redundant way to write this? basically @.Filter is an
optional parameter, if it isn't present, I want to return all records and if
it is present all records where Retired <> @.Filter. Any ideas? Can I wrap
the WHERE clause in an if statement? Or is there a better way?

TIA,
ChrisJust one block:

-- get list of taskorders with their respective fundingtotals as of
specified date
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)

Note this last line. If @.Filter is NULL, the entire block is ALWAYS
true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
null, "TO1.Retired <> @.Filter" is the part that matters.|||Sweet! Thanks man.

"figital" <mharen@.gmail.com> wrote in message
news:1141927222.299970.274010@.i39g2000cwa.googlegr oups.com...
> Just one block:
> -- get list of taskorders with their respective fundingtotals as of
> specified date
> SELECT TO1.TaskOrderID,
> FROM TaskOrder TO1
> WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
> @.BillingContractID)
> AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)
> Note this last line. If @.Filter is NULL, the entire block is ALWAYS
> true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
> null, "TO1.Retired <> @.Filter" is the part that matters.

Help on SQL Injection...

Hi All:
I can't seem to get this thing work... When I type this in a textbox :'; exec master.dbo.sp_addsrvrolemember 'redice','sysadmin' -- , there's no respond, I mean, I check redice's role, but the System Administrators is not checked.
Any idea about this?
Thanks in advance.
Can you post your button click code or the code that executes the TextBox string?

|||Hi azamsharp:
This is my code:

Dim userNameAsString = txtUserName.Text

Dim pwdAsString = txtPwd.Text

Dim strSQLAsString = "select * from testing where userName = '" & userName & "' and pwd = '" & pwd & "'"

Dim comAsNew SqlCommand(strSQL, con)

Dim readerAs SqlDataReader

If pwd = ""Or userName = ""Then

lblMsg.Text = "Please enter user name and password."

Else

Try

con.Open()

reader = com.ExecuteReader

If reader.HasRowsThen

lblMsg.Text = "welcome"

Else

lblMsg.Text = "You are not authorized to enter this page."

EndIf

con.Close()

Catch exAs SqlException

lblMsg.Text = ex.Message

EndTry

EndIf
Thanks.

|||Guys, are you aware of how dangerous this can be? You are executing commands from web applications and that might cause you a lot of problems. I recommend you use parameters in your code while trying to access the SQL Server, this way everything entered in the textboxes would be treated as strings.
check this article please:UPDATE: Please, please, please, learn about injection attacks!
ergards
Regards|||First of all I have to agree with Bilal about SQL Injections. Don't runquery based on the parameters comming from the TextBoxes. Second yourcode looks fine to me, does it throw any exceptions. Are you authorizedto even run the select command on "testing" table. You can right clickthe table and give the users permission to run appropriate commands.Second It seems that all this code is written under "Button Click"event. If that is the case please keep all data access code in aseparate class library. Having code in Button Click means that you aremixing BLL with Presentation Layer.
Try to use stored procedures they are much faster than those queriesthat you are writing. Also check out the article below this may belittle advance for you but its always good to adopt good practices fromstart.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/CustEntCls.asp
|||

azamsharp wrote:

Try to use stored procedures they are much faster than those queries that you are writing.

I can prove that you are wrong over here. Stored procedures are just as fast in comparision to parameterized queries. There is, however, an exception to this finding under very certain circumstances but it is very rare that a stored procedure will outperform a parameterized SQL query by at least 5%. I'm just going to save me the time of what Frans Boumahas already done.

azamsharp wrote:

Don't run query based on the parameters comming from the TextBoxes.

You should parameterize anything that goes to your database. Instead of doing the bare minimum to protect from user input, you should do the full monty of parameterizing anything and everything you can. The reason is simply that you have not covered the full path of the exploit - you assume the most likely and yet are unable to cover the least expected. The truth of the matter is that is how exploits are found -- through the least expected resource.
If you trust drop down lists as being "secure," you better think twice. There is nothing stopping me from writing a HTTP request with a well crafted response of where your drop down lists is supposed to look like becoming my "text boxes." Another possible exploit is through querystrings. May I continue?
However, if you read the initial post clearly, it is not asking how to prevent SQL injections. It is merely trying to understand how SQL injections occur so value can be placed on how easy the exploit is. So it is not a matter of writing a well tiered application; it is just a demo to try understand the exploint.|||

redice wrote:

Hi All:
I can't seem to get this thing work... When I type this in a textbox :'; exec master.dbo.sp_addsrvrolemember 'redice','sysadmin' -- , there's no respond, I mean, I check redice's role, but the System Administrators is not checked.

To just put a simple example, here is some code:
command.CommandText = "select * from products where id = " + this.Page.QueryString["Id"];
As you guessed it, the above just gets the product out of the database based upon the ID passed in the querystring. Now the normal SQL that will yield for a URL like "View.aspx?Id=1" should be as follows:
select * from products where id = 1
Now let's assume we using the following URL:
View.aspx?Id=2; drop table products
Our favourite browser, Internet Explorer will automactially encode the URL for a proper URL request. The "SQL statement" that will be yielded is:
Select * from products where id = 2; drop table products
Which is actually two SQL statements! One being the "read" and the second part being the dropping of the table. Now is seems like that this was an easy attack. Let's try another ad-hoc SQL statement.
command.CommandText = "select * from products where productname = '%" + this.Page.Request.Querystring["SearchFor"] + "' order by price";
The first part of the SQL injection will be to insert the following into the builder:
'; select * from sysobjects where xtype = 'U
Now that will select all user objects from the database. And if you happen to display the exception information to the world, you just make the exploiters' lives easier because of obvious reasons. There may be other things that the exploiters' may use to find out more about your database... but if I tell you, I would have to kill you. However, there is your simple demo on how the SQL injections can occur.|||Hi all:
Okay, I know I should've said this. I was asked to do some experiments of how an attacker can use SQL Injection to attack the application. I KNOW how dangerous SQL Injection is. My bad, I guess, I should've mentioned to all of you that the purpose of this post is to find out how an attacker can attack... I was supposed to do a demo to my boss... Man~~~~ my bad, my bad...
Well, the user 'redice' has no permission at all, thus, this attacker (redice) is trying to grant himself a permissionsysadmin.
Anyone, please help.
Thanks.|||

redice wrote:

I should've mentioned to all of you that the purpose of this post is to find out how an attacker can attack... I was supposed to do a demo to my boss...

It is very good that you are cognisant of the risk of SQL injection attacks, and that you are trying to guard against them. You and your boss can pat each other on the back for your proactivity.
However, the above statement is a very risky approach to security. You should never presume to know how someone might attack your website. Unless you yourself are a very advanced hacker, then there is simplyno way to know how someone might attack your website. SQL injection attacks are only one type attack.

As Justin intimated, a hacker will not attack your website through your nicely crafted webpage. A hacker can use a spoofing program that allows him to create a complete HTTP request, adding any malicious form values, querystring values, and cookie values that he cares to add.If he's clever enough, and you haven't guarded against it, he can tamper with viewstate values to trigger a TextChanged event. [EDIT: By default, ViewState is tamper-proof.] The point is that you cannot "find out how an attacker can attack." You can learn some of the ways, and that's good, but it is the wrong approach to security in general.
What you need to do is assume that you simply don't know anything about how attackers do their thing, and instead follow well-documented best practices when designing your website. That is, you focus on your own defense, not on the attacker's offense.
Recently I conducted an audit of a website. I typed "goat's beard" into the search textbox, because that is the name of a flower, and is thus a legitimate search term. The apostrophe generated a server-side error that was echoed back onto the website. Sensing a vulnerability, I contacted a white-hat hacker. She quickly showed that the site was very vulnerable to SQL injection attacks.
I contacted the website's developer. Having been given this "demo"--precisely the type that you are creating for your boss--the website developer then updated his code to reject any search terms with an apostrophe. I pointed out that not only was that worthless, it means my dear old grandma cannot search for her favourite flower. In response, rather than reject such terms, he stripped out apostrophes from any query that went to the database. That too was worthless. This went on for a long time, as after fixing this single search textbox, he then had to do the same thing with the contact form's textboxes. The querystrings then proved to be vulnerable too.He was coding towards a demo of specific attacks, rather than coding a generalised defense.
This white-hat hacker was actually able to craft a querystring that permanently corrected a spelling mistake on the website. Had she been a black-hat hacker, who knows what she might have done.
The point of this story is that if you have a "demo", there is a real risk that you will code toknown attack types, updating your code until the demo suggests that your website is secure. The proper approach is to assume, quite correctly, that you simply cannot know all risks and all types of attack. This approach properly moves the focus to the defense, and not to the attacks.
Sheesh, another long reply. Perhaps my MVP award should read, "Most Verbose Person."

|||Hello fellows experts:
First of all, I wanna thank all of you for your inputs. It sure helps me to understand more bout SQL Injection. Ummm haha and after the miscommunication, SomeNewKid comes to my rescue. Thanks a lot. You've explained to me so detailed and yeah, you are so right about focusing the defense than the attack. Hope my demo will do fine.
Thank you to haidar_bilal, azamsharp, master4eva, and lastly, SomeNewKid! Thanks for sharing your thoughts.
CHEERS!
|||

Question to "Most Verbose Person",
Is it possible to have some sort of a Blog entry or even an article, things that developers should take care of while developing website against attacks? For example, one of those threats are SQL Injection, Cross-Page scripting and others too. Explain each attack, and what is the proposed solution.
And comment on the ' stuff, parametrizing queries, doesn't overcome that problem of using ' ? just a question.
Regards

|||

haidar_bilal wrote:

Is it possible to have some sort of a Blog entry or even an article, things that developers should take care of while developing website against attacks?

The problem here is that there is not a single determinable setup that is entirely secure. As much as we may wish it were this simple, there cannot be some sort of checklist of attack types, and the defenses against them.
One of the greatest weaknesses in security is a developer whothinks his website is secure. A developer might follow an article of the type you suggest, implement its recommendations, and then consider the job is done. Hackers find new ways of attacking targets, and not all attacks are through software. There are social and physical aspects to hacking, too.
Even more telling, not all vulnerabilities are the domain of attackers. We developers can do dumb things and corrupt our own websites. Website owners or content providers may misuse their tools and inadvertently cause damage. Even Google has corrupted websites! (This occurs when the option to "delete this item" is a hyperlink rather than a true button, and Google happily follows that link as it indexes the site.)
All of this is compounded by the fact that websites grow and evolve, and so too do the technologies on which websites operate.
There are of course resources that talk about security. A pertinent one isBuilding Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication. This is a 600 page document of whichonly 3 pages deal with SQL injection attacks! You will see then that security is a mammoth topic that could never be covered in an article or a blog.
The point of my rambling is that security is such a huge topic that we must choose what it is that we focus on. The wrong thing to focus on is trying to learn all types of attacks and, one by one, address them. (Who'd have listed Google as an attacker?) The right thing to do is focus on the integrity of your code and the environment in which it executes.

Help needed with trigger

Hi All,

I have this trigger wich runs good!

CREATE trigger trUpdateGEOData
on dbo.BK_Machine
after insert, update
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber >= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID

Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.

Does someone have an Idea?

Cheers WimLOC_Street = case when insrted.PostalCode is Null or
inserted.DoorNumber is Null then '' else GEO_Postcode.STraatID end

Change your UPDATE Statment to include this. Do something similar with Loc_City.

Help needed with Simple Stored Procedure

Hey all,

Can anyone tell me why this stored procedure doesn't work?

ALTER

PROCEDURE [dbo].[RemoveLocation]

@.Id

int,

@.Name

varchar

AS

DELETE

FROM Classifieds_Terminals

WHERE

[Id]= @.Idand [Name]= @.Name

I try exeuting it like this:

USE

[CLASSIFIEDSDB2.MDF]

GO

DECLARE

@.return_valueint

EXEC

@.return_value= [dbo].[RemoveLocation]

@.Id

= 18,

@.Name

= N'Terminal A'

SELECT

'Return Value'= @.return_value

GO

It returns 0 and nothing happens...?

Here is the table:

id | Name

18 Terminal A
18 Terminal B
18 Terminal C

Hi,

i have modified your Stored Procedure, now it should working as expected:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[RemoveLocation] @.Id int,@.Name varchar(100)
AS

DELETE FROM Classifieds_Terminals WHERE [Id] = @.Id and [Name] = @.Name

RETURN (SELECT @.@.ROWCOUNT)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards
Marc André

|||

Thanks,

I also added a autoincrement field and used that id to delete the entry.

Help needed with DATETIME Arithmetic

Hi,

I have the following query:

SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,
dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.
Product,
dbo._Fault.Problem_Description,
dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,
dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,
dbo._Fault.Duration_date_summary,
dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,
dbo._Fault.Serial_No,
dbo._Product.id AS PRODUCTID, dbo._Product.description
FROM dbo._Fault LEFT OUTER JOIN
dbo._Product ON dbo._Fault. Product = dbo._Product.id

I need an additional field appended to the end of this (called
'DATECOMPLETED') that will take the 'Duration_start_date' and add the
'Duration_duration' to it - effectively giving me a new datetime field that
is newer than the start date.

I have tried unseccessfully to amend the code but I am afraid my knowledge
of SQL is limited. I would greatly appreciate if someone could show me how
to do this.

Thanks,

DarrenMintyman wrote:

Quote:

Originally Posted by

Hi,
>
I have the following query:
>
SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,
dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.
Product,
dbo._Fault.Problem_Description,
dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,
dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,
dbo._Fault.Duration_date_summary,
dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,
dbo._Fault.Serial_No,
dbo._Product.id AS PRODUCTID, dbo._Product.description
FROM dbo._Fault LEFT OUTER JOIN
dbo._Product ON dbo._Fault. Product = dbo._Product.id
>
I need an additional field appended to the end of this (called
'DATECOMPLETED') that will take the 'Duration_start_date' and add the
'Duration_duration' to it - effectively giving me a new datetime field that
is newer than the start date.
>
I have tried unseccessfully to amend the code but I am afraid my knowledge
of SQL is limited. I would greatly appreciate if someone could show me how
to do this.
>
Thanks,
>
Darren

If Duration_duration is a number and you want it to be the number of
days added to Duration_start_date you would add something like this to
the select list:

dateadd(dd, Duration_duration, Duration_start__date)|||Thanks for the reply. That seems to have done the trick :o)

"ZeldorBlat" <zeldorblat@.gmail.comwrote in message
news:1158695486.220267.171700@.m73g2000cwd.googlegr oups.com...

Quote:

Originally Posted by

>
Mintyman wrote:

Quote:

Originally Posted by

>Hi,
>>
>I have the following query:
>>
>SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,
>dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.
>Product,
> dbo._Fault.Problem_Description,
>dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,
>dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,
> dbo._Fault.Duration_date_summary,
>dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,
>dbo._Fault.Serial_No,
> dbo._Product.id AS PRODUCTID,
>dbo._Product.description
>FROM dbo._Fault LEFT OUTER JOIN
> dbo._Product ON dbo._Fault. Product =
>dbo._Product.id
>>
>I need an additional field appended to the end of this (called
>'DATECOMPLETED') that will take the 'Duration_start_date' and add the
>'Duration_duration' to it - effectively giving me a new datetime field
>that
>is newer than the start date.
>>
>I have tried unseccessfully to amend the code but I am afraid my
>knowledge
>of SQL is limited. I would greatly appreciate if someone could show me
>how
>to do this.
>>
>Thanks,
>>
>Darren

>
If Duration_duration is a number and you want it to be the number of
days added to Duration_start_date you would add something like this to
the select list:
>
dateadd(dd, Duration_duration, Duration_start__date)
>

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>

help me out for storing more data

CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO

using this script i have created my blog table.

and a procedure given below. i am using to insert data in it.

CREATE PROCEDURE SP_BlogAdd

@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)

AS

INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO

now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).

i want more than this size to store data in it.

please give me some detailed code.

i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.

Please help me out.

regards,
ASIFhttp://www.dbforums.com/showthread.php?t=1605270

help me out for storing more data

CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO

using this script i have created my blog table.

and a procedure given below. i am using to insert data in it.

CREATE PROCEDURE SP_BlogAdd

@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)

AS

INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO

now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).

i want more than this size to store data in it.

please give me some detailed code.

i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.

Please help me out.

regards,
ASIF
if you are on SQL Server 2005, you can use the VARCHAR(MAX) Type, which will allow you to store up to 2GB of data and use all string and aggregation operations on it, other like the TEXT datatype. Anyway, if you are on SQL Server 200 you have to keep in mind, that you cannot store more than 8096 in one row, therefore you can′t create the table above without having pro′blems while inserting more than 8046 bytes of data. Most people either scaled tables out for this cases in order to split up the data to more than one table referencing them with a 1:1 relation OR, like in your cases used the TEXT type for the columns. You can′t modify the length column, because the length of the TEXT column is 2GB having a pointer stored in the tables with the size of 16Bytes. So anything you put in, can be up to the size of 2GB.