creating new tables vs passing parameters

Hi,
I am using an Access ADP front end with a SQL Server backend.
I have a report which is based on a stored procedure with multiple parameters.
I have a Search form with multiple drop downs and the parameters are passed to the Sp from this form.

I had many problems passing the parameters to the Report since some of them may not be supplied and a default of % should be used.

I finally decided to creat a new table using the SP and insert the data to that table.

My question is if I create a temp table named "A" each time the report is ran, and say two users run the report at the same time, what will happen?

Is there any way to creat a temp table that SQL Server would take care of its name?

ThanksThere is no problem if you create a temp table during the execution of a SP. SQLServer will take care of you temp table and identify it after the connection user, so that even if two different users executes the same store proc the temp tables that are created are different. A temporary table is scoped to the execution of the batch (here the store proc), that means the table will be removed after the store proc ends.

But I still don't understant why you are not satisfied with store procs and which is the big problem in passing parameters to one. what the benefit will be if you create a temp table?

ionut calin|||The problem is that I want the SP to be the recordsource for a report.
See, I have a search form with the below criteria:

Caller
Property Manager
Date
BuildingID
Comm. Status
Ops Mgr

Then, I have a stored procedure based on 3 joins. The user should be able to check the Parameters he wants to search for and the SP will return the results. For example if I want all the records for Date='2/2/2002' and BuildingID='55555', the SP will return all the recods for these values and % (any thing) for other parameters.

I am using an access front end and I use VB to pass the paramertes to the SP. Its fine so far. But when I set the recordsource of the report to a sql string executing the SP with the parameters the report gets the first parameter and not the second or third or... one.
I don't get why?
Thats why I decided to set the recordsource of the report to a table.
You can see the VB code and the SP below:

Stored Procedure:---------------
---------------
CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = '%',
@.RPropMgr varchar(50) = '%',
@.RDate varchar(50) = '%',
@.RComStat varchar(50) = '%',
@.ROpsMgr varchar(50) = '%',
@.RBID varchar(50) = '%')

AS SELECT
dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID LIKE @.RCaller OR
dbo.tblComIssue.ContactID IS NULL) AND

(dbo.tblProperty.ContactID LIKE @.RPropMgr OR
dbo.tblProperty.ContactID IS NULL) AND

(dbo.tblComIssue.IssueClosed LIKE @.RComStat OR
dbo.tblComIssue.IssueClosed IS NULL) AND

(dbo.tblProperty.OpsMgrID LIKE @.ROpsMgr OR
dbo.tblProperty.OpsMgrID IS NULL) AND

(dbo.tblProperty.BuildingID LIKE @.RBID OR
dbo.tblProperty.BuildingID IS NULL) AND

(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE CONVERT(Varchar(10), @.RDate, 101) OR
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

----------------------

VB Code:------------------
'I have check boxes for each parameter. If the check box is check and the users wants to serach by that check box I will add that parameter to the string:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = Param & "@.RCaller=" & "'" & Me!RCaller & "'"
End If

If Me!ChBID = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RBID=" & "'" & Me!RBID & "'"
End If

If Me!ChPropMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RPropMgr=" & Me!RPropMgr
End If

If Me!ChDate = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
End If

If Me!ChComStat = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RComStat=" & "'" & Me!RComStat "'"&
End If

If Me!ChOpsMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.ROpsMgr=" & "'" & Me!ROpsMgr & "'"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview|||Sorry for the delay, but not my fault. I've wrote a replay for you post it but it didn't apear (I don't know why).

First of all, passing parameters to stored procs:

-I would try to set null as the default value for params (i've had problem passing null value through an ODBC connection (here you have an OLEDB one, and it just might work, but why take chances)

So store proc would look like this:

CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = NULL,
@.RPropMgr varchar(50) = NULL,
@.RComStat varchar(50) = NULL,
@.ROpsMgr varchar(50) = NULL,
@.RBID varchar(50) = NULL)

AS SELECT

--@.RDate varchar(50) = '%', I can't see the point of this parm

dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID IS NULL or @.RCaller is null or dbo.tblComIssue.ContactID LIKE '%'+@.RCaller
) AND

(
dbo.tblProperty.ContactID IS NULL or @.RPropMgr is null or dbo.tblProperty.ContactID LIKE '%'+@.RPropMgr
) AND

(
dbo.tblComIssue.IssueClosed IS NULL or @.RComStat is null or
dbo.tblComIssue.IssueClosed LIKE '%'+@.RComStat
) AND

(
dbo.tblProperty.OpsMgrID IS NULL or @.ROpsMgr is null or
dbo.tblProperty.OpsMgrID LIKE '%'+@.ROpsMgr
) AND

(
dbo.tblProperty.BuildingID IS NULL or @.RBID is null or
dbo.tblProperty.BuildingID LIKE '%'+@.RBID OR
) --AND

--this make no sense at all. What's the point of next possible condition
--"03/12/2003" like "%01/12/2003". You can not use like to compare a
--date field with what another date, or another date part. No sense at all
--(at least not for me) so I've remove it
--(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE
--CONVERT(Varchar(10), @.RDate, 101) OR
--CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

VB:

In VB when you want to call a sub, a functin or a method and you don't want to give explicit values for the parameters that have a default one, you simply "step over" the parameter, but still put commas, like:

DOCmd.SomeMethod 1,,3,4 (here param 2 takes whatever its default value is)

In SQLServer store proc you can't just simply ignore a param. If you want a parameter to take its default value you must write DEFAULT instead of explicit value.

exec SomeStoreProc 1,DEFAULT,3,4 (You don't have to specify param names like @.PARAM1=1, @.PARAM2=default and so on...)

So here is your VB code:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = "'" & Me!RCaller & "' , "
else
Param="DEFAULT , "
End If

If Me!ChBID = True Then
Param = Param & "'" & Me!RBID & "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChPropMgr = True Then
Param = Param & "'" & Me!RPropMgr & "' , "
else
Param=Param & "DEFAULT , "
End If

'I removed the follwoing lines (for the known reason)
'If Me!ChDate = True Then
'If Not Param = "" Then
'Param = Param & ","
'End If
'Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
'End If

If Me!ChComStat = True Then
Param = Param & "'" & Me!RComStat "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChOpsMgr = True Then
Param = Param & "'" & Me!ROpsMgr & "'"
else
Param=Param & "DEFAULT"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview

I am not too familiar with .adp type of project, and I'm not sure if this can really work:

RecordSource = "execute SP_Report_ComIssue_Custom_Param " & Param

but if you said that it works its ok. The main problem was the way that you passed the parameters.

Another thing. If you use Access XP .adp's then your store procedures are seen by Access as querys, so you can set that query as record source for report in design view, and not need to change afterwards (only need to set parameters value for querys -> it's very simple using ADODB and ADOX objects hierachy-> see Catalog object in ADO help)

In Access 2000, .adp's works with SQLServer7 and sees store proces as store procs. I don't think that you can set the record source of a report to a store proc (only to a table or a query, maybe that was thw reason they change the way that store procs aree seen in Access 2002)

Anyway record source property of Access report sucks, you can not for instance set the recor source to a ADO recordset (or to a DAO recordset for that matter). I think this is relly stupid, because prevent programmers from benefit the real power of SQLServer store procs, and I mean it store procs are the best (and now user defined functions too).

Anyway, good luck!

ionut calin