sql, how to create, insert, select, delete temptbl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql, how to create, insert, select, delete temptbl

Author

Topic

purple cloud
Starting Member

2 Posts

Posted - 2008-01-15 : 02:48:49

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.:

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

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-15 : 02:51:49

is it possible for your users to name their photos with a string 2gb in size? if not, varchar(max) is probably overkill. same for your other varchar(max) columns.

elsasoft.org

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-15 : 02:57:09

I take it - you are hard-coding an "a" for the lightbox name, Try this:

sqlInsert = "sp_Insert "&photoid&",'"&photoname&"','a','"& NOW()&"'"
objconn.Execute sqlInsert

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources