Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@pTable'.

/test.asp, line 15

    strsql = "CREATE Procedure sp_testProc " & _
             " @pField varchar(20), " & _
             " @pTable varchar(20)" & _
             " As " & _
             " Select @pField FROM @pTable ORDER BY @pField ASC " & _
             " Go"
           
    dataConn.Execute(strsql)

quote:

---

PS. based on what you're doing, I'd still use SQL.

---

Dim conn, rs, strSQL

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=SQLSVR1;database=******;uid=sa;pwd=********;"

set rs = Server.CreateObject("ADODB.Recordset")

strSQL = "CREATE PROCEDURE sp_GetID @location varchar(15) AS SELECT TENANT_ID, HEAD_L_NAME, HEAD_F_NAME, PROJECT_ID FROM ten_mast where project_id = @location"
rs.Open strSQL, conn

set rs = Nothing

strConnString = "driver={SQL Server};server=sqlxx.xxxxx.net;uid=xxxx;pwd=xxxx;database=xxxx"
set dataConn = Server.CreateObject("adodb.connection")
dataConn.Open strConnString
  '#### Lets test out this stored procedure thing

  if request.querystring("s") = "create" then
    strsql = "CREATE Procedure sp_testProc " & _
             " @pField varchar(20), " & _
             " @pTable varchar(20)" & _
             " As " & _
             " Select @pField FROM @pTable ORDER BY @pField ASC "
           
    Set rs = Server.createObject("adodb.recordset")
    rs.Open strsql, dataConn
    rs.Close
    Set rs = nothing
    
    response.write("Stored Procedure Created")
  end if
  
  
dataConn.Close
set dataConn = nothing

quote:

---

CREATE PROCEDURE ZCommunity_VotingGetPollsForWebBox2
@InCommunity_ID int,
@InSection_ID int,
@InUsername nvarchar(50)
AS
DECLARE @DefaultRoles nvarchar(256)

SELECT DISTINCT p.Poll_ID
FROM Community_VotingPolls p
LEFT JOIN Community_VotingPollSections s ON s.Poll_ID = p.Poll_ID
INNER JOIN Community_VotingPollRoles r ON r.Poll_ID = p.Poll_ID
WHERE
Community_ID = @InCommunity_ID AND
IsActive=1 AND
(IsGlobal=1 OR (s.Section_ID = @InSection_ID)) AND
(r.Rolename = 'Community-Everyone'
OR r.Rolename IN (SELECT 'Community-Authenticated' AS Role FROM Community_Users WHERE User_Username=@InUsername)
OR r.Rolename IN (SELECT UserRoles_Rolename FROM Community_UsersInRoles WHERE UserRoles_Username=@InUsername)) AND
(StartDate is null OR StartDate <= getUtcdate()) AND
(EndDate is null OR EndDate >= getUtcdate())

---

Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

Dim objTextStream

strFileName = server.MapPath("create_sp.sql")
const fsoForReading = 1
response.Write (strFileName)
If objFSO.FileExists(strFileName) then
	'The file exists, so open it and output its contents
	Set objTextStream = objFSO.OpenTextFile(strFileName, fsoForReading)
	sql = objTextStream.ReadAll
	objTextStream.Close
	Set objTextStream = Nothing
Else
	'The file did not exist
	Response.Write strFileName & " was not found."
End If

'Clean up
Set objFSO = Nothing

 
strConnString = "Provider=SQLOLEDB;Data Source=127.0.0.1;uid=username;pwd=password;database=communitystarterkit" 
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open strConnString

my_conn.Execute sql

my_conn.Close
Set my_conn = Nothing
%>