help me for a small query in sql server 2000

[zathrone]

I am having a problem making a SQL query .. plz help me sort it out . It is drafted in the attachment.

With lots of thanks

[rmiao]

T-sql doesn't support array and you can't use table variable in sp, but you can save value of taxtboxes to temp table and let your sp get them from that temp table. In your sp, you can either build dynamic sql to get all rows for all textboxes or use cursor to get rows for each textbox.

[zathrone]

T-sql doesn't support array and you can't use table variable in sp, but you can save value of taxtboxes to temp table and let your sp get them from that temp table. In your sp, you can either build dynamic sql to get all rows for all textboxes or use cursor to get rows for each textbox.

Thanks for your reply!
can u plz provide me with an example of it , i mean the one u have mentioned?

[If any other ideas, plz post!)

[mikr0s]

--modify the procedure according to your database table
--exec records_per_person @break_str = '^', @person_str = 'John^Nick^George^James'

CREATE PROCEDURE records_per_person
@break_str VARCHAR(2) = NULL,
@person_str NVARCHAR(1000) = NULL --in form of name1[breaking character]name2[breaking character]
AS

IF @break_str IS NULL
RETURN

DECLARE @runner SMALLINT

--table variable holding input names
DECLARE @mynames TABLE (the_name NVARCHAR(100) NULL)

--add trailing breaking string/s
SELECT @person_str = @person_str + @break_str
WHERE RIGHT(@person_str, LEN(@break_str)) <> @break_str

--run through input names
SELECT @runner = 1

WHILE @runner <= LEN(@person_str)
BEGIN
--add name to table variable
INSERT INTO @mynames (the_name)
VALUES (SUBSTRING(@person_str, @runner,CHARINDEX(@break_str, @person_str, @runner+1)-@runner) )

--move to next breaking string
SELECT @runner = CHARINDEX(@break_str, @person_str, @runner+1) + 1
END

SELECT my.the_name AS person_name, COUNT(tbl.person_name) AS num_of_records
FROM @mynames my
LEFT JOIN db_table tbl ON tbl.person_name = my.the_name
GROUP BY my.the_name

[zathrone]

--modify the procedure according to your database table
--exec records_per_person @break_str = '^', @person_str = 'John^Nick^George^James'

CREATE PROCEDURE records_per_person
@break_str VARCHAR(2) = NULL,
@person_str NVARCHAR(1000) = NULL --in form of name1[breaking character]name2[breaking character]
AS

IF @break_str IS NULL
RETURN

DECLARE @runner SMALLINT

--table variable holding input names
DECLARE @mynames TABLE (the_name NVARCHAR(100) NULL)

--add trailing breaking string/s
SELECT @person_str = @person_str + @break_str
WHERE RIGHT(@person_str, LEN(@break_str)) <> @break_str

--run through input names
SELECT @runner = 1

WHILE @runner <= LEN(@person_str)
BEGIN
--add name to table variable
INSERT INTO @mynames (the_name)
VALUES (SUBSTRING(@person_str, @runner,CHARINDEX(@break_str, @person_str, @runner+1)-@runner) )

--move to next breaking string
SELECT @runner = CHARINDEX(@break_str, @person_str, @runner+1) + 1
END

SELECT my.the_name AS person_name, COUNT(tbl.person_name) AS num_of_records
FROM @mynames my
LEFT JOIN db_table tbl ON tbl.person_name = my.the_name
GROUP BY my.the_name

Thanks a lot for ur nice effort ! I really appreciate ur reply.

[zathrone]

[



Thanks For The Reply


]