sp_executesql LEFT JOIN doesn't work

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 2008 Forums

Transact-SQL (2008)

sp_executesql LEFT JOIN doesn't work

Author

Topic

the_pavle
Starting Member

3 Posts

Posted - 2011-04-05 : 02:46:55

Hi I have this problem!!! In My @sql string I put LEFT JOIN, but it seems like it's ignored, and working like JOIN, Why!!!??? Here is my Proc:
ALTER PROCEDURE [dbo].[tsqlMala]
-- Add the parameters for the stored procedure here
@JMBG as nvarchar(50) = null,
@Pol as nvarchar(50) = null,
@BracniStatus as nvarchar(50) = null,
@Adresa as nvarchar(50) = null,
@Grad as nvarchar(50) = null,
@Ime as nvarchar(50) = null,
@Prezime as nvarchar(50) = null,
@Model as nvarchar(50) = null

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @sql nvarchar(MAX),
@paramlist nvarchar(4000)

SET @sql = 'SELECT K.KlijentId, K.JMBG, lookup.Pol.Pol, lookup.BracniStatus.BracniStatus, A.Adresa, G.Grad, I.Ime,
I.Prezime, M.Model
FROM Klijent10k K left join lookup.Pol on K.PolId = lookup.Pol.PolId
left join lookup.BracniStatus on K.BracniStatusId = lookup.BracniStatus.BracniStatusId
left join Adresa10k A on K.KlijentId = A.KlijentId
left join lookup.Grad G on A.GradId = G.GradId
left join Ime10k I on K.KlijentId = I.KlijentId
left join Vozilo10k V on K.KlijentId = V.KlijentId
left join lookup.Model M on V.ModelId = M.ModelId
WHERE 1 = 1'

IF @JMBG IS NOT NULL
SELECT @sql = @sql + ' AND K.JMBG Like @xJMBG + ''%'''
IF @Pol IS NOT NULL
SELECT @sql = @sql + ' AND lookup.Pol.Pol Like @xPol + ''%'''
IF @BracniStatus IS NOT NULL
SELECT @sql = @sql + ' AND lookup.BracniStatus.BracniStatus Like @xBracniStatus + ''%'''
IF @Adresa IS NOT NULL
SELECT @sql = @sql + ' AND A.Adresa Like @xAdresa + ''%'''
IF @Grad IS NOT NULL
SELECT @sql = @sql + ' AND G.Grad Like @xGrad + ''%'''
IF @Ime IS NOT NULL
SELECT @sql = @sql + ' AND I.Ime Like @xIme + ''%'''
IF @Prezime IS NOT NULL
SELECT @sql = @sql + ' AND I.Prezime Like @xPrezime + ''%'''
IF @Model IS NOT NULL
SELECT @sql = @sql + ' AND M.Model Like @xModel + ''%'''

SELECT @paramlist = '@xJMBG nvarchar(50),
@xPol nvarchar(50),
@xBracniStatus nvarchar(50),
@xAdresa nvarchar(50),
@xGrad nvarchar(50),
@xIme nvarchar(50),
@xPrezime nvarchar(50),
@xModel nvarchar(50)'

EXEC sp_executesql @sql, @paramlist, @JMBG, @Pol, @BracniStatus, @Adresa, @Grad, @Ime, @Prezime, @Model
END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-05 : 03:26:42

It's because of your where clause. You're filtering for values, not allowing for nulls, hence the where clause is turning the join into an inner join by excluding the rows where the values are null.

--
Gail Shaw
SQL Server MVP

the_pavle
Starting Member

3 Posts

Posted - 2011-04-05 : 03:59:59

I don't understand, If I Take Select K.KlijentId... that part, and put Where Model like '2%' in separate Query I get What I want, but Why not here. By this he should only add params that I have provided, what do you mean by --where clause is turning the join into an inner join by excluding the rows where the values are null.-- ??? Can you then give me solution??? Thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-05 : 04:27:58

If you have a query like this:

SELECT T1.Col1, T2.Col2 FROM T1 LEFT OUTER JOIN T2 ON <join clause>

Then any rows where there isn't a matching row from T2 will have all the columns from T2 null. That's the essence of an outer join.

If you then add a filter that filters on the second table, that filter will exclude any rows where the column is Null, hence removing all the rows that had values for T1 but not T2

So this:
SELECT T1.Col1, T2.Col2 FROM T1 LEFT OUTER JOIN T2 ON <join clause> WHERE T2.SomeCol = 'A Value'
is completely equivalent to the following, because that where clause does not allow for null values of T2.SomeCol
SELECT T1.Col1, T2.Col2 FROM T1 INNER JOIN T2 ON <join clause> WHERE T2.SomeCol = 'A Value'

If you want to preserve the null rows, the rows that had values in T1, not in T2, you need to add an extra predicate to the where.

SELECT T1.Col1, T2.Col2 FROM T1 LEFT OUTER JOIN T2 ON <join clause> WHERE (T2.SomeCol = 'A Value' OR T2.SomeCol IS NULL)

--
Gail Shaw
SQL Server MVP

the_pavle
Starting Member

3 Posts

Posted - 2011-04-05 : 06:35:29

I solved the problem!!! That wasn't the problem, because I have tried something like that with ISNULL('A Value', ''), Problem was because, On the beginning I set All params to Null, and in If (IF @JMBG IS NOT NULL)... But when I was calling procedure I did it like this
tsqlMala '','','','','','','','2' and in that moment All params are not NULL, and he is doing something wrong, When I changed If (IF @JMBG <> '') Everything works fine!!!

Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources