Anyone know the correct sytax for SQL Parameters in Access?

• Aaron Edwards

  SSC Eights!

  Points: 834

  More actions

  July 31, 2005 at 12:27 am

  #67828

  I can't seem to find the info online.  I just want to do a basic INSERT or UPDATE query, with @Parameters to make code more readable and deal automatically with the problem of apostrophes in input text.  Thanks.

  Aaron

• dUros

  SSCommitted

  Points: 1506

  More actions

  July 31, 2005 at 10:29 am

  #578372

  I don't know if understand you, but anyway. I use function like this to check the string for parameters:

  Function Fstr(varInput as variant) as string

     Dim varTemp As String

     If Len(Trim(Nz(varInput, ""))) = 0 Then

        varTemp = ""

     Else

        varTemp = Replace(varInput, "'", "''")

        varTemp = Replace(varTemp, """", """""""")

     End If

    

     FStr = varTemp

  End Function

  And here is primer for SP. Parameter myVar go through Fstr function:

  CREATE PROCEDURE test_sp

  @myVar varchar(50) = null

  AS

  SET NOCOUNT ON

  DECLARE @SQLString varchar(250)

  SET @SQLString = 'SELECT [' + @myVar + ']  AS myOutput FROM [My Table]'

  EXEC (@SQLString)

  GO

  I think to make UPDATE or INSERT SP is equal ....