How to get sp to give all users

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

How to get sp to give all users

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-06-21 : 10:58:33

In executing this sp, you see I enter a value for @USERID. If I leave this null, I get no results. But I want to see all the data for all users how would I get that?

USE [PRO2]
GO

DECLARE @return_value int

EXEC @return_value = [PC].[NOT_GET_NOTES]
@FACILITYKEY = N'ADLU',
@SMRN = NULL,
@PDISCIPLINE = NULL,
@CREATEDBY = NULL,
@PKEYWORD = NULL,
@APPKEY = -1,
@NOTETYPE = 1,
@DATEFROM = N'06-01-2005',
@DATETHRU = N'06-22-2012',
@UNITSTR = NULL,
@SHIFTCODE = -1,
@USERID = N'ADMIN',
@ISTATUS = 0,
@IFIELDKEY = -1

SELECT 'Return Value' = @return_value

GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-21 : 11:55:53

You would need to inspect the code for the stored proc [PC].[NOT_GET_NOTES] to see what value you need to send in for @USERID for it to return data for all users. It may very well be that it is programmed such that it will work only if you give a valid user id.

If you have the ability to change the stored proc, you would want something like this in the WHERE clause


WHERE
   -- OTHER CONDITIONS IN WHERE CLAUSE
   (@USERID = UserId OR @USERID IS NULL)
   -- OTHER CONDITIONS IN WHERE CLAUSE

This will work, but it may have some performance implications. If you run into that take a look at this blog: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources