Query select based on authority - need help

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query select based on authority - need help

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-18 : 11:16:59

Need help completing a query.

Have attached code for my query completed so far.

Scenario:

I need to list a specific job or multiple jobs based upon user selection. If @JobNumber is filled in then select a single job and otherwise multiple jobs.

User will only be able to see job or jobs that falls within his/her authority range like: UserID, JobAuthorityFrom, JobAuthorityTo (a user may have multiple ranges)

Example Job Number authorization by range:

User...... From... To....
TestUser 300500 300599
TestUser 410000 490000
TestUser 526000 530100

I'm connecting to a db2/AS400 (via linked server) to build a temp table (#AuthorityTable) according to user id passed as parm.

Now I need to use the #AuthorityTable in my select and only select jobs that are withing each range.

CREATE PROCEDURE sp_JobInquiry_New  

@JobNumber int,
@UserId char(10)

AS

CREATE TABLE	#AuthorityTable

		(
			user_id char (10),
			busunit_from int,
                                        busunit_to int
	
		                		)
INSERT INTO   		#AuthorityTable (user_id, busunit_from, busunit_to)
SELECT	 	msuser, msmcuf, msmcut
FROM OPENQUERY  	(vgsys400, 'select * from vgisec.f0001')
WHERE		 msuser = @UserId




BEGIN
SELECT   	dbo.Job.CompanyJobId, dbo.Job.Name, 
            		dbo.Region.CompanyRegionID, 
		dbo.Job.Active,
           	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production

FROM               dbo.job 
             	            inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
	            inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
                         left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                         left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                         inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 

WHERE          dbo.Job.CompanyJobId = @JobNumber 
                        and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

GROUP BY    dbo.Job.CompanyJobId, dbo.Region.CompanyRegionID, dbo.Job.Name,  dbo.Job.Active 
END



DROP TABLE  	 #AuthorityTable
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 11:33:01

which of your table has business unit value column?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-18 : 12:03:21

visakh16,

The #AuthorityTable (temptable) contains the busniess unit from / to values and also user id. Business units are in actuality job numbers and range indicates what this particular user is authorized to view. The job number comes from the Job table and the field is called CompanyJobId (this is the one I need to check and make sure it is within the ranges of the authority table). Hope this helps, sorry I was not more clear....

Also please note that the fields: msmcuf, msmcut from the f0001 table are characters(12) and I'm trying to convert them to Integers in my temp table (#AuthorityTable). CompanyJodId field is integer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 14:11:10

quote:
Originally posted by snufse

visakh16,

The #AuthorityTable (temptable) contains the busniess unit from / to values and also user id. Business units are in actuality job numbers and range indicates what this particular user is authorized to view. The job number comes from the Job table and the field is called CompanyJobId (this is the one I need to check and make sure it is within the ranges of the authority table). Hope this helps, sorry I was not more clear....

Also please note that the fields: msmcuf, msmcut from the f0001 table are characters(12) and I'm trying to convert them to Integers in my temp table (#AuthorityTable). CompanyJodId field is integer.

do like this:-

CREATE PROCEDURE sp_JobInquiry_New  

@JobNumber int,
@UserId char(10)

AS

CREATE TABLE	#AuthorityTable

		(
			user_id char (10),
			busunit_from int,
                                        busunit_to int
	
		                		)
INSERT INTO   		#AuthorityTable (user_id, busunit_from, busunit_to)
SELECT	 	msuser, msmcuf, msmcut
FROM OPENQUERY  	(vgsys400, 'select * from vgisec.f0001')
WHERE		 msuser = @UserId




BEGIN
SELECT   	dbo.Job.CompanyJobId, dbo.Job.Name, 
            		dbo.Region.CompanyRegionID, 
		dbo.Job.Active,
           	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production

FROM               dbo.job 
             	            inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
	            inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
                         left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                         left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                         inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         inner join #AuthorityTable at ON
dbo.Job.CompanyJobId>=at.busunit_from  AND dbo.Job.CompanyJobId<=at.busunit_to AND
<table>.Userid=at.user_id             
                         left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 

WHERE          dbo.Job.CompanyJobId = @JobNumber 
                        and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

GROUP BY    dbo.Job.CompanyJobId, dbo.Region.CompanyRegionID, dbo.Job.Name,  dbo.Job.Active 
END
 

DROP TABLE  	 #AuthorityTable
GO

also remember to put correct table name for userid (i dont know which table holds it)

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-18 : 14:17:36

