Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Stored Procedure are argument for Select where value IN Statement Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:55 PM
Points: 6, Visits: 2
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
Post #1542249
Posted Monday, February 17, 2014 12:56 PM This worked for the OP Answer marked as solution


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 2,763, Visits: 5,908
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).




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1542253
Posted Monday, February 17, 2014 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1542254
Posted Monday, February 17, 2014 1:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:55 PM
Points: 6, Visits: 2
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
Post #1542271
Posted Monday, February 17, 2014 1:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:55 PM
Points: 6, Visits: 2
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
Post #1542275
Posted Monday, February 17, 2014 1:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1542278
Posted Monday, February 17, 2014 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:55 PM
Points: 6, Visits: 2
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
Post #1542279
Posted Monday, February 17, 2014 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:55 PM
Points: 6, Visits: 2
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
Post #1542280
Posted Monday, February 17, 2014 2:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:55 PM
Points: 6, Visits: 2
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
Post #1542281
Posted Monday, February 17, 2014 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1542283
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse