how to select single column from store proc?

Hi Experts,
I have a store procedure that accepts a string and returns a table, now I would like to select only one column from that store procedure, like the following
Select distinct EmployeeID From dbo.proc_FilterOrientationNotes 'Orientations.FacilityID = 4422'

Open in new window

however I am receiving the following error "Incorrect syntax near 'Orientations.FacilityID = 4422'."
How can I accomplish that?
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how's your SP dbo.proc_FilterOrientationNotes looks like? will it return an output value or a select statement?
bfuchsAuthor Commented:
Hi,
Output values
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try like (assuming 2nd parameter to output the value):
...
declare @youroutput varchar(30);
Exec dbo.proc_FilterOrientationNotes 'Orientations.FacilityID = 4422'  , @youroutputOUTPUT;
...

Open in new window


further reading:
Return Data from a Stored Procedure
https://msdn.microsoft.com/en-us/library/ms188655.aspx

>>post the codes for SP dbo.proc_FilterOrientationNotes when necessary,
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vikas GargAssociate Principal EngineerCommented:
Hi,

You can try something like this also

Declare @tablevar table(col1,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1 FROM @tablevar
Vitor MontalvãoMSSQL Senior EngineerCommented:
It misses an ALIAS and the WHERE clause?
Select distinct Orientations.EmployeeID 
From dbo.proc_FilterOrientationNotes AS Orientations
WHERE Orientations.FacilityID = 4422

Open in new window

bfuchsAuthor Commented:
Hi Experts,

Ryan is right, perhaps I should have post the SP code in order to clarify the question.

Here it goes..

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotes]
(@strWhere varchar(5000))
AS 
declare @tmp varchar(5000)
declare @strWhere2 varchar(5000)

	set @tmp='SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, 
                      dbo.Facilitiestbl.Name AS FacilityName, dbo.view_OrientationLastNote.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + '' '' + dbo.Employeestbl.FirstName As EmpName
FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
                      dbo.view_OrientationLastNote ON dbo.Orientations.ID = dbo.view_OrientationLastNote.OrientationID LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID'


IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where Orientations.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end

	if charindex('order by',@tmp)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
execute (@tmp)

Open in new window


So what it does is, it accepts a parameter which is used to apply the filter to the results returned (and the where is being added by the store proc, therefore is not necessary to send).

Now since this SP returns a recordset of rows and multiple columns, I need a way to only get a single column.

Thanks,
Ben
bfuchsAuthor Commented:
Actually from what Ryan is commenting, I assume its possible to modify the SP to have a logic like, if second parameter is null return all columns, otherwise only return parameters specified in param2..

However wondering if there is a way to accomplish that without modifying the store proc?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>wondering if there is a way to accomplish that without modifying the store proc?
no... in general, if we need to customize the output, it means we need to modify the sp accordingly.

I'm not too sure if I get what you want... but need some clarifications from you:
1. >>now I would like to select only one column from that store procedure
So, it that mean to return a single column with a single value OR is that possible to return a single column with multiple values?

2. it seems that your SP was originally designed to return the record(s), but not the output values (as mentioned in your comment ID: 40945268), can you reconfirm what you need to return from your SP?
bfuchsAuthor Commented:
@Ryan,

1-single column with multiple values.
2-
will it return an output value or a select statement?
Pardon, I guess was not sure what you were calling output values, I meant records as opposed to just a select statement

Thanks,
Ben
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, perhaps you can try something like this to accept optional parameters in a SP, and see if this fulfill your requirement?

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotes]
(
	@strWhere varchar(5000) = NULL,
	@strColName varchar(5000) = NULL
)
AS 
	declare @tmp varchar(5000)
	declare @strWhere2 varchar(5000)

	SET NOCOUNT ON;
	
    	IF @strColName IS NULL
    	Set @strColName = 'TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR '+
                      	'Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID,  '+
                      	'dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered)  '+
                      	'AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial,  '+
                      	'dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing,  '+
                      	'dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays,  '+
                      	'dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title,  '+
                      	'dbo.Facilitiestbl.Name AS FacilityName, dbo.view_OrientationLastNote.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility,  '+
                      	'dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email,  '+
                      	'dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation, '+	
                 	'FROM dbo.Orientations INNER JOIN '+
                 	'dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN '+
                      	'dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN '+
                      	'dbo.view_OrientationLastNote ON dbo.Orientations.ID = dbo.view_OrientationLastNote.OrientationID LEFT OUTER JOIN '+
                      	'dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN '+
                      	'dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID '

	set @tmp='SELECT '+ @strColNam

	IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

	IF @strWhere IS NULL set @tmp = @tmp + ' where Orientations.EmployeeID = 3' 

	if @strWhere = 'Long'
	begin
		set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
		set @tmp = @tmp + ' where ' + @strWhere2 
	end

	if charindex('order by',@tmp)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'

	execute (@tmp)

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
but the part:

>>
if @strWhere = 'Long'
	begin
		set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
		set @tmp = @tmp + ' where ' + @strWhere2 
	end

Open in new window


looks a bit weird to me...

what field to be compared with [value] from table: ProgrammingSettings ?
bfuchsAuthor Commented:
@Ryan,

1- I will test your code & let you know, just one question, what is the syntax of if ... elseif..else..end if, in t-sql language?

2- The reason behind that 'long' code is as follows, this stored proc is being called by an Access 2000/3 FE app, and for some reason there is a limit of 250 characters that can be transferred from the FE to the sql BE, therefore in case the @strWhere param is longer than that, I must save it in a table and have the SP take it from there..

Thanks,
Ben
bfuchsAuthor Commented:
Hi Ryan,

Just had one more question, as the original post stated, I need it distinct "Select distinct EmployeeID", let me know if this can easily be modified, if not I will open a new post to address that,

Thanks.
Ben
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>what is the syntax of if ... elseif..else..end if, in t-sql language?
You can try use CASE WHEN clause.

CASE (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms181765.aspx

>>I need it distinct "Select distinct EmployeeID", let me know if this can easily be modified
since we are passing "field name itself" in the SP, if you would like to "select distinct EmployeeID", i guess you probably can try like:

Exec dbo.proc_FilterOrientationNotes 'Orientations.FacilityID = 4422'  ,  'distinct EmployeeID';

try customize your SP if necessary

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks Ryan, I dont think will have a chance to test that before next week, however your answer looks like making sense.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.