Avatar of debdba

asked on 

Nested SQL with input parameter - use stored procedures? view? function?

I'm a novice to SQL Server but not to SQL and relational databases.  Here's what I want to do but I'm not sure how to approach it in SQL Server:  

I have a stored procedure (call it sp1) that expects a parameter value: @UserID.  The SQL in the stored procedure is somewhat complicated - uses 2 selects UNION'd together.  The stored procedure returns a list of Sites for a specific User.  (Site columns: SiteID, SiteName, etc.)

I'd like to write a stored procedure (sp2) that uses the list of SiteID's out of sp1 in the WHERE clause like this:
SELECT ...... FROM ....
WHERE SiteID IN (SELECT SiteID FROM sp1 WHERE @UserID = valueFedFromProgram)

Someone suggested making sp1 a view, but then someone said a view can't contain a parameter.  Someone else told me that a stored procedure name (sp1) can't be used as if it were a table or viewname.  Another person said use a function but they weren't sure that a function could have a UNION operator.

Hopefully you can see what I'm trying to do and guide me in the right direction on SQL Server.  I'm going to use this in a .NET webpage, C# code.  The database is currently a SQL Server 2000 database running in SQL Server 2005.
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon