November 9, 2004 at 10:29 am
I am new to SQL, Now that I have created a Stored Procedure to create a view and compare the view to existing tables and if different and new records. How to I launch it automatically? Do I have to create a function to call the store procedure?
What is the best apprach.
I have create a Stored procedure in Query Analyzer, When I copy it into a new Stored Procedure and apply it, close it, then reopen it the new procedure doesn’t contain the info I just pasted, Why? Currently this file is saved as a .sql file – what is the best approach to executing this if not from a stored procedure?
Karen
Here is my code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE SiteManager_Import
AS
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'vwProject_Type')
DROP VIEW vwProject_Type
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'vwProject_Type')
DROP VIEW vwProject_Type
GO
CREATE VIEW vwProject_Type
AS
SELECT Q1.ID ProjTypeID, Q1.DESCR, Q1.PROJ_DESCR_CATEGORY, Q1.PROJ_CLASS, Q1.CPN
FROM SiteManager1..APPPCT.PROJECT_TYPE as Q1
LEFT JOIN ProjectTypes ON Q1.ID = ProjectTypes.ProjectTypeCode
WHERE ((ProjectTypes.ProjectTypeCode) Is Null)
GO
SET NOCOUNT ON
INSERT INTO dbo.ProjectTypes ( ProjectTypeCode, ProjectTypeDescrip, ProjectDescripShort, ProjectClass,
CapitalProjectNum, DateCreated, UserCreated, DateModified, UserModified)
(SELECT ProjTypeID, DESCR, PROJ_DESCR_CATEGORY, PROJ_CLASS, CPN,
CURRENT_TIMESTAMP AS DateCreated, 'Import' AS UserCreated,
CURRENT_TIMESTAMP AS DateModified, 'Import' AS UserModified
FROM vwProject_Type)
GO
Drop View vwProject_type
Thanks in advance,
Karen
November 9, 2004 at 5:33 pm
Ok. From reading your code I'm not sure if you want the view or a stored proc? No reason to create a view - just use a select in the stored procedure. You could probably re-write this as a stored proc and it would work fine. A view is used in a different way - for example, as a security tool. If your accounting dept should not see all of the numbers in table, you could create a view that would show them just the numbers they need to see.
Get rid of the "GO" statements from inside the procedure. GO is for batch processing and you are not batch processing. That's probably why when you open the stored proc again it looks unfinished. It is saved to just the first GO.
Bon chance avec la.
Quand on parle du loup, on en voit la queue
July 1, 2005 at 7:40 am
If there is a properly formed sproc existing in the library, how do you execute it? (Using the Enterprise Manager)
July 1, 2005 at 8:22 am
Not sure what you mean by "launch it automatically" ?!
Can you not just "EXEC SiteManager_Import" in your QA ?!
Also, if you outline your goals it would help - a cursory look at the procedure suggests that it might be "overkill" for whatever you want to accomplish!
**ASCII stupid question, get a stupid ANSI !!!**
July 2, 2005 at 6:07 am
Once you have created a proper stored procedure, you can run it several ways. Here are a few of the most common:
1. In Query Analyzer, type in the name of the proc, highlight it and press F5
2. Create a scheduled job to run the proc.
3. In a program, (VB, ASP, .Net), call the stored procedure with a command object.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply