help for a search procedure

hello,
please help me to give me solution
i have a table
CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
CITY VARCHAR2(30), STREET VARCHAR2(40))
MY DATA IS
INSERT INTO T_STUDENT_MT VALUES (1,
'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
INSERT INTO T_STUDENT_MT VALUES (2,
'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
INSERT INTO T_STUDENT_MT VALUES (3,
'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO T_STUDENT_MT VALUES (4,
'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
PLEASE SUGGEST TO WRITE A PROCEDURE FOR
'CONDITION 1: If i pass iNAME to null it should NOT check in where
condition
'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
LIKE iNAME || '%'
CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
AS
BEGIN
--SINGE QUERY TO RETURN STUDENTT DATA
END;
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!kamal hussain wrote:
> hello,
> please help me to give me solution
> i have a table
> CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
> NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
> CITY VARCHAR2(30), STREET VARCHAR2(40))
>
> MY DATA IS
> INSERT INTO T_STUDENT_MT VALUES (1,
> 'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
> INSERT INTO T_STUDENT_MT VALUES (2,
> 'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
> INSERT INTO T_STUDENT_MT VALUES (3,
> 'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
> INSERT INTO T_STUDENT_MT VALUES (4,
> 'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
>
> PLEASE SUGGEST TO WRITE A PROCEDURE FOR
> 'CONDITION 1: If i pass iNAME to null it should NOT check in where
> condition
> 'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
> LIKE iNAME || '%'
>
> CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
> COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
> AS
> BEGIN
> --SINGE QUERY TO RETURN STUDENTT DATA
> END;
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm assuming condition 1 means if the variable @.Name is NULL, then you
want all the rows.
Try this:
CREATE PROCEDURE STUDENTSEARCH(
@.NAME VARCHAR(50) = NULL
)
AS
SELECT [ID]
,[NAME]
,FATHERNAME
,COUNTRY
,STATE
,CITY
,STREET
FROM T_STUDENT_MT
WHERE ((@.NAME IS NOT NULL AND [NAME] = @.NAME)
OR (@.NAME IS NULL))
OR (LEN(@.NAME)=1 AND [NAME] LIKE @.NAME + '%')
Note: In SQL Server there isn't a data type of VARCHAR2 and the
concatenation symbol is the plus sign (+).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQi0xPoechKqOuFEgEQJmOwCg1I3RunB5ASmn
7rtRnj7EZDpoKTkAoMrL
wTkd98HDDTfkao35SpHRNH7S
=WkyP
--END PGP SIGNATURE--

Help - Unusual Problem

First, I can not change the data structure. I have a table called codesubsections that has an identity as the primary key. When a new codesubsection is entered the user can mark a formercodesubsection as a parent. The problem is that I need to be able to evaluate the table and identify the lowest CodeSubsectionID for a given row. so for instant when I look at row 7 it's text is 17 B p2 but because the CodeSubSectionID is not null I need to look at 6 then 5 and since 5 is null I need the text for 5,6,7 all to be 16:1 B. The only solution I have had any kind of luck with is to do a self join 3 times and coalesce the values up. See the code at the end. I am at my wits end, I had it working the other way assuming that the CodeSubSectionID was the parent of the record. This is the final piece of code on a project that is supposed to end tomorrow.

Any help is appreciated. I would normally post the procedures but I think that they are more confusing that the data explanation. This type of relationship is a first for me after 20 some years.

ID CodeSubSectionID Text

1 NULL 16:1 A

2 NULL 16:1 B

3 NULL 17 A

4 NULL 17 B p1

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

8 3 17 C

9 NULL 18

What is the version of SQL Server that you are using? You can use recursive CTEs in SQL Server 2005 to get your results. A generic solution on SQL Server 2000 requires some procedural code but you can expose it via non-inline table-valued function. Please specify your version and expected results. I am not sure how the concatenation is supposed to happen. Does this have to be in specific order for the related items.|||

This piece of code can get the result:

SELECT Y.id1 AS id, Y.minPid, text FROM codesubsections INNER JOIN

(SELECT Z.id1, COALESCE(Z.id4,Z.id3,Z.id2,Z.id1) AS minPid

FROM (SELECT A.id as id1, B.id as id2,C.id as id3, D.id as id4

FROM codesubsections AS A LEFT OUTER JOIN

codesubsections AS B ON B.id = A.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS C ON C.id = B.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS D ON D.id = C.CodeSubsectionID) AS Z) AS Y on Y.minPid=id

--You may keep adding the LEFT OUTER JOIN levels to move up.

|||

Sorry using 2000 and CTE not available.

The second solution would be fine and that is what I had done however, I don't know how deep I have to go beforehand, so I need to test the grandparent and if it has a code section and continue to loop. In order to do that I added an UnProcessed field and this is the loop that I set up and the UDF that I am using. The problem is that when the loop finishes that the bottom item's codesectionid is null and does not get included in the last loop so I never get the 5 text value to update the 6 and 7 value. it only goes to the 6 record. Hope this makes sense.

I really do appreciate the responses. Thanks in advance

DECLARE @.id INT
SET @.id = 1
WHILE @.id = 1

BEGIN
UPDATE CodeSectionsSummary SET
CodeSectionsSummary.UnProcessed = PCSUMMARY.UnProcessed,
CodeSectionsSummary.CodeSubSectionID =PCSUMMARY.CodeSubSectionID,
CodeSectionsSummary.CodeSection = PCSUMMARY.Parent,
CodeSectionsSummary.ParticipantTypeID = PCSUMMARY.ParticipantTypeID,
CodeSectionsSummary.CodeTypeID = PCSUMMARY.CodeTypeID
FROM
CodeSectionsSummary
INNER JOIN PCSUMMARY(0) PCSUMMARY ON CodeSectionsSummary.ID = PCSUMMARY.ID

IF EXISTS (SELECT COUNT(*) FROM CodeSectionsSummary WHERE UnProcessed = 1)
SET @.id = 0
ELSE
CONTINUE
END

Code for the UDF

CREATE FUNCTION dbo.PCSUMMARY
( @.CNT INT)

RETURNS @.RtnValue TABLE
(
[ID] INT ,
CodeSubSectionID INT,
Parent VARCHAR(10),
UnProcessed INT,
ParticipantTypeID INT,
CodeTypeID INT
)
AS
BEGIN
INSERT INTO @.RtnValue
SELECT CodeSectionsSummary.ID,
CodeSubsections_2.FormerCodeSubsectionID AS CodeSubSectionID,
COALESCE (CodeSections_2.CodeSection, CodeSections_1.CodeSection, CodeSections.CodeSection) AS Parent,
CASE TEST.FormerCodeSubsectionID
WHEN ISNULL(TEST.FormerCodeSubsectionID,0) THEN
CASE CodeSubsections_2.FormerCodeSubsectionID
WHEN isnull(CodeSubsections_2.FormerCodeSubsectionID, 0)
THEN 1 ELSE 0 END
ELSE 2
END AS UnProcessed,
COALESCE (CodeSubsections_2.ParticipantTypeID, CodeSubsections_1.ParticipantTypeID, CodeSubsections.ParticipantTypeID) AS ParticipantTypeID,
COALESCE (CodeSections_2.CodeTypeID, CodeSections_1.CodeTypeID, CodeSections.CodeTypeID) AS CodeTypeID

FROM CodeSectionsSummary INNER JOIN
CodeSubsections ON CodeSectionsSummary.ID = CodeSubsections.ID INNER JOIN
CodeSections ON CodeSubsections.CodeSectionID = CodeSections.ID AND
CodeSubsections.CodeSectionID = CodeSections.ID AND CodeSubsections.CodeSectionID = CodeSections.ID LEFT OUTER JOIN
CodeSections CodeSections_2 INNER JOIN
CodeSubsections CodeSubsections_2 ON CodeSections_2.ID = CodeSubsections_2.CodeSectionID LEFT OUTER JOIN
CodeSubsections TEST ON CodeSubsections_2.FormerCodeSubsectionID = TEST.ID RIGHT OUTER JOIN
CodeSubsections CodeSubsections_1 INNER JOIN
CodeSections CodeSections_1 ON CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND
CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND CodeSubsections_1.CodeSectionID = CodeSections_1.ID ON
CodeSubsections_2.ID = CodeSubsections_1.FormerCodeSubsectionID ON
CodeSubsections.FormerCodeSubsectionID = CodeSubsections_1.ID
WHERE (CodeSectionsSummary.CodeSubSectionID IS NOT NULL)
RETURN
END

|||

I once had a situation like yours...

Why don't you do this:

declare @.iSubSection as int,
@.vcText as varchar ( max ),
@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

while not @.iSubSection is null

begin
insert into @.tbTable
select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text
From your_table
where @.iSubSection = id
end

update your_table
set text = @.vcText
from @.table
where iID = ID

|||

Miguelb, i think you are on to a possible solution to this nightmare. Business Analyst that use reverse logic had a special place in hades reserved. I played around with your suggestion. The problem is that in the following records 7 looks at 6, 6 at 6 and 5 never gets evaluated because of the null value. Note, the sample data does not show that the text for 5 will probably be different than 6, sorry about that. I will keep playing with this, most promising approach so far.

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

|||

Hi,

I read a few papers about this topic. I came across this solution by using "String-Based "Nested Sets" technique" from the following link. Here is the link to the resource by Dennis W. Forbes.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies2.htm

I tested your dataset with this solution and it worked pretty good.

|||

Ever since Heracles snatched Cerberus that they let anyone enter in hades, so I wouldn't be surprised to see those people there :)

You are right - I forgot the last leaf. Try this (you may have to tweak it a little more):

declare @.iID as int,

@.iSubSection as int,

@.vcText as varchar ( max ),

@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

set @.iID = null

while not @.iSubSection is null

begin

insert into @.tbTable

select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text

From your_table

where @.iSubSection = id

end

if @.iID is not null

begin

insert into @.tbTable

values ( @.iID )

end

update your_table

set text = @.vcText

from @.table

where iID = ID

Help - deleting existing auto-generated primary key

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

help

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

HELP

I have created one database named test on DServ Server and in that database
I have created three fileGroup named Primary, BaseFileGroup,
TransactionFilegroup and after that I took the backup of that all filegroup
and restore it into another server. (OServ). After a week I took only the
backup of TransactionFileGroup from DServ and want to restore it on Oserv
server but Failed to do that. can any body tell me that is it possible that
I only restore
specific filegroup rather to restore all the filegroup ?
Thanks
Noor
What error message do you get?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

help

hi i need some help.I tried using alter table tablename <modify columnname datatype> to change the primary key of the table but it didnt work.I was prompted that there is an error because of using the term modify.what is the command that i should use?

Quote:

Originally Posted by izzo

hi i need some help.I tried using alter table tablename <modify columnname datatype> to change the primary key of the table but it didnt work.I was prompted that there is an error because of using the term modify.what is the command that i should use?

Hi
I don't think you can modify the primary key. You need to delete it and then create a new one on a different column|||first drop the primary key constraint
alter table tablename drop pk_constraint
then add a new constraint to whatever column you want to add it on to, making sure that the values in the column obliges the rules of primary key.
alter table tablename add constraint pk_col1 primary key(col1)

hope this one helps u