Create SP with Temp Tables? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Create SP with Temp Tables?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-22 : 10:57:19

Is it possible to create a stored procedure with a Create Temp table and insert inside that sp?

I tried doing it, but it says my parameters need to be declared. I had it declared already, so I'm not sure if this is possible.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-22 : 11:07:11

[code]
create proc bla
as
create table #blah (id int)
insert into #blah
select 1 union all
select 2 union all
select 3

select * from #blah
drop table #blah
go

exec bla

go
drop proc bla
[/code]

Go with the flow & have fun! Else fight the flow

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-22 : 11:55:49

Here's a small example that i have that keeps giving me error.

CREATE PROCEDURE CW_CLOSEOUT
@STARTDATE AS SMALLDATETIME,
@ENDDATE AS SMALLDATETIME

AS

IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
WHERE NAME LIKE '#TEMP%')
DROP TABLE #TEMP1
GO

CREATE TABLE #TEMP1(
TYPE CHAR(1),
BASE_ID VARCHAR(30),
LOT_ID VARCHAR(3),
SPLIT_ID VARCHAR(3),
SUB_ID VARCHAR(3),
PART_ID VARCHAR(30))

INSERT INTO #TEMP1(
TYPE,
BASE_ID,
LOT_ID,
SPLIT_ID,
SUB_ID,
PART_ID)

SELECT TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, PART_ID
FROM PART
WHERE CREATED_DATE BETWEEN @STARTDATE AND @ENDDATE

SELECT *
FROM #TEMP1

This is not my complete SP, but it shows what I'm trying to do. Keeps sayin "Must declare the variable '@STARTDATE'". Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-22 : 11:59:38

you have a GO after the drop table #temp1.
so everythign below go isn't in a sproc.

you could also just do
SELECT TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, PART_ID
into #TEMP1
FROM PART
WHERE CREATED_DATE BETWEEN @STARTDATE AND @ENDDATE

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-22 : 12:10:26

BOL

quote:

GO
Signals the end of a batch of Transact-SQL statements to the Microsoft® SQL Server™ utilities.

Syntax
GO

Remarks
GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently. For more information, see osql Utility, isql Utility, and SQL Query Analyzer.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources