How to use the REPLACE FUNCTION 1

[git2dn]

I created a dynamic SQL string named strSQLFS in my Access applcation and the last part of this string is a WHERE clause as follows:

strSQLFS = " (C.DateL <= @DateFI AND P.Prop='FI' AND P.Ira Is Null AND S.Cycle='1') "

In my Access application I call a parameterized stored procedure in the following paragraph:

With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDFl"
.Parameters.Refresh
Set prmSQL = .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
.Parameters.Append prmSQL
Set RptYear = .CreateParameter("@RptYear", adInteger, adParamInput, 4, intYearSP)
.Parameters.Append RptYear
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With


My stored procedure is as follows:

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYear int

AS
DECLARE @DateFI DATETIME

SELECT @DateFI = CAST((@RptYear - tblS.B) AS VARCHAR) +'-09-30' FROM tblS

(comment: tblS.B represents an integer value)

SET @prmSQL = REPLACE (@prmSQL,'@DateFI', ???)
EXEC( @prmSQL)

GO

The problem is that I define a variable in the stored procedure named @DateFI yet I reference it in my dynamic SQL string. I get an error when I execute the Access application which I attribute to referencing the stored procedure variable @DateFI out of scope in my dynamic sql.

Can I use the REPLACE Function (to get around the error) to replace @DateFI in the @prmSQL parameter which contains the dynamic SQL that I passed from the Access application to the stored procedure ?

If I can use the REPLACE FUNCTION, do you know how I would fill in the 3rd parameter of this function

 

[philhege]

Since @DateFI is a string in your passed information, and you are CASTing the date as a character, you should be able to use

SET @prmSQL = REPLACE (@prmSQL,'@DateFI', @DateFI)

In order to reduce confusion, I'd call the replace marker something other than the same name as the parameter.

HTH,


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.