Crearting a new table via SPROCS

I supply a parameter @.TEMPTABLE for the table name. When I execute my SPROC with the parameter, execution goes thru by I always end up getting "@.TEMPTABLE" as the name of the table instead of the value of the parameter.

Any ideas would be appreciated.

Thank you.

CREATE PROCEDURE sp_CREATE_TEMP_TABLE
(@.TEMPTABLE varchar(30))
AS
CREATE TABLE @.TEMPTABLE (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]Hi,

your tablename is "@.temptable" ... the server will not use the var, instead it will name the table...

you need to build an cmd-string and execute it. something like this:

declare @.S nvarchar(1000)
select @.S=
'CREATE TABLE'+@.TEMPTABLE+' (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY] '
exec sp_executesql @.S

markus

CR &SQL

Hi all,

How to add Query Execution at detailed band of Crystal Report
pls
Here is my Code

Dim strSQL As String
Dim cn As New ADODB.Connection
Dim connectionstring As String

connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\Vb98\Prepaid Management System\Login.mdb"

cn.Open connectionstring

strSQL = "SELECT i.*, ii.* " _
& "FROM Inwards i LEFT JOIN Inwards_item ii ON i.receipt_no = ii.Receipt_no " _
& "WHERE ii.Receipt_no = " & inputinward.Txtreceiptno.Text
Debug.Print strSQL
CrystalReport1.SQLQuery = strSQL
cn.Execute strSQL
'CrystalReport1.DataSource = strSQL
CrystalReport1.ReportTitle = "Inwards Print Invoice Report"
CrystalReport1.Action = 1

cn.CloseMay be like this:
[code]
...
Dim rs as ADODB.RecordSet
Set rs = cn.Execute strSQL
CrystalReport1.DataSource = rs
CrystalReport1.ReportTitle = "Inwards Print Invoice Report"
...
[code]|||set rs = cn.Execute strSQL

Expectedn End of Statement.........

Counting stored procedure execution

I am looking for a way to count the number of times a stored procedure on the database has been executed over let's say over a period of time(month, years, etc).

Is there a system stored procedure or a system table that stores that information.

I am struggling to find some information about this topic
Thanks for the helpI don't know if that information is stored, but what you can do is to create your own table that will store the datetime the stored-procedure is run. You can then run your queries on that table.