Unused params in select - SQL Server Forums

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

Unused params in select

Author

Topic

MarkMeier
Starting Member

2 Posts

Posted - 2015-05-04 : 10:35:57

Dear all
I'm pretty new to T-SQL and busy with an Migration of an Access frontend/backend db to a Frontend/SQLBackend. I have used many filters in Access 2010 where the user can filter the records in a form. Now I have migrated one of them to SQL like:

ALTER PROCEDURE [dbo].[spWarenbewegungFilter]
@PID REAL=0,
@ArtikelName NVARCHAR (100)='%',
@ArtikelBeschreibung NVARCHAR (100)='%',
@MitarbeiterNr int=0,
@Type NVARCHAR (100)="%",
@NameFirma NVARCHAR(100)='%',
@FromDate DATETIME2 ='19900101',
@ToDate DATETIME2 ='21000101',
@Commision NVARCHAR (200)='%'

AS
SET NOCOUNT ON;

SELECT WarenbewegungTab.*, WarenbewegungArtikelTab.*
FROM WarenbewegungTab INNER JOIN WarenbewegungArtikelTab ON WarenbewegungTab.RecordNr = WarenbewegungArtikelTab.WarenbewegungNr
WHERE

WarenbewegungArtikelTab.PID = @PID AND
WarenbewegungArtikelTab.ArtikelName LIKE @ArtikelName AND
WarenbewegungArtikelTab.ArtikelBeschreibung LIKE @ArtikelBeschreibung AND
WarenbewegungTab.Typ LIKE @Type AND
WarenbewegungTab.ProjektNummer LIKE @Commision AND
WarenbewegungTab.NameFirma LIKE @NameFirma

ORDER BY WarenbewegungTab.WarenbewegungNummer DESC;

PRINT @@rowcount

GO

you see, I use the wildacrd char (%)if one of the string params is not given. The Problem is with an Integer param (see first line of WHERE clause). I have to ommit this line If I want have all "PID's" in the select.

Does any other char work in that field like "%" for a string?
Or is there another method available which I can use in such cases (i.e. conditional compiling or whatever)

Of course I can check the integer values whether they are NULL or not and write 4 different Selects with different WHERE clauses.

Thanks for any hints

BR
Mark

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-04 : 11:12:14

some problems here:


@PID REAL=0
...then later
WarenbewegungArtikelTab.PID = @PID

Comparing reals is risky, due to rounding. But then is PID a real or an integer? If an integer, change the declaration to @PID int = 0.

The LIKE operator is only for strings. For numbers you can use a variety of operators: <, >, =, <=, >=, <>, IS NULL, BETWEEN
You can probably do it in one query (not four!). Post some sample data and desired results. Use this link as a guideline:

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Gerald Britton, MCSA
Toronto PASS Chapter

MarkMeier
Starting Member

2 Posts

Posted - 2015-05-04 : 12:28:40

Hi Gerald
Yes, you're right. Real is a bit risky to compare. I found a solution with an additional inserted criteria like:

DECLARE
@PIDx REAL
...

IF @PID=0 SET @PIDx=-1 ELSE SET @PIDx=9999999999;
.....
WHERE
(WarenbewegungArtikelTab.PID = @PID OR WarenbewegungArtikelTab.PID > @PIDx) AND
.....

Thank you for the Guideline hint how to post Topics. I'm going trough and give my best next time.

BR
Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources