Link to home
Start Free TrialLog in
Avatar of Irwin Santos
Irwin SantosFlag for United States of America

asked on

How to execute an SQL procedure using ASP?

I have the following SQL procedure below and would like to integrate that into an ASP page.  What would be the command(s) to do this?  To execute the procedure, all one has to do is click on the URL link.

I would like for this to be run as a STORE PROCEDURE.

Please refer to the following link for background information;
https://www.experts-exchange.com/questions/23519113/Creating-a-module-to-import-CSV-into-MS-SQL.html

Thanks in advance:
irwinpks
create table upload_PRHOURS (HDRECIDN varchar(60),HDHDRLNK varchar(60),HDWRKDTE varchar(60),HDCOMNUM varchar(60),HDSTRNUM varchar(60),HDSHFNUM varchar(60),HDREGNUM varchar(60),HDEMPNUM varchar(60),HDREGHRS varchar(60),HDOVTHRS varchar(60),HDRECSTS varchar(60),HDRECSRC varchar(60),HDUSRCHG varchar(60),HDDTECHG varchar(60))
go
 
BULK INSERT upload_PRHOURS from 'c:\inetpub\client\waiktrade\data\tempuploads\upload_prhours.csv' with (FORMATFILE = 'c:\inetpub\client\waiktrade\data\tempuploads\upload_prhours.fmt', firstrow=2)
go
 
INSERT PRHOURS  (HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG) 
SELECT HDRECIDN, HDHDRLNK, HDWRKDTE, HDCOMNUM, HDSTRNUM, HDSHFNUM, HDREGNUM, HDEMPNUM, HDREGHRS, HDOVTHRS, HDRECSTS, HDRECSRC, HDUSRCHG, HDDTECHG 
from upload_PRHOURS where not exists (select * from PRHOURS p where p.HDDTECHG = upload_prhours.hddtechg and p.hdrecidn = upload_prhours.hdrecidn) 
GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Irwin Santos

ASKER

@mark_wills

I've got everything except the temp table.  By having the "#" prefix, does that tell the procedure to be temporary?

thanks,
Irwinpks
Yep, sure does... the procedure is complete...  and just for future information you can also get a ## temporary table - it is a global temp table - (as opposed to the local temp table) - globals can share across procedures / sessions...

from books on-line:

Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table by using the value specified for table_name in the CREATE TABLE statement, for example:

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users. The Database Engine does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

>A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

>All other local temporary tables are dropped automatically at the end of the current session.

>Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


@mark_wills

Again thanks!  I learned something new today.  Best wishes!
irwinpks