Creating a "Macro" in SQL 2

[lamago]

Hi everyone,

Im trying to go from Access to SQL. I want to set up a "macro" type procedure that does several operation. Update a table, drop a table, and create several other tables. When I use the stored procedure in SQLServer I can only use 1 Go. Does this mean I have to do one for each action? Is there a way to put them all in one? And if so where and how do we "run" it? I can do the code fine, I just dont know where to do it and how to run it other than doing several stored procedures.

 

[gmmastros]

Almost true. Go signifies the end of the stored procedure. However, you don't need to seperate the individual sql commands with go.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lamago]

Except when I dont put the go statement it doesnt create the table. Here is my code. How do I run a stored procedure?

UPDATE [cahsee].[cahseeadmin].[Class Schedule]
SET [School Number] = 32,
[School] = 'PHS West'
WHERE
( [School Number] = 30 AND
[grade] = 9)

UPDATE [cahsee].[cahseeadmin].[Class Schedule]
SET [School Number] = 99,
[School] = 'Learning Center'
WHERE
( [School Number] = 40 AND
[Track] = 4)

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'CAHSEEAttemptedSQL' AND type = 'T')

DROP PROCEDURE CAHSEEAttemptedSQL

create table cahseeadmin.CAHSEEAttemptedSQL (Permid float, attpela int, attpmath int)
insert into CAHSEEAttemptedSQL (permid, attpela, attpmath)
select studentid, sum( case when elapassed in ('N', 'P') then 1 else 0 end ) as attpela,
sum( case when mathpassed in ('N', 'P') then 1 else 0 end ) as attpmath
from pusdcahseeresults
group by studentid
GO

 

[gmmastros]

Assuming all of the commands are valid (and at first glance they appear to be), here's how you create the stored procedure.

Create Procedure [!]ProcedureName[/!]
As
UPDATE [cahsee].[cahseeadmin].[Class Schedule] 
SET  [School Number] = 32,
     [School] = 'PHS West'
WHERE 
    ( [School Number] = 30 AND
     [grade]     = 9)

UPDATE [cahsee].[cahseeadmin].[Class Schedule] 
SET  [School Number] = 99,
     [School] = 'Learning Center'
WHERE 
    ( [School Number] = 40 AND
     [Track]     = 4)

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CAHSEEAttemptedSQL' AND type = 'T')

   DROP PROCEDURE CAHSEEAttemptedSQL

create table cahseeadmin.CAHSEEAttemptedSQL (Permid float, attpela int, attpmath int)
insert into CAHSEEAttemptedSQL (permid, attpela, attpmath)
select studentid, sum( case when elapassed in ('N', 'P') then 1 else 0 end ) as attpela,
                          sum( case when mathpassed in ('N', 'P') then 1 else 0 end ) as attpmath
from pusdcahseeresults
group by studentid

You first execute everything in the previous code block. The commands won't run, but the stored procedure will be created. To run the stored procedure, just call the name of the stored procedure in a seperate QA window, or Highlight just the name and press F5.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Looking a little closer, I notice you have a 'DROP PROCEDURE'. I suspect you'll want to change that to, 'Drop Table'


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Create table needs one more parameter (If I could named so) the ight syntax is:

CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name

so if you don't want to specify schema name you must change your statemets to:

create table cahseeadmin..CAHSEEAttemptedSQL
-- or just
USE cahseeadmin
create table CAHSEEAttemptedSQL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lamago]

Thanks, this is what im missing. What is a QA window? Query Analyser? How do I get to this using Enterprise Manager?

 

[gmmastros]

In Enterprise Manager, click Tools -> SQL Query Analyzer

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

QA is Query Analiser, From EnterPrise Manger select Tools->QA

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lamago]

Thanks! That exactly it! Actually cahseeadmin is the schema and cahsee is the database. Ill add that too.

Thanks!