Stack With SQL Server

Stack With SQL Server

• Sanjay_msdn

  It isn't beautiful but at least it show that it's possible. There is all possible kind of errors and concurrency issues to consider but it works for getting a sum. I would definatly seek other solutions, even as a humble lackey.

  This is not reverse polish notation, that you have to figure out. This just demonstrates a stack and recursive calling to process it in a totally proprietary way.

  create table dbo.Stack
(
stkID int identity(1,1) not null primary key,
stkData varchar(50) not null,
)
go

create procedure dbo.spStackPush
(
@stkData varchar(10)
)
as
begin
set nocount on
insert into Stack (stkData) values (@stkData)
if @@error <> 0 raiserror('Could not push to stack', 16, 1)
return 0
end
go

create procedure dbo.spStackPop
(
@stkData varchar(10) output
)
as
begin
set nocount on
declare @stkID int
select
@stkData = stkData,
@stkID = stkID
from dbo.Stack
where stkID = (select max(stkID) from dbo.Stack)

delete from dbo.Stack where stkID = @stkID
return 0
end
go

create procedure dbo.spStackEvaluate
(
@stkData varchar(10) output
)
as
begin
set nocount on
declare @stkLeft varchar(10)
declare @stkRight varchar(10)
declare @stkNext varchar(10)

exec dbo.spStackPop @stkData output

if @stkData = '+'
begin
exec dbo.spStackPop @stkLeft output
exec dbo.spStackPop @stkRight output

set @stkData = cast(@stkLeft as int) + cast(@stkRight as int)
end

if exists(select 1 from dbo.Stack)
begin
exec dbo.spStackPop @stkNext output
exec dbo.spStackPush @stkData
exec dbo.spStackPush @stkNext
exec dbo.spStackEvaluate @stkData output
end

return 0
end
go

-- Test script
delete dbo.Stack

declare @stkData varchar(10)

exec dbo.spStackPush '10'
exec dbo.spStackPush '+'
exec dbo.spStackPush '20'
exec dbo.spStackPush '+'
exec dbo.spStackPush '30'
exec dbo.spStackPush '40'
exec dbo.spStackPush '+'

exec dbo.spStackEvaluate @stkData output
print @stkData

  
  

• Sephiroth84

  Do two procedures that do the essential stack basics to add some abstraction. spStackPush and spStackPop.

  Then you use another procedure spStackEvaluate that you call. In pseudecode without error handling maybe something like this. There is no need for recursion to do what you seem to want to do.

  procedure spStackEvaluate
     @val output
  begin
     execute spStackPop @val output

     if @val = '+'
     begin
         execute spStackPop @leftval output
         execute spStackPop @rightval output
         set @val = cast(@leftval as numeric) + cast(@leftval as numeric)
     end
  end

   

  
  
  

• oceanhai

  I choose not to call spStackEvaluate for spStackPop because it is not only popping the stack, it is doing much more. Stack operations are usually push and pop so to at least not to try confuse myself I tried to stick to those basic operations.

  Rename spStackPop to spStackPopOne and stStackEvaluate to spStackPop and you will be more close to what you describe. It's just names so it does not matter much.

  
  
  

• Guy Serfaty

  I'm sure you were trying to be helpful, but trust me when I say that I wouldn't touch T-SQL for doing logic / processing with a bargepole unless I was absolutely forced to, as is the case.
  
  I am not dictating the requirements in this case.
  
  Once again, I DO need to implement a stack in Sql and I would be incredibly grateful if anyone has any advice on the recursive call mechanism needed to get this working (i.e. if the value popped of the stack is an operator, to gather the next two values (operands) and operate on them).
  
  

• Vossekop

  Why do you want to do this in SQL Server   Can't you implement this in a client tier

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <dpua@discussions.microsoft..com> wrote in message news:6f811e87-6d27-424c-8f6e-e880289c443d@discussions.microsoft.com...

  Hi,
  
  I need to implement a stack using Sql Server.
  
  The basic table is simple:-
  id (int), val (char(10)).
  
  Basically I want a recursive EvaluateStack stored procedure. This should select the topmost value from the stack, and then either return it or (in the case of an operator) recursively call itself in order to get a left hand and a righthand operator. So:
  
  (pseudo-code)
  SELECT val FROM STACK WHERE id=@Count
  CASE WHEN val="+"
  lhs=EXEC EvaluateStack
  rhs=EXEC EvaluateStack
  return lhs+rhs
  SELECT @Count=@Count-1
  Etc. etc.
  
  The problem is, being a bit rusty with Sql Server, can anyone give me some pointers how to best implement the conditional logic
  
  I.e. I want to evaluate the value in the row at the bottom of the table, then if it is an operator EXECUTE this stored procedure again.
  
  Thanks in advance. Another peculiarity is that '+' evaluates to true using Sql Server's ISNUMERIC function.
  
  
  
  
  

  
  

• Xinil

  Thanks for the answer. However, there is a need for recursion.
  
  The stack in question is basically being used to hold the values for a reverse polish notation calculation on certain values in the database.
  
  So, while a formula could be as simple as 2,2,+, it could also take a more complex form...and even with sthg not that much more complex like 10,10,+,2,/: recursion is needed. Because, say you evaluate '/'. Naturally you then need two operands. The problem is you are going to get one operand (2 in this case) and then another operator, engendering the need for a recursive call in order to get a valid operand.
  
  I'm sure your stomach is churning at doing it this way, so let me underline again that this is not a design of my choice, I am a humble lackey.
  
  

• Joseph Lee

  Andreas,

  Many thanks for this. This is a good way towards a solution, although as I say I believe the call to Pop must be replaced with a recursive call to Evaluate. That way if it hits an operator it will first fetch two operands.

  But that's the bit of code that is proving tricky, and somewhat undocumented...

  
  
• Stack With SQL Server