visakh16,

I do not think I have to check the user id, it's taken care of populating the #AuthorityTable, all we need is to check that job number (CompanyJobId) is within range of each record from the #AuthorityTable...... so in all essence I do not think I need to populate the temp table with the user id after all ... I will let you know how it goes, so far, thank you....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 14:22:15

quote:
Originally posted by snufse

visakh16,

I do not think I have to check the user id, it's taken care of populating the #AuthorityTable, all we need is to check that job number (CompanyJobId) is within range of each record for the #AuthorityTable...... so in all essence I do not think I need to populate the temp table with the user id after all ... I will let you know how it goes, so far, thank you....

no probs in that case you dont need the last condition in inner join. try it out and let me know how you got on.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-20 : 10:37:10

That worked great. Thank you very much. May I ask for additional couple of things?

1. After I fill the #AuthorityTable and there are no records, I would like to return to my vb program (possible with a return code I can check on) as this means user is not authorized to anything. In this case I do not need to continue the "Select..."

2. After I do the select, and no records been selected ie no match I would like to have a return code so I can tell the user that no records were found for the selection

3. Also I need to be able to search by job number using wild card like job number = 3505*. This is a text box in my vb program where as the job number in the table is integer. So how would I be able to search using wild card on an integer?

4. Instead of adding up quantities (like: sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production) I would like to return a boolean value indicating whether any quanties were found. I plan to use this value in a gridview as a buttonfield and be able to click and select details showing an additional page.

I have attached the current script:

CREATE PROCEDURE sp_JobInquiry

@JobNumber int,
@UserId char(10)

AS

CREATE TABLE	#AuthorityTable

		(
			busunit_from int,
                                        busunit_to int
			                		)

INSERT INTO   		#AuthorityTable (busunit_from, busunit_to)
SELECT	 	msmcuf, msmcut
FROM OPENQUERY  	(vgsys400, 'select * from vgisec.f0001')
WHERE		 msuser = @UserId


IF @JobNumber <> 0

	BEGIN
		SELECT   		dbo.Job.CompanyJobId, 
					dbo.Job.Name, 
            					dbo.Region.CompanyRegionID, 
					dbo.Job.Active,
           	             				sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             				sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production

		FROM   	            dbo.job 
             	            				inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
	            				inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
                         				left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                        		 		left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                         				inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         				inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         				inner join #AuthorityTable at ON
                         				dbo.Job.CompanyJobId>=at.busunit_from  AND dbo.Job.CompanyJobId<=at.busunit_to 
                         				left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 

		WHERE  	           dbo.Job.CompanyJobId = @JobNumber and (dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

		GROUP BY   	           dbo.Job.CompanyJobId, dbo.Region.CompanyRegionID, dbo.Job.Name,  dbo.Job.Active 


	END
 
ELSE

	BEGIN

		SELECT   		
					dbo.Job.CompanyJobId, 
					dbo.Job.Name, 
            					dbo.Region.CompanyRegionID, 
					dbo.Job.Active,
           	       		      		sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	       		      		sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production

		FROM   	            dbo.job 
          		   	            		inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
			            		inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
           		              		left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
          		               		left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
            			             		inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
               		          		inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                 		        		inner join #AuthorityTable at ON
                    		     		dbo.Job.CompanyJobId>=at.busunit_from  AND dbo.Job.CompanyJobId<=at.busunit_to 
                    		     		left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 

		WHERE  	       (dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

		GROUP BY   	       dbo.Job.CompanyJobId, dbo.Region.CompanyRegionID, dbo.Job.Name,  dbo.Job.Active 


		END

DROP TABLE  	 #AuthorityTable
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-20 : 13:27:44

quote:

Originally posted by snufse

That worked great. Thank you very much. May I ask for additional couple of things?

1. After I fill the #AuthorityTable and there are no records, I would like to return to my vb program (possible with a return code I can check on) as this means user is not authorized to anything. In this case I do not need to continue the "Select..."
see (1) below
2. After I do the select, and no records been selected ie no match I would like to have a return code so I can tell the user that no records were found for the selection
See (2) below
3. Also I need to be able to search by job number using wild card like job number = 3505*. This is a text box in my vb program where as the job number in the table is integer. So how would I be able to search using wild card on an integer?
See (3) below
4. Instead of adding up quantities (like: sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production) I would like to return a boolean value indicating whether any quanties were found. I plan to use this value in a gridview as a buttonfield and be able to click and select details showing an additional page.
See (4) below
I have attached the current script:
Also no need of IF...ELSE i have merged them into 1

CREATE PROCEDURE sp_JobInquiry

@JobNumber int,
@UserId char(10)

AS

CREATE TABLE	#AuthorityTable

		(
			busunit_from int,
                                        busunit_to int
			                		)

DECLARE @Rows int

INSERT INTO   		#AuthorityTable (busunit_from, busunit_to)
SELECT	 	msmcuf, msmcut
FROM OPENQUERY  	(vgsys400, 'select * from vgisec.f0001')
WHERE		 msuser = @UserId

IF (SELECT COUNT(*) FROM #AuthorityTable)=0--(1)
 RAISERROR  ('your error message here', 16, 1)


SELECT   		dbo.Job.CompanyJobId, 
					dbo.Job.Name, 
            					dbo.Region.CompanyRegionID, 
					dbo.Job.Active,
           	             				max(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then 1 ELSE 0 END) AS material,
         	             				max(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN 1 ELSE 0 END) AS production--(4)

		FROM   	            dbo.job 
             	            				inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
	            				inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
                         				left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                        		 		left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                         				inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         				inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         				inner join #AuthorityTable at ON
                         				dbo.Job.CompanyJobId>=at.busunit_from  AND dbo.Job.CompanyJobId<=at.busunit_to 
                         				left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 

WHERE  	           (dbo.Job.CompanyJobId LIKE CAST(@JobNumber AS varchar(10)) + '%'
OR @JobNumber=0)--(3)
and (dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')

		GROUP BY   	           dbo.Job.CompanyJobId, dbo.Region.CompanyRegionID, dbo.Job.Name,  dbo.Job.Active 

SET @Rows=@@ROWCOUNT--(2)
IF @Rows=0
 RAISERROR  ('No matching records', 16, 1)

	
DROP TABLE  	 #AuthorityTable
GO

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-20 : 18:34:45

visakh16, so far thank you for execellent assistance ... I will let you know how I come within a day or so ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 18:58:54

For (1), you can use @@ROWCOUNT as well.

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-21 : 18:13:38

visakh16, we tested the query today and it works great. Good job.

May I ask you (again) for a couple of additional things:

Users would like to:

1. Search on job name ie "dbo.Job.Name" so I need to pass in additional parm for this
@Text char(20)

2. Search on Date range from/to ie "dbo.Job.ChangeDate", additional 2 parms
@DateFrom datetime,
@DateTo datetime

Is it possible to incorporate all of this in "one" select?
User will fill in either Job Number or Search Text or Date Range From/To

Here is my present script"

CREATE PROCEDURE sp_Vecellio_JobInquiry

@JobNumber int,
@UserId char(10)

AS

CREATE TABLE	#AuthorityTable

		(
			busunit_from int,
                                        busunit_to int
			                		)

DECLARE @Rows int

INSERT INTO   		#AuthorityTable (busunit_from, busunit_to)
SELECT	 	msmcuf, msmcut
FROM OPENQUERY  	(vgsys400, 'select * from vgisec.f0001')
WHERE		 msuser = @UserId

IF (SELECT COUNT(*) FROM #AuthorityTable)=0
 RAISERROR  ('You are not authorized to any selections', 16, 1)


SELECT   		dbo.Job.CompanyJobId, 
					dbo.Job.Name, 
            					dbo.Region.CompanyRegionID, 
					dbo.Job.Active,
           	             				max(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then 'Yes' ELSE 'No' END) AS material,
         	             				max(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN 'Yes' ELSE'No' END) AS production,
                                                                  max(case dbo.EquipmentLaborEvent.Hours WHEN  0 THEN 'No' ELSE'Yes' END) AS equipment,
                                                                  max(case dbo.EmployeeLaborEvent.Hours WHEN  0 THEN 'No' ELSE'Yes' END) AS labor

		FROM   	            dbo.job 
             	            				inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
	            				inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
                         				left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                        		 		left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
                         				inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         				inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
					left outer join dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EventGuid = dbo.Event.EventGuid
					left outer join dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid
                         				inner join #AuthorityTable at ON
                         				dbo.Job.CompanyJobId>=at.busunit_from  AND dbo.Job.CompanyJobId<=at.busunit_to 
                         				left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 

WHERE  	                                      (dbo.Job.CompanyJobId LIKE CAST(@JobNumber AS varchar(10)) + '%'
                                                                OR @JobNumber=0)--(3)
                                                                and (dbo.SourceType.CompanySourceTypeId = 'PR'
                                                                or dbo.SourceType.CompanySourceTypeId = 'MA')

GROUP BY   	                                     dbo.Job.CompanyJobId, dbo.Region.CompanyRegionID, dbo.Job.Name,  dbo.Job.Active 

SET @Rows=@@ROWCOUNT
IF @Rows=0
 RAISERROR  ('No matching records', 16, 1)

	
DROP TABLE  	 #AuthorityTable
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 18:36:07

[code]CREATE PROCEDURE dbo.sp_Vecellio_JobInquiry
(
@JobNumber INT,
@UserID VARCHAR(10),
@Text VARCHAR(20) = NULL,
@DateFrom DATETIME = NULL,
@DateTo DATETIME = NULL
)
AS

SET NOCOUNT ON

CREATE TABLE #AuthorityTable
(
busunit_from INT,
busunit_to INT
)

INSERT #AuthorityTable
(
busunit_from,
busunit_to
)
SELECT msmcuf,
msmcut
FROM OPENQUERY(vgsys400, 'SELECT * FROM vgisec.f0001')
WHERE msuser = @UserID

IF @@ROWCOUNT = 0
BEGIN
RAISERROR('You are not authorized to any selections.', 16, 1)
RETURN
END

SELECT dbo.Job.CompanyJobID,
dbo.Job.Name,
dbo.Region.CompanyRegionID,
dbo.Job.Active,
MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'MA' THEN 'Yes' ELSE 'No' END) AS Material,
MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'PR' THEN 'Yes' ELSE'No' END) AS Production,
MAX(CASE dbo.EquipmentLaborEvent.Hours WHEN 0 THEN 'No' ELSE 'Yes' END) AS Equipment,
MAX(CASE dbo.EmployeeLaborEvent.Hours WHEN 0 THEN 'No' ELSE 'Yes' END) AS Labor
FROM dbo.Job
INNER JOIN dbo.Event ON dbo.Event.JobGuid = dbo.Job.JobGuid
INNER JOIN dbo.ProductionEvent ON dbo.ProductionEvent.EventGuid = dbo.Event.EventGuid
LEFT JOIN dbo.Product ON dbo.Product.ProductGuid = dbo.ProductionEvent.ProductGuid
LEFT JOIN dbo.Item ON dbo.Item.ItemGuid = dbo.Event.ItemGuid
INNER JOIN dbo.Source ON dbo.Source.SourceGuid = dbo.ProductionEvent.SourceGuid
INNER JOIN dbo.SourceType ON dbo.SourceType.SourceTypeGuid = dbo.Source.SourceTypeGuid
LEFT JOIN dbo.EquipmentLaborEvent ON dbo.EquipmentLaborEvent.EventGuid = dbo.Event.EventGuid
LEFT JOIN dbo.EmployeeLaborEvent ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid
INNER JOIN #AuthorityTable AS at ON at.busunit_from <= dbo.Job.CompanyJobId
AND at.busunit_to >= dbo.Job.CompanyJobId
LEFT JOIN dbo.Region ON dbo.Region.RegionGuid = dbo.Job.RegionGuid
WHERE dbo.SourceType.CompanySourceTypeID IN ('PR', 'MA')
AND (dbo.Job.CompanyJobID LIKE CAST(@JobNumber AS VARCHAR(10)) + '%' OR @JobNumber = 0 OR @JobNumber IS NULL)
AND (dbo.Job.Name LIKE '%' + @Text + '%' OR @Text IS NULL)
AND (dbo.Job.ChangeDate >= @DateFrom OR @DateFrom IS NULL)
AND (dbo.Job.ChangeDate <= @DateTo OR @DateTo IS NULL)
GROUP BY dbo.Job.CompanyJobID,
dbo.Region.CompanyRegionID,
dbo.Job.Name,
dbo.Job.Active

IF @@ROWCOUNT = 0
RAISERROR('No matching records.', 16, 1)

DROP TABLE #AuthorityTable[/code]

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-21 : 19:31:58

Peso, thank you, I will check it out.

Question: Since you do not have any 'if" in the "where" portion (like: if job number passed, if date from/to passed)does this mean that it will handle any parms passed in? Not sure if I am clear in my wording...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 19:41:02

The different OR's in the WHERE clause will take care of optional parameters.
If you don't want to use an optional parameter, just pass them as null or don't even bother to pass them since they are predeclared as NULL.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources