Create one store procedure to execute multiple SQL statements

[phil22046]

I have a very complex crosstab type of report I have been assigned to produce and my approach has been to create a temp file in the database with all the column headings and zeros in all the places where the numbers would go. What I want to do is to use several SQL statements to update the temp table and fill in the numbers.


Can I get a series of sql statements to run in a single stored procedure?

 

[gmmastros]

yes

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

Yes. Have a go and see what happens...


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

I forgot to mention...

When you create a procedure that performs multiple steps, I encourage you to add Set Nocount On at the beginning of the procedure. Like this...

Create Procedure WhateverNameYouWant
  @Param1 int,
  @Param2 varchar(20)
As
[!]SET NOCOUNT ON[/!]
[green]-- You T-SQL code here[/green]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

One thing to avoid is to use the word GO between the steps, this will end the batch and thus end the execution fo the sp.

If you are doing realted inserts/updates/deletes look up transaction processing to make sure that you put them inthe smae tranasaction so they will be comitted or rolled back together.

"NOTHING is more important in a database than integrity." ESquared

 

[phil22046]

Only the first SQL statement seems to do anything. The second creates a temp table in my database and no temp table exists after execution. Do I need to put the word GO between each statement?

 

[phil22046]

My beginning of code is:

Create Procedure RevenueByMonth

@startingdate datetime,
@endingdate datetime

SET @startingdate = '01/01/2007'
SET @endingdate = '12/31/2007'

I am getting this error:

Server: Msg 156, Level 15, State 1, Procedure RevenueByMonth, Line 6
Incorrect syntax near the keyword 'SET'.

 

[gmmastros]

You are missing the AS keyword.

Create Procedure RevenueByMonth

@startingdate datetime,
@endingdate datetime
[!]AS
Set NOCOUNT ON[/!]
SET @startingdate = '01/01/2007'
SET @endingdate   = '12/31/2007'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

If you create a temp table in a stored procedure, it will be gone once the SP exits. If you want the temp table to persist outside of the SP, you have to create it outside the SP.