Help on Calling a function in stored procedure

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help on Calling a function in stored procedure

Author

Topic

bplvid
Starting Member

45 Posts

Posted - 2012-03-30 : 10:28:36

I'm doing a file upload and I store the path of the file in my sqlserver.On inserting I need to check for the filename with extension already exists.For that I've a function that returns the filename from the path and I need that function to be called in my stored procedure to check for it exists before doing insert.

I've my function named GetFileName.Can someone help me how to handle this function in my stored procedure,I've provided my incomplete stored procedure below.In my below SP i don't know what shd i have in my dotted place where i don't have any specific column for filename i just have my path stored.
CREATE PROCEDURE InsertImageResource
(

@userName nvarchar(256),

@DirectoryPathtoFile nvarchar(256)

)

AS
BEGIN

declare @filename nvarchar(max);

SELECT @filename = dbo.GetFileName(@DirectoryPathtoFile);

if exists(select r.username,
d.DirectoryPathtoFile,
v.DirectoryPathtoFile,
a.DirectoryPathtoFile,
i.DirectoryPathtoFile,
o.DirectoryPathtoFile,
db.DirectoryPathtoFile from resources r,documentitems d,videoitems v,audioitems a,imageitems i,otheritems o,databasefileitems db
where (r.username = @username and ------------- = @filename));

END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-30 : 11:36:51

which table currently has existing file details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-03-30 : 12:39:16

quote:
Originally posted by visakh16

which table currently has existing file details?

SQL Server MVP
http://visakhm.blogspot.com/

I'm doing a fileupload,I've 1 table to hold the title and description of the file and another table to hold the path of the file based on the extension.Before inserting I need to check for the title and uploaded filename if already exists.Since i don't have a column for filename,i created a function to get the filename from the path.Another important thing here is I've multiple tables with same column for different filetype.How can i handle the insert based on fileextension in sqlserver.I've the table schema below.

Usertable - Parent Table
userid(PK),
Username,
Upload date

Imagetable - Table that hold image file path
Imageid(PK)
userid(FK)
DirectoryPathofFile
Author
Summary

DocumentTable - Table that hold Document file path
Docid(PK)
userid(FK)
DirectoryPathofFile
Author
Summary

Hope this helps

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-30 : 13:03:27

Is it to late to redesign this?

Make 1 table, add a column called filetype, and break out the filename from the path and a fn column

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-03-31 : 19:53:01

Someone help me understand this,I've got solutions to insert into two tables having same columns but my issue here is I've 2 different tables each holding different columns.I need to insert into my 1st(parent) table and just insert the pk of my table1 into my 2'nd table.
My parent table has columns id (pk),Name,description and my table2 has columns table1-id(FK),id(PK),directorypath,upload date.I need to insert into table1 values name and description and just insert the tabel1 -ID(FK),directorypath,upload date into tabel 2.Help me understand is it possible using my below Stored procedure (Check the insert part)

I receive a error 'INcorrect syntax near OUTPUT' .My query is as follows.I may be missing something.Please help on it.

CREATE PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7)

)
AS

declare @FileType nvarchar(max),
@FileName nvarchar(max),

@Proceed bit

