problem getting the decoded string value from db

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)

problem getting the decoded string value from db

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-13 : 00:10:49

Hello all,

let me first write down the tables that I have. I am using SQL Server 7.0.


if exists (select * from sysobjects where id = object_id(N'[dbo].[Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Order]
GO

CREATE TABLE [dbo].[Order] (
	[OrderID] [varchar] (5) NOT NULL ,
	[CustomerID] [varchar] (5) NOT NULL ,
	[OrderDate] [varchar] (8) NOT NULL ,
	[bitmapTest] [int] NOT NULL 
)
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[codeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[codeTable]
GO

CREATE TABLE [dbo].[codeTable] (
	[bitmapID] [int] NOT NULL ,
	[description] [varchar] (50) NOT NULL 
) ON [PRIMARY]
GO

I have following data in both tables.


use cascadeTest
go

insert into [dbo].[codeTable] (bitmapID,description)
	select 1, 'A' UNION all
	select 2, 'B' UNION all
	select 4, 'C' UNION all
	select 8, 'D'

insert into [dbo].[Order] (OrderID,CustomerID,OrderDate,bitmapTest)
	select 1, 'BILBO', '19/04/99', 12 union ALL
	SELECT 2, 'FRODO', '18/05/99', 9 UNION ALL
	SELECT 3, 'GNDLF', '17/04/99', 5

Now, the bitmapTest field in dbo.Order table is a number that shows the combination of string value. This combination can be generated by looking up the table dbo.codeTable and perform bitwise AND operation with it. So, its a bitmap implementation.

So, in dbo.Order table, first entry has bitmapTest value = 12. That means it is a combination of 'C' and 'D' from dbo.codeTable. So, I am writing a stored procedure that takes an integer as its argument and should return the string value with the combination mentioned above as comma separated string.

So, I am creating a stored procedure named 'sp_dynamicDecode' which takes an integer argument. When I execute that procedure like this:

exec sp_dynamicDecode 12

It should give me output as
'C,D'.

The stored procedure code is mentioned below:


use cascadeTest
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_dynamicDecode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_dynamicDecode]
GO

create procedure sp_dynamicDecode
(
	@codeNo int
)
AS

set nocount on

declare @outputString varchar(50)
declare @counter int
declare @rowcounter int
declare @tempCodeNo_local int
declare @tempString_local varchar(20)
declare @tempOutput varchar(50)
declare @queryOne nvarchar(1000)
declare @queryTwo nvarchar(1000)

set @counter = (select count(*) from [dbo].[codeTable])
set @rowcounter = 0
set @outputString = ''

while(@counter > 0)
begin

	set @queryOne = 'select top 1 bitmapID from [dbo].[codeTable] 
			where bitmapID not in (select top '+ cast(@rowcounter as nvarchar) +' bitmapID from [dbo].[codeTable] order by bitmapID) order by bitmapID'

        execute sp_executesql @tempCodeNo_local =  @queryOne

	print @tempCodeNo_local

	set @queryTwo = '(select description from [dbo].[codeTable]
		 	where bitmapID = ' + cast(@tempCodeNo_local as nvarchar)

	execute @tempString_local = sp_executesql @queryTwo

	set @tempOutput = ', ' + @tempString_local

	print @tempOutput

	if (@codeNo & @tempCodeNo_local) = @tempCodeNo_local
		set @outputString = @outputString + @tempOutput

	set @counter = @counter - 1
	set @rowcounter = @rowcounter + 1
end

set @outputString = right(@outputString, len(@outputString)-2)

select @outputString

This procedure does not work and gives me an error. I tried print statements to debug and find out that the variables @tempCodeNo_local don't get the value assigned from the query that I run. That does not give proper result for the entire procedure.

So, any suggestions to make this working.

My basic aim is to create a view to display dbo.Order table information where such description will be displayed into a column inplace of the bitmapTest field. So that field gets replaced by a field with comma separated string values for that particular number.

Thanks in advance for your suggestions and solutions.

Ujjaval

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 01:35:25

See if this helps
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-14 : 20:42:58

Madhivanan,
thanks for your reply. String concatenation can be done with the code that I have posted in my main post. But, the condition to concatenate those strings is not working in here. Let me write that part here again.


-- This is the code taken from the code for stored procedure sp_dynamicDecode.

while(@counter > 0)
begin

--queryOne query string should return an integer number from [dbo].[codeTable] row by row --in each loop.
--Here, I can't get that integer number in the variable @tempCodeNo_local which is --declared in the stored procedure.

	set @queryOne = 'select top 1 bitmapID from [dbo].[codeTable] 
			where bitmapID not in (select top '+ cast(@rowcounter as nvarchar) +' bitmapID from [dbo].[codeTable] order by bitmapID) order by bitmapID'

        execute sp_executesql @tempCodeNo_local =  @queryOne

	print @tempCodeNo_local

--This queryTwo is basically reads the associated String value with the integer number --retrieved from queryOne execution and that value should be accessed via variable --@tempCodeNo_local.	

        set @queryTwo = '(select description from [dbo].[codeTable]
		 	where bitmapID = ' + cast(@tempCodeNo_local as nvarchar)

	execute @tempString_local = sp_executesql @queryTwo

	set @tempOutput = ', ' + @tempString_local

	print @tempOutput

	if (@codeNo & @tempCodeNo_local) = @tempCodeNo_local
		set @outputString = @outputString + @tempOutput

	set @counter = @counter - 1
	set @rowcounter = @rowcounter + 1
end

My string concatenation condition is in summary like this:

1. Read [bitmapTest] integer value from [dbo].[Order]
2. For all the row values in table [dbo].[codeTable]
3 Read [bitmapID] from [dbo].[codeTable]
4 Read [description] string value from [dbo].[codeTable] for the [bitmapID] read in step 3.
5. Perform bit operation AND on [bitmapID] and [bitmapTest] integer values and
on success, concatenate the [description] string with the specified @outputString variable and return that.

In, above algorithm, I have trouble in Step-3 and Step-4. I can't read [bitmapID] and [description] row by row and store that in the variables @tempCodeNo_local and @tempString_local for further execution.

Anybody, any ideas to get that done????????

Thanks in advance.

Ujjaval

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 02:33:10

Change

set @queryTwo = '(select description from [dbo].[codeTable]
where bitmapID = ' + cast(@tempCodeNo_local as nvarchar)

to

Select @queryTwo = description from [dbo].[codeTable]
where bitmapID = ' + cast(@tempCodeNo_local as nvarchar

Madhivanan

Failing to plan is Planning to fail

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-16 : 17:14:31

Thanks madhivanan.

I was also wondering, would that make a difference if I use CURSOR instead of this select queries???

-Ujjaval

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 01:11:39

You have replaced Cursors with While Loop

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources