quote:

---

Originally posted by spirit1

ohhh.....

you have to put it all into a string...

Go with the flow & have fun! Else fight the flow

---

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.ou_transaction_history_sp @WhereClause varchar(1024)='' AS

SET NOCOUNT ON

SET DATEFORMAT dmy

DECLARE @Item_No varchar(30), @Location varchar(10), @Today varchar(20), @EndDate varchar(20), @SQLString varchar(8000)

DECLARE @PosItem int, @PosStart int, @PosEnd int, @PosLoc int, @Pos smallint, @N smallint

DECLARE @Transactions TABLE (id int identity(1,1), curr_date datetime, tran_id int, tran_type char(1), tran_status char(1), tran_no int, movement_qty decimal(20,8), rolling_balance decimal(20,8), tran_mtrl_cost decimal(20,8), user_nm varchar(30), location varchar(10), part_no varchar(30), start_date datetime, end_date datetime)

CREATE TABLE #ResultSet (id int identity(1,1), curr_date datetime, movement_qty decimal(20,8), rolling_balance decimal(20,8), tran_type varchar(15), tran_id int, tran_status char(1), tran_no int, user_nm varchar(30), location varchar(10), part_no varchar(30), start_date datetime, end_date datetime)

-- get position of parameters passed in where clause

SET @PosItem = PATINDEX ( '%part_no%' , @WhereClause )

SET @PosStart = PATINDEX ( '%start_date%' , @WhereClause )

SET @PosEnd = PATINDEX ( '%end_date%' , @WhereClause )

SET @PosLoc = PATINDEX ( '%location%' , @WhereClause )

-- this stored procedure will only do something if a part number and location are supplied in the where clause

IF @PosItem > 0 AND @PosLoc > 0
	BEGIN
		-- get the part_no sent by user

		SELECT @Pos = @PosItem, @N = 0
		
		WHILE(1=1)
			BEGIN
				SELECT @Pos = CHARINDEX('%',@WhereClause,@Pos+1), @N = @N + 1
				
				IF @Pos = 0
					BREAK
	
				IF @N % 2 = 1
					BEGIN
						SET @Item_No = SUBSTRING(@WhereClause,@Pos+1,CHARINDEX('%',@WhereClause,@Pos+1)-@Pos-1)
						BREAK
					END
			END

		-- get the location sent by user

		SELECT @Pos = @PosLoc, @N =0
		
		WHILE(1=1)
			BEGIN
				SELECT @Pos = CHARINDEX('%',@WhereClause,@Pos+1), @N = @N + 1
					
				IF @Pos = 0
					BREAK

				IF @N % 2 = 1
					BEGIN
						SET @Location = SUBSTRING(@WhereClause,@Pos+1,CHARINDEX('%',@WhereClause,@Pos+1)-@Pos-1)
						BREAK
					END
			END

		-- load all transactions for supplied part_no and location into @transactions

		INSERT @Transactions (curr_date, tran_id, tran_type, tran_status, tran_no, movement_qty, rolling_balance, tran_mtrl_cost, user_nm, location, part_no)		
		SELECT 
		curr_date,
		tran_id,
		tran_type,
		tran_status,
		tran_no,
		tran_inv_qty,
		0,
		tran_mtrl_cost,
		user_nm,
		location,
		part_no
		FROM dbo.inv_tran
		WHERE part_no = @Item_No AND location = @Location AND LEN(RTRIM(update_typ))>0 
		AND tran_type <> 'C' AND tran_type <> 'H' 
		AND tran_status <> 'P'

		-- calculate all transactions rolling balances into @ResultSet table

		INSERT #ResultSet (curr_date, movement_qty, rolling_balance, tran_type, tran_id,  tran_status, tran_no,  user_nm, location, part_no)		
		SELECT 
		curr_date, 
		movement_qty, 
		rolling_balance  = (SELECT SUM(movement_qty) FROM @Transactions where id <= A.id),
		tran_type = CASE tran_type WHEN 'I' THEN 'Inv Adj' WHEN 'S' THEN 'Sales' WHEN 'U' THEN 'Usage' WHEN 'P' THEN 'Produce' ELSE 'Unknown' END,
		tran_id, 
		tran_status, 
		tran_no, 
		user_nm, 
		location, 
		part_no
		FROM @Transactions A 

		-- build SQL to return data to user

		SELECT @SQLString = '
		SELECT 
		curr_date, 
		movement_qty, 
		rolling_balance, 
		tran_type, 
		tran_id,
		tran_status,
		tran_no,
		user_nm,
		location,
		part_no
		FROM #ResultSet
		WHERE '

		-- get start date if sent by user

		IF @PosStart > 0	
			SELECT @SQLString = @SQLString + 'curr_date >= ''' + SUBSTRING(@WhereClause, @PosStart+20, 8) + ''''

		ELSE
			SELECT @SQLString = @SQLString + 'curr_date >= ''2000/01/01'''  
			
		-- get end date if sent by user

		IF @PosEnd > 0
			BEGIN

				SELECT @EndDate = SUBSTRING(@WhereClause, @PosEnd+18, 8 ) + ' 23:59:59'

				SELECT @SQLString = @SQLString + ' AND curr_date <= ''' + @EndDate + ''' ORDER BY curr_date, tran_id'	

			END

		ELSE
			BEGIN
			
				SELECT @Today = convert(varchar, GETDATE(), 103) + ' 23:59:59'

				SELECT @SQLString = @SQLString + ' AND curr_date <= ''' + @Today + ''' ORDER BY curr_date, tran_id'	
		
			END

		-- send data back to user

		exec (@SQLString)
		
	END
ELSE

	-- if part_no or location not supplied return an empty table

	SELECT * FROM #ResultSet

GRANT ALL ON dbo.ou_transaction_history_sp TO PUBLIC