select @FileName = dbo.GetFileName (@DirectoryPathToFile , '\')

select @FileType = reverse(SUBSTRING(REVERSE(@FileName), 0, CHARINDEX('.',REVERSE(@FileName))))

SELECT @Proceed = CASE WHEN EXISTS (select 1 from ImageItems where DirectoryPathToFile = @DirectoryPathToFile) THEN 0
WHEN EXISTS (SELECT 1 from DocumentItems where DirectoryPathToFile = @DirectoryPathToFile) THEN 0
WHEN EXISTS (SELECT 1 FROM VideoItems where DirectoryPathToFile = @DirectoryPathToFile) THEN 0 ELSE 1 END

IF @Proceed = 1
BEGIN
IF @FileType IN ('doc','pdf','txt')
INSERT INTO dbo.Resources(ResourceName, DescriptionOfResource) VALUES
(@ResourceName, @Description)
OUTPUT Inserted.ResoureDatabaseID,@DirectoryPathToFile, @UploadDate INTO
dbo.DocumentItems VALUES (ResoureDatabaseID,DirectoryPathToFile, UploadDate)
END

Note: The table schema was not created by me and i'm not allowed to make any changes to the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-31 : 20:15:54

the last insert should be like:-


...

INSERT INTO dbo.Resources(ResourceName, DescriptionOfResource) 
OUTPUT Inserted.ResoureDatabaseID,@DirectoryPathToFile, @UploadDate INTO 
dbo.DocumentItems
VALUES (@ResourceName, @Description) 
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-03-31 : 21:52:54

Thanks for the suggestion.I've done the changes and executed the SP.I get the below error.Please help me fix this error.I'm stuck with this for long hrs.

Invalid column name 'ResoureDatabaseID'.

The target table 'dbo.DocumentItems' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK__Documenti__Resou__17C286CF'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-31 : 22:21:39

seems like you've foreignkey constraint in table dbo.DocumentItems

why not dump the results into a temp table/table variable and then use it insert to dbo.DocumentItems

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-03-31 : 23:32:36

quote:
Originally posted by visakh16

seems like you've foreignkey constraint in table dbo.DocumentItems

why not dump the results into a temp table/table variable and then use it insert to dbo.DocumentItems

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Just would like to learn more about the above query.Is it not possible to use the above query if the table 2 has a foreign key? Yes my table 2 has foreign key(table1 PK), table 2 primary key.

please explain.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-31 : 23:59:29

yes. you cant as specified by the error message.
So solution is to dump result to temp table and populate from there

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-01 : 23:27:57

My Table schema is shown below.On executing my SP i get error msg saying USerid is Null.How do I insert my Userid (foreign key) .Please help me with my SP.

Resource Table

ResourceDatabaseID (PK)
Userid (FK)
Resourcename
Descriptionofresource

DocumentItem Table

DocumentItemID (PK)
ResourceDatabaseID (FK)
DirectoryFilePath
Uploaddate

I'm trying the below stored procedure.

ALTER PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7),
@resourcestatus nvarchar(256),
@Username nvarchar(256)

)
AS

declare @FileType nvarchar(max),
@FileName nvarchar(max),
@Proceed bit,
@resourceid uniqueidentifier,
@UserId uniqueidentifier

set @UserId =(select UserId from aspnet_Users where UserName= @Username)

IF @Proceed = 1
BEGIN
IF @FileType IN ('doc','pdf','txt')

insert into Resources
(
ResourceDatabaseID,
UserId,
ResourceName,
DescriptionOfResource,
ResourceStatus
)
values ( NEWID(),@UserId,@ResourceName,@Description,@resourcestatus)

SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)
insert into Documentitems
(
ResourceDatabaseID,
DocumentitemDatabaseID,
DirectoryPathToFile,
UploadDate
)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 00:21:03

how are you calling the SP?
first check if you've uer record existing for username passed

select UserId from aspnet_Users where UserName= @Username

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-02 : 09:57:28

quote:
Originally posted by visakh16

how are you calling the SP?
first check if you've uer record existing for username passed

select UserId from aspnet_Users where UserName= @Username

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes,I'm using asp.net membership and I've username and Userid.All i need is to grab the session which holds the used id and insert in my query which is a foreign key in my resources table.

My resources table has Userid as Foreign key and my documenttable(table 2) has redourcesdatabaseid as foreginkey.
Hope this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 13:58:17

then you wont get null for @Userid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-03 : 14:56:54

quote:
Originally posted by visakh16

then you wont get null for @Userid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I"m back here.When I execute my SP I get it inserted but it throws me msg saying
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." on line56

insert into Resources(ResourceDatabaseID, UserId,ResourceName,DescriptionOfResource,ResourceStatus)
values (@resourceid,@UserId,@ResourceName,@Description,@resourcestatus)

SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)

insert into Documentitems(ResourceDatabaseID,DocumentitemDatabaseID,DirectoryPathToFile,UploadDate)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
end

I've tried to fix it but no luck with that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:23:26

the error is because below statement is returning more than 1 row

SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources