9
I'm trying to use a stored procedure to display the results of a table, and an associated table or recent changes to the database. The stored procedure is:
set ANSI_NULLS ON
set NOCOUNT ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[MKTG_Current]( @added smalldatetime OUTPUT, @named varchar(50) OUTPUT)
AS
DECLARE @pDate smalldatetime;
DECLARE @AIID int;
DECLARE @Table varchar(50);
DECLARE @Bork nvarchar(350);
SET @pDate = GETDATE()
SELECT @Table=[Table], @AIID=AIID, @added=date_added FROM MKTG_Recent WHERE date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, @pDate))
SET @named = @Table
SET @Bork = 'SELECT * FROM ' + QUOTENAME(@Table) + ' WHERE AIID= ' + cast(@AIID as varchar(100))
EXECUTE sp_executesql @Bork, @added OUTPUT, @named OUTPUT
SELECT @added, @named
It's supposed to return to items in addition to the results from the select statement. There are no inputs to the stored procedure. The stored procedure compiles fine in SQL Management Studio (2008), but the page returns an error: Microsoft OLE DB Provider for SQL Server error '80040e14'
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. index.asp, line 61
Line 61 of the page is in bold:
dim Objrs, cmd
set Objrs = Server.CreateObject("ADODB.RecordSet")
set cmd = Server.CreateObject("ADODB.Command")
set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnect
set cmd.ActiveConnection = conn
cmd.CommandText="MKTG_Current"
cmd.CommandType=adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@added", 135, 2)
cmd.Parameters.Append cmd.CreateParameter("@named", 200, 2, 50)
Line 61 **set Objrs = cmd.Execute**
name_of_table = cmd.Parameters("@named")
added = cmd.Parameters("@added")
I'm of the impression that this is caused by a SQL code error, but I'm not seeing it. A quick check of the Objrs.state is returning a 0, which means the problem