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.
Author |
Topic |
ITSSQL
Starting Member
8 Posts |
Posted - 2008-02-27 : 13:04:39
|
I have written following SQL query, this creates temporary table, inserts rows into it. I need to create VIEW "vw_NumberOfAttachments" in the database. I initially created table using "CREATE TABLE" but then i got error as VIEW can not be filled by temporary table. Hence I am using DECLARE TABLE--------------------------------SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE sp_GetViewNumberOfAttachments -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @emailMessageID bigint DECLARE @metaDataStorageID bigint DECLARE @numberOfAttachments int DECLARE @AttachmentDetails TABLE ( emailMessageID bigint, metaDataStorageID bigint, numberOfAttachments int ) DECLARE ATTACHMENT_CURSOR CURSOR FOR SELECT emailMessageID, metaDataStorageID FROM ppaEmailMessage WHERE hasAttachments='true' OPEN ATTACHMENT_CURSOR FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID WHILE @@FETCH_STATUS = 0 BEGIN -- here the table name need to get dynamically the name of the attachment table -- for a moment it is written as ppaMsOfficeDoc, but that should change dynamically set @numberOfAttachments = (SELECT count(*) FROM ppaMsOfficeDoc WHERE metaDataStorageID = @metaDataStorageID) INSERT INTO @AttachmentDetails(emailMessageID, metaDataStorageID, numberOfAttachments) VALUES (@emailMessageID, @metaDataStorageID, @numberOfAttachments) FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID END CLOSE ATTACHMENT_CURSOR DEALLOCATE ATTACHMENT_CURSOR IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vw_NumberOfAttachments') DROP VIEW vw_NumberOfAttachments GO CREATE VIEW vw_NumberOfAttachments AS SELECT @AttachmentDetails.emailMessageID, @AttachmentDetails.metaDataStorageID, @AttachmentDetails.numberOfAttachments FROM @AttachmentDetails GOENDGO----------------------I am getting following errors:-----------Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57Incorrect syntax near 'vw_NumberOfAttachments'.Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3Must declare the scalar variable "@AttachmentDetails".Msg 102, Level 15, State 1, Line 2Incorrect syntax near 'END'.-----------Can anyone please suggest whats wrong in there? Many thanks |
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-15 : 10:55:43
|
Wouldn't just a view be easier than all that? You can then pass a parameter and filter the VIEW that already exists ?CREATE VIEW vw_numberofattachmentsASSELECT email.emailMessageID ,email.metaDataStorageID ,doc.CT_numberofAttachmentsFROM ppaEmailMessage email inner join (Select Count(NumberofAttachments) as CT_numberofAttachments ,metadataStorageID FROM ppaMsOfficeDoc ) doc on email.metastorageID = doc.metastorageIDWHERE email.hasAttachments='true' and email.metaDataStorageID = doc.metaDataStorageID quote: I am getting following errors:-----------Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57Incorrect syntax near 'vw_NumberOfAttachments'.Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3Must declare the scalar variable "@AttachmentDetails".Msg 102, Level 15, State 1, Line 2Incorrect syntax near 'END'.
the syntax error that comes of first is from not closing the very first BEGIN with an END, but is related to the various GO statements included.You can't have GO statements inside the stored procedure, it resets the "session" so the variable declaration before a GO statement doesn't get carried through past the go statement. For the same reason "END" appears between go statements, so in context it doesnt reference anything. Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
|