February 17, 2014 at 12:44 pm
I have a stored procedure that ends with
Select columnname from tablename order by ordercolumn
We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.
The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:
Select @userCount = COUNT(*) from permissions where UserID = @userid and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (dbo.sp_FoldersOfFile(@fileID))
The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue. Any help is greatly appreciated.
Thanks!
Jerry
February 17, 2014 at 12:56 pm
You can't use a stored procedure like that.
You have several options and you need to choose what's best for you.
To use the SP you can use INSERT INTO:
CREATE TABLE #TempTableForThis( SingleColumn varchar(255))
INSERT INTO #TempTableForThis
EXEC dbo.sp_FoldersOfFile(@fileID)
Select @userCount = COUNT(*)
from permissions where UserID = @userid and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (SELECT SingleColumn FROM #TempTableForThis)
DROP TABLE #TempTableForThis
Or you could query the table directly:
Select @userCount = COUNT(*)
from permissions where UserID = @userid and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (SELECT columnname FROM tablename)
There are other options but these seem to be the best in this case (or that's my guess with not much information).
February 17, 2014 at 1:00 pm
Another option might be to convert sp_FoldersOfFile into an inline table valued function.
BTW, you should not name your procs with the sp_ prefix. That is reserved for system procs. I abhor prefixes in general but this is one you should avoid regardless of personal preference.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2014 at 1:38 pm
Sean,
Thanks for replying. I don't use prefixes myself but the errors/nonexistent data I was was seeing caused me to try things such as changing names, etc.
Jerry
February 17, 2014 at 1:46 pm
Luis,
I was hoping to have a seperate Stored Procedure to give me just the folder names that I would be able to use in other areas. I had even created another Function to return the values in
('val1', 'val2', 'val3', 'val4')
thinking perhaps dynamic SQL was the way to go but abandoned that in consideration of security concerns.
I will try your solutions here and let you know what I find.
Thanks!
Jerry
February 17, 2014 at 1:52 pm
jerryw 5810 (2/17/2014)
Luis,I was hoping to have a seperate Stored Procedure to give me just the folder names that I would be able to use in other areas. I had even created another Function to return the values in
('val1', 'val2', 'val3', 'val4')
thinking perhaps dynamic SQL was the way to go but abandoned that in consideration of security concerns.
I will try your solutions here and let you know what I find.
Thanks!
Jerry
This is why I suggested an iTVF. Once you build that you have to make a very minor modification to your code.
Select @userCount = COUNT(*) from permissions where UserID = @userid and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (select FolderName from dbo.sp_FoldersOfFile(@fileID))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2014 at 1:53 pm
Luis,
I used the first example you provided. Thanks for the help. I owe you a good lunch if you are ever in Salt Lake City, Utah, USA.
Jerry
February 17, 2014 at 1:58 pm
Sean,
I tried that using the Stored Procedure but couldn't get it to work. Are you saying it would have worked if the logic had been in an inline Table Valued Function? There is other logic behind the scenes that may or may not work in an inline Table Valued Function but I'll give it a try.
Thanks again. Same offer to you (lunch) if you're ever in Salt Lake City.
Jerry
February 17, 2014 at 2:05 pm
Incidentally, here's the code that is being used. The folders table has folderid, foldername, and parentid fields that determine names and heirarchy. When the parentID value is 0, that's the root folder of a project. If there's a better way to do this, I'm always open to learn.
ALTER PROCEDURE [dbo].[FoldersOfFile]
@theFileID int = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @topFolderID int
DECLARE @curParent int
DECLARE @curName varchar(255)
Declare @folderOrd int
select @topFolderID = FolderID from files Where fileID = @theFileID
SET @curParent = @topFolderID
CREATE TABLE #FolderNames (FolderOrder Int, FolderName varchar(255))
Set @folderOrd = 0
Select @curName = folderName from folders Where folderID = @curParent
WHILE @curParent <> 0
BEGIN
insert into #FolderNames(FolderOrder, FolderName) VALUES (@folderOrd, @curName)
Set @folderOrd +=1
select @curParent = parentID, @curName = folderName from folders Where folderID = @curParent
--Select @curName = folderName from folders Where folderID = @curParent
END
Declare @projName varchar(255)
Select @projName = ProjectName from projects Where projectID = dbo.ProjectForFolder(@topFolderID)
Set @folderOrd +=1
insert into #FolderNames(FolderOrder, FolderName) VALUES (@FolderOrd, @projName)
Select FolderName from #FolderNames Order by FolderOrder
Drop Table #FolderNames
END
February 17, 2014 at 2:18 pm
Yeah we can convert to an iTVF. Can you put together ddl for the table and a few rows of sample data. With CREATE TABLE and a few INSERTS I have enough info to build this out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2014 at 2:21 pm
Then I noticed this. "dbo.Projectforfolder(@topFolderID)". We need to have enough definition of this to know what it is doing too since the value from that is returned in the final output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2014 at 2:32 pm
This should give you a similar output to what you have now. As Sean said, more information could help us help you.
CREATE FUNCTION [dbo].[FoldersOfFile](@theFileID int)
RETURNS TABLE AS
RETURN
WITH FolderNames AS(
SELECT ProjectName, folderID, ParentID, 1 AS FolderOrder
FROM folders
CROSS APPLY( SELECT ProjectName
FROM projects
WHERE projectID = dbo.ProjectForFolder(folderID)) x
WHERE folderID = (SELECT FolderID
FROM files
WHERE fileID = @theFileID)
UNION ALL
SELECT f.foldername, f.folderid, f.parentid, FolderOrder + 1
FROM folders f
JOIN FolderNames n ON f.parentid = f.folderid
)
SELECT *
FROM FolderNames;
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply