September 19, 2007 at 9:49 am
Hello All,
just to let you know in starting out im pretty new with SQL stored procedures. Im trying to write a very simple procedure that will add a record to a table and im getting an error and i dont understand why. I have pasted the procedure below as it will help understand whats wrong. Can someone please explain to me what im doing wrong. im not just looking for it to be fixed, i would like to know what im doing that isnt right so i can learn as well.
Thanks in advance,
John
set
ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER
PROCEDURE [dbo].[sp_AddAttachment]
@au_FileName varchar(40) = 'DefaultData.doc',
@au_FileLocation varchar(100) = 'c:\inetpub\Nuke\DefaultLocation\',
@au_PlanID int = 9999,
@au_PlanActionID int = 9999,
@au_AttachedTo char(1) = 'X',
@au_DocumentID int = 999
AS
-- Take @au_DocumentID which is passed in from the ASPX page and find the name
-- of the file it refers to
DECLARE
@name_holder varchar(100);
SET @name_holder=(SELECT DocumentNameInternal FROM xsdl_Document WHERE DocumentID = @au_DocumentID)
-- get the folderpath from xsld_Folder based on the folderID from xsld_Document
-- based off the @au_DocumentID
DECLARE
@path_holder varchar(255);
SET @path_holder=(select f.FolderPath, d.DocumentID from xsdl_Folder f, xsdl_Document d where exists(f.FolderID = d.FolderID and d.DocumentID = @au_DocumentID))
*** here is where the problem lies. Im trying to get the file path from another table in the database and add it into the table below. When i run this query in the query panel it runs no problem but here for some reason i cant get it to work even when @au_DocumentID has the correct value.*** why is this syntax invalid and what can i do to make it valid?***
INSERT
INTO dbo.cnAttachments (
FileName,
FileLocation
,
PlanID,
PlanActionID
,
Attached_To
)
VALUES
(
@name_holder
,
@path_holder
,
@au_PlanID
,
@au_PlanActionID
,
@au_AttachedTo
)
September 19, 2007 at 10:04 am
SET @path_holder=(select f.FolderPath, d.DocumentID from xsdl_Folder f, xsdl_Document d where exists(f.FolderID = d.FolderID and d.DocumentID = @au_DocumentID))
Your select returns 2 values. You cannot set 2 values into 1 variable.
September 19, 2007 at 10:10 am
lol thanks man sorry for the dumb ass question. If SQL error messages were only in plan english i wouldnt have just made an ass of myself at this moment lol at a later date im sure but i coulda saved this one.
thanks,
John
September 19, 2007 at 10:11 am
1. You are etrying to assign 2 values (f.FolderPath, d.DocumentID) while you need only the first one.
2. Correlated sub-query is written incorrect
Try to do this:
select FolderPath
from xsdl_Folder f
where exists(
select folderid
from xsdl_Document
where FolderID = f.FolderID
and DocumentID = @au_DocumentID)
Let me know whether it works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply