Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 15th, 2008, 03:12 AM
Authorized User
 
Join Date: Aug 2006
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default store procedure, temp table

HALO THERE..I NEED SOME HELP.
i have do a query sql in the coding side using asp. after i do the searching, i wan to insert it into the temp table in the ms sql server.
below here is the asp code to call the SP to insert the search result into the temp table.:
Code:
 
sqlInsert = "sp_Insert "&photoid&",'"&photoname&"',"&a&",'"& NOW()&"'"
objconn.Execute sqlInsert
after that, i got the problem in create the temp table, insert the result into the table, select out the search result(distinct it) from the temp table and then after display delete the temp table OR maybe got other good ways?
can some one plz help me..urgent.
this is the SP i try to create but seem got problem
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP_INSERT]

@PHOTOID NUMERIC(18),
@PHOTONAME VARCHAR(MAX),
@LIGHTBOXNAME VARCHAR(MAX),
@DATEINSERT DATETIME
AS
BEGIN
    Create Table #tempmail(
        --INSERTDATE DateTime Default(GetDate()),
        INSERTDATE DateTime,
        PHOTOID VarChar(MAX) NOT NULL,
        PHOTONAME VarChar(MAX) NOT NULL,
        LIGHTBOXNAME VarChar(MAX) NULL
    )

IF (@PHOTOID IS NOT NULL)
    BEGIN
        INSERT INTO #tempmail (INSERTDATE,PHOTOID,PHOTONAME,LIGHTBOXNAME)
        VALUES(@DATEINSERT,@PHOTOID ,@PHOTONAME,@LIGHTBOXNAME)
    END 

Select * From #tempmail
RETURN
END
hope to hear from u all soon..plz help.thanx

 
Old January 15th, 2008, 05:43 AM
Friend of Wrox
 
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Try double #. For e.g. ##temp1

urt
 
Old January 15th, 2008, 05:46 AM
Friend of Wrox
 
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Every time if you select the temp table it will contain only the last inserted row, that is current row. So why you need to insert data in temp table when you are not processing it further except select.

urt





Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Temp Table Variable in Stored Procedure rweide SQL Server ASP 2 April 15th, 2011 03:50 PM
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
How can I refresh a table in store procedure flyfish SQL Server 2000 1 July 19th, 2005 07:01 PM
Can you pass a table to a store procedure? pbyrum SQL Language 1 January 26th, 2005 01:43 PM
global temp table vs.permanent table use sofya SQL Server 2000 0 December 17th, 2004 01:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.