Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: creating new tables vs passing parameters

    There 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

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    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").Record Source = strSQL

    DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

    DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview
    Last edited by Sia; 04-14-03 at 16:58.

  4. #4
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    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").Record Source = 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •