substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

substring

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-05-27 : 13:27:39

I have a form that displays all allowed languages(together with respective IDs). The user can uncheck the ones that they don't want to use. I get a list of languageID's that user want to use. I want to add records to a table which contains all language combinations that are not allowed.
How to go about it.

Thanks

Sharjeel

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-27 : 19:16:19

Sharjeel

Q.what defines whether language combinations are allowed?

Post some code -

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

php95saj
Starting Member

43 Posts

Posted - 2002-05-28 : 03:06:57

A user has access to all languages by default. All I need to do is to allow user to tell that they don't want to use certain languages. So they check thoes boxes in the form and submit. I now have all the languages that they want, all avaiable languages. All I want to do now is to subtract user's preferred languages from all available languages to get the languages that they don't want. When the user submits form I get
1. language ID's(that they want to use) in a comma separated list.

(
@languageID nvarchar(1000)='',
@separator char(1)=','
)
2. In the stored procedure I call another stored procedure to get all available languageID's and popoulate a temporary table.

create table #tempLanguages (languageDirectionID int)

insert into #tempLanguages
(languageDirectionID)
select distinct tbl_LanguageDirections.languageDirectionID
from tbl_Locale_Languages
inner join tbl_Languages on tbl_Locale_Languages.languageID = tbl_Languages.languageID
inner join tbl_LanguageDirections on tbl_Languages.languageID = tbl_LanguageDirections.targetLanguageID
where tbl_LanguageDirections.serviceLevelID = @serviceLevelID
and tbl_LanguageDirections.sourceLanguageID = @sourceLanguageID
and tbl_LanguageDirections.active = 1
order by tbl_Locale_Languages.languageDesc

3. Now I want to run a loop on the languageID list passed by the form submitted by the user and delete languageID's from the temporary table that are in the list.
??????(this is where I am stuck)

4. I will be left with the languageID's that the user doesn't want.

Thanks.

Sharjeel

What defines language combinations are allowed is that

php95saj
Starting Member

43 Posts

Posted - 2002-05-28 : 11:33:25

Just for information, I got it around.

CREATE PROCEDURE [usp_accountProfile]

(
@serviceLevelID tinyint=3,
@sourceLanguageID int=1,
@loginID int=0,
@languageDirectionID nvarchar(1000)='',
@separator char(1)=','
)

as

set nocount on
if (select loginLevel from tblAccountLogins where loginID = @loginID) != 1
begin

-- create a temporary table
create table #tempLanguages (languageDirectionID int)

declare @iPosStart int,
@iPosEnd int,
@iLenDelim int,
@iExit int
-- run a loop over the passed list
set @iPosStart = 1
set @iPosEnd = 1
set @iLenDelim = len(@separator)
declare @ID int

set @iExit = 0

while @iExit = 0
begin

set @iPosEnd = charindex(@separator, @languageDirectionID, @iPosStart)

if @iPosEnd <= 0
begin
set @iPosEnd = len(@languageDirectionID) + 1
set @iExit = 1
end
-- extract languageDirectuionID from the list
set @ID = convert(int,substring(@languageDirectionID,@iPosStart,@iPosEnd - @iPosStart))
set @iPosStart = @iPosEnd +1

-- insert @id into the temporary table
insert into #tempLanguages
(languageDirectionID)
values
(@ID)

end
-- create a temporary table
create table #tempSourceLanguages (languageID int)

-- store all languageDirectionID's that are in tbl_LanguageDirections for the selected sourceLanguage
insert into #tempSourceLanguages
(languageID)
select languageDirectionID from tbl_LanguageDirections where sourceLanguageID = @sourceLanguageID

-- delete records from tblLanguageDirectionRestrictions, if the languageDirectionID is in temporary table
delete from tblLanguageDirectionRestrictions
where loginID = @loginID
and languageDirectionID IN (select * from #tempSourceLanguages)
-- This will ensure that we don't have duplicate entries in the table for that loginID and sourceLanguageID
-- Now it is safe to add all the languageDirectionID's that the user doesn't want.

-- select all languageDirectionID's from which are not present in the temporary table
insert into tblLanguageDirectionRestrictions
(loginID, languageDirectionID)
select distinct @loginID, tbl_LanguageDirections.languageDirectionID
from tbl_Locale_Languages
inner join tbl_Languages on tbl_Locale_Languages.languageID = tbl_Languages.languageID
inner join tbl_LanguageDirections on tbl_Languages.languageID = tbl_LanguageDirections.targetLanguageID
where tbl_LanguageDirections.sourceLanguageID = @sourceLanguageID
and tbl_LanguageDirections.active = 1
and tbl_LanguageDirections.languageDirectionID NOT IN (select * from #tempLanguages)
order by tbl_LanguageDirections.languageDirectionID

end
set nocount off

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources