How to get the recordset in SQL Server 2008 r2 stored procedure?

Can anyone convert this code to a SQL stored procedure?

Please I need your help I do not have an idea of this. Thanks in advance for those who will help.

This is a vb function, I want it to be in a SQL Server stored procedure.

Public Function setFileLog(strRTID As String, strReportID As String, strReportTitle As String, strApplID As String, strApplName As String, strFileName As String, strFilePath As String, strCreUser As String) As Boolean
    On Error GoTo ErrHandler

    strSQL = "SELECT * FROM COEM_FILELOG WHERE (FLOG_RTID = '" & strRTID & "') and (FLOG_REPORTID = '" & strReportID & "') " & _
    " and (FLOG_REPORT_TITLE = '" & strReportTitle & "') and (FLOG_APPLID = '" & strApplID & "') and (FLOG_APPL_NAME = '" & strApplName & "') " & _
    " and (FLOG_FILENAME = '" & strFileName & "') and (FLOG_FILEPATH = '" & strFilePath & "') and (FLOG_CREUSER = '" & strCreUser & "')"开发者_如何学编程
         Set rsSearch = clsDBManager.GetRS(strSQL, strConCustomServer)

     If Not rsSearch.EOF Then
        'call DAO setExceptionLog
        strRemarks = "Duplicate Entry in COEM File Log"
        Call clsDAO.setExceptionLog(strRTID, strReportID, strReportTitle, strApplID, strApplName, strFileName, strFilePath, strCreUser, strRemarks)

     Else

        strSQL = "INSERT INTO COEM_FILELOG ( FLOG_RTID, FLOG_REPORTID, FLOG_REPORT_TITLE, FLOG_APPLID, FLOG_APPL_NAME, " & _
                " FLOG_FILENAME, FLOG_FILEPATH ,FLOG_CREDATE, FLOG_CREUSER) " & _
                " VALUES ('" & strRTID & "', '" & strReportID & "', '" & strReportTitle & "', '" & strApplID & "', " & _
                " '" & strApplName & "','" & strFileName & "', '" & strFilePath & "', '" & Format(Now(), "MM/DD/YYYY HH:MM:SS") & "', '" & strCreUser & "')"

    Call clsDBManager.Execute(strSQL, strConCustomServer)
    End If
Exit Function

We can write it as the following stored procedure:

create procedure SetFileLog
    @RTID varchar(max),
    @ReportID varchar(max),
    @ReportTitle varchar(max),
    @ApplID varchar(max),
    @ApplName varchar(max),
    @FileName varchar(max),
    @FilePath varchar(max),
    @CreUser varchar(max)
as
INSERT INTO COEM_FILELOG
  (FLOG_RTID, FLOG_REPORTID, FLOG_REPORT_TITLE, FLOG_APPLID, FLOG_APPL_NAME, FLOG_FILENAME,
   FLOG_FILEPATH ,FLOG_CREDATE, FLOG_CREUSER) 
   SELECT @RTID, @ReportID, @ReportTitle,@ApplID, @ApplName,@FileName,
    @FilePath, CURRENT_TIMESTAMP, @CreUser
   WHERE NOT EXISTS(
       SELECT * FROM COEM_FILELOG
       WHERE
           FLOG_RTID = @RTID and 
           FLOG_REPORTID = @ReportID and
           FLOG_REPORT_TITLE = @ReportTitle and
           FLOG_APPLID = @ApplID and 
           FLOG_APPL_NAME = @ApplName and 
           FLOG_FILENAME = @FileName and
           FLOG_FILEPATH = @FilePath and
           FLOG_CREUSER = @CreUser)

return @@ROWCOUNT

Notes:

1. I doubt those parameter declarations are correct, but given your VB function is currently stringly-typed, it's the closest matching definition.
2. I don't trust the uniqueness constraint on this table either - is it really true that two rows are allowed, provided they differ only in e.g. that the creating user is different?
3. The return value of the procedure will be 0 (no rows inserted - an existing row prevented the insertion) or 1 (1 row inserted). You might want to reverse this, if you're working with the convention that 0 = success, all other return values = error.
4. We're now generating the current datetime value on the server, rather than in the client code. You may have issues if working across mutliple timezones, or if there is significant clock drift between client and server machines.

If you go into SQL 2008 R2 it can create a template of a Stored Procedure for you, then all you have to do is include the parameters and then the logic of the code. Since you already have the logic in place this shouldn't take very long. Just go to YourDB > Programmability > Stored Procedures, then right click the Stored Procedures folder and click Script To > New Stored Procedure.