exec report$operating_plan$criteria_text @classification output, @status output, @project_owner output, @team output, @department output

Classification      Status  Project Owner  Team  Department
------------------  ------  -------------  ----  ----------
Business Necessity  All	    All            All   All

' This executes a procedure, returns get rows and parameters are persisted
Public Function Execute(ByVal commandText, ByVal commandType, ByRef parameters)
	Dim objCommand:   Set objCommand   = CreateCommand(commandText, commandType, parameters)
	Dim objRecordset: Set objRecordset = Server.CreateObject("ADODB.Recordset")
 			
	Execute = Null
	objRecordset.CacheSize      = 20
	objRecordset.CursorLocation = adUseClient
	objRecordset.CursorType     = adOpenStatic
	objRecordset.LockType       = adLockReadOnly
 			
	Call OpenConnection()
	Set objCommand.ActiveConnection = m_objConnection
	Call objRecordset.Open(objCommand)
 			
	If objRecordset.State = adStateOpen Then
		If Not objRecordset.BOF And Not objRecordset.EOF Then
			Execute = objRecordset.GetRows()
		End If
 			
		Call objRecordset.Close()
	End If
 			
	If IsArray(parameters) Then Call PersistParameters(objCommand, parameters)			
	Set objCommand.ActiveConnection = Nothing
	Call CloseConnection()			
	Set objCommand   = Nothing
	Set objRecordset = Nothing
End Function
 
' Two functions used by the above
Private Function CreateCommand(ByVal commandText, ByVal commandType, ByRef parameters)
	Dim objCommand: Set objCommand = Server.CreateObject("ADODB.Command")
	objCommand.CommandText = commandText
	objCommand.CommandType = commandType
 			
	If IsArray(parameters) Then
		Dim objParameter
 				
		For Each objParameter In parameters
			If IsObject(objParameter) And Not objParameter Is Nothing Then
				Call objCommand.Parameters.Append(objParameter)
			End If
		Next
	End If
 			
	Set CreateCommand = objCommand
	Set objCommand    = Nothing
End Function
 		
Private Function PersistParameters(ByRef command, ByRef parameters)
	Dim intLength: intLength = UBound(parameters)
	Dim intPos
 			
	For intPos = 0 To intLength
		With command.Parameters.Item(parameters(intPos).Name)
			Set parameters(intPos) = CreateParameter(.Name, .Type, .Direction, .Size, .Value)
		End With
	Next
End Function
 
' The function that is called.
Public Function [report$operating_plan](ByRef classification, ByRef status, ByRef project_owner, ByRef team, ByRef department, ByVal start_date, ByVal team_report)
	Dim db: Set db = New SqlDatabase
	Dim parameters(6)
 		
	Set parameters(0) = CreateParameter("@classification", adVarChar, adParamInputOutput, 8000, classification)
	Set parameters(1) = CreateParameter("@status", adVarChar, adParamInputOutput, 8000, status)
	Set parameters(2) = CreateParameter("@project_owner", adVarChar, adParamInputOutput, 8000, project_owner)
	Set parameters(3) = CreateParameter("@team", adVarChar, adParamInputOutput, 8000, team)
	Set parameters(4) = CreateParameter("@department", adVarChar, adParamInputOutput, 8000, department)
	Set parameters(5) = CreateParameter("@start_date", adDate, adParamInput, 8, start_date)
	Set parameters(6) = CreateParameter("@team_report", adBoolean, adParamInput, 1, team_report)
 				
	[report$operating_plan] = db.Execute("report$operating_plan", adCmdStoredProc, parameters)
	Set db = Nothing
 		
	classification = parameters(0).Value
	status         = parameters(1).Value
	project_owner  = parameters(2).Value
	team           = parameters(3).Value
	department     = parameters(4).Value
End Function

create procedure report$operating_plan$criteria_text
	@classification varchar(8000) = null output,
	@status         varchar(8000) = null output,
	@project_owner  varchar(8000) = null output,
	@team           varchar(8000) = null output,
	@department     varchar(8000) = null output
as
    set nocount on

    declare @sql   nvarchar(4000)

    set @classification = isnull(@classification, '')
    set @status         = isnull(@status, '')
    set @project_owner  = isnull(@project_owner, '')
    set @team           = isnull(@team, '')
    set @department     = isnull(@department, '')

    if len(@classification) < 1
        begin
            set @classification = 'All'
        end
    else
        begin
            set @sql = 'select name
                          from project_classifications
                         where project_classification_id in (select convert(int, value)
                                                               from dbo.csv_to_table(''' + @classification + '''))'
            exec table_to_csv @sql, @classification output
        end

    if len(@status) < 1
        begin
            set @status = 'All'
        end
    else
        begin
            set @sql = 'select name
                          from project_statuses
                         where project_status_id in (select convert(int, value)
                                                       from dbo.csv_to_table(''' + @status + '''))'

            exec table_to_csv @sql, @status output
        end

    if len(@project_owner) < 1
        begin
            set @project_owner = 'All'
        end
    else
        begin
            set @sql = 'select last_name + '', '' + first_name
                          from users
                         where user_id in (select convert(int, value)
                                             from dbo.csv_to_table(''' + @project_owner + '''))'

            exec table_to_csv @sql, @project_owner output                        
        end

    if len(@team) < 1
        begin
            set @team = 'All'
        end
    else
        begin
            set @sql = 'select name
                          from teams
                         where team_id in (select convert(int, value)
                                             from dbo.csv_to_table(''' + @team + '''))'

            exec table_to_csv @sql, @team output
        end

    if len(@department) < 1
        begin
            set @department = 'All'
        end
    else
        begin
            set @sql = 'select name
                          from departments
                         where department_id in (select convert(int, value)
                                                   from dbo.csv_to_table(''' + @department + '''))'

            exec table_to_csv @sql, @department output
        end
go

quote:

---

Note Regarding Return Values and Output Parameters
Stored procedure return values and output parameters are only available if the CommandType is adCmdStoredProc and the command consists of only a single stored procedure call.

If you are submitting a batch of commands along with the stored procedure call, or submitting multiple stored procedure calls, then the return value(s) and output parameters are not available directly. You can get them indirectly by declaring some SQL Server variables and returning them as a Recordset. In the example below, the batch calls two stored procedures, both of which have a return value. The ? parameter markers can only be used for input parameters. You will receive an error if you try to use them with the OUTPUT keyword.

---

quote:

---

In this instance Classification was '3' going in, and should be that coming out. However it resorts back to '3' when it comes out.

---

I may be completely stoned, but that doesn't sound like a problem. You said it was '3' going in, and it should be '3' coming out, and indeed it is '3' coming out.

Is classification the only variable that this happens to? If it is, then I'm REALLY at a loss to explain this, unless there is something special about the Classifications table that doesn't apply to the others. The dbo.csv_to_table() function may be the culprit, but the code for it isn't here. What does this function return if it's passed an empty string?

I'm thinking that there is some kind of default ADO Connection behavior that affects how empty strings are interpreted. ADO may be doing something internally that screws up empty strings. This would depend on what settings the provider supports, and if you're connecting with the ODBC provider, I'm almost positive that's what's happening. ODBC is pretty finicky about certain things, and empty strings are pretty finicky in themselves; the twain may not be meeting here.

Have you tried using ByVal instead of ByRef? It's a shot in the dark, but I have the feeling that there may either be a mismatch of ByVal with ByRef somewhere, or that passing ByRef in one of the functions may not be the right way to go.

And you DEFINITELY AREN'T gonna like this suggestion, but if all else fails, can you separate the variables with one set for input only, and another for output only?

Edited by - robvolk on 04/04/2002 14:55:46

create procedure table_to_csv
    @sql    nvarchar(4000),
    @csv    varchar(8000) output
as
    set nocount on
 
    declare @id     int
    declare @value  varchar(8000)
    declare @string varchar(8000)
 
    set @string = ''
 
    create table #table_to_csv (id int identity(1,1), value varchar(8000))
    insert into #table_to_csv (value)
        exec(@sql)
 
    while exists(select * from #table_to_csv)
        begin
            select top 1
                   @id = id,
                   @value = value
              from #table_to_csv
 
            set @string = @string + @value + ', '
 
            delete
              from #table_to_csv
             where id = @id
        end
 
    set @csv = left(@string, len(@string) - 1)
    drop table #table_to_csv
go
 
create function csv_to_table(@csv varchar(8000)) returns @csv_to_table table (value varchar(8000)) as
    begin
        declare @position int
        set @position = charindex (',', @csv, 0)
 
        while len(@csv) > 0 and @position > 0
            begin
                insert into @csv_to_table (value)
                    select ltrim(rtrim(substring(@csv, 0, @position)))
 
                set @csv = ltrim(rtrim(substring(@csv, @position + 1, len(@csv))))
                set @position = charindex (',', @csv, 0)
            end  
 
        if len(@csv) > 0 
            insert into @csv_to_table (value)
                select @csv      
 
        return
    end
go

quote:

---

Microsoft OLE DB Provider for SQL Server error '80040e09'

SELECT permission denied on object 'teams', database 'stars', owner 'dbo'.

/includes/SqlDatabase.asp, line 119

---

quote:

---

oh no i need to iterate through each record ... no just the first one...

---