SQL Server Stored Procedure not workign correctly

Transact-SQL
1

hi,

I have 2 tables

Artist

p/k - ArtistId

StageName -nvarchar

Appoved - bit

Table 2

Playlist

TrackId

Name

on the aspx page a user can enter value in text box. on button click it calls
SQL stored procedure and binds result to gridview.

for example they seach for artist name - the query should check artist table
and the playlist table to see if the Passed in Parameter corresponds. If it
does and Artist is Approved=1 - then I want to include in results.

If user is Approved=0 - then I dont want to show the artist even
if there is a match with parameter

My problem is that an artist in Artist table with approved =0 IS SHOWING UP IN RESULTS

here is my code

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GridProcedure]

@Name nvarchar(40)

AS

SET NOCOUNT ON;

SELECT DISTINCT

a.ArtistId,
a.FirstName,
a.LastName,
a.StageName,

a.Approved,

a.Updated,
C.NameEn as CountryName,
L.City,

(SELECT TOP (1) i.FilePath FROM ProfileImages as i WHERE i.ArtistId = a.ArtistId) AS ImagePath
FROM Artist as a
Left outer join PlayMeLocation L On L.ArtistId=a.ArtistId
Inner join Country C on C.CountryId=L.CountryId
WHERE
a.StageName LIKE '%' + @Name + '%' OR
exists (SELECT 1 FROM PlayList as p
WHERE
p.ArtistId = a.ArtistId AND
p.Name LIKE '%' + @Name + '%')
AND Approved=1

thanks for any help

2

Your OR is the problem. You need to add parenthesis around it

WHERE (a.StageName LIKE '%' + @Name + '%'
	OR EXISTS (
		SELECT 1
		FROM PlayList AS p
		WHERE p.ArtistId = a.ArtistId
			AND p.NAME LIKE '%' + @Name + '%'
		))
	AND Approved = 1
2 Likes
3

You need couple of extra brackets to account for the operator precedence.

WHERE   
		(
			a.StageName LIKE '%' + @Name + '%'
			OR EXISTS 
			( 
				SELECT  1
				FROM    PlayList AS p
				WHERE   p.ArtistId = a.ArtistId
						AND p.Name LIKE '%' + @Name + '%' 
			)
		)
        AND Approved = 1
2 Likes
4

@JamesK, Great minds think alike. :smile:

1 Like
5

thank you very much....seems to have done the trick!

finally if i set the gridview to display 50 results at a time

If there query returns 200+ is this existing code efficient to handle
that

thank you again

6

Indeed, they do, fellow member of mutual admiration society :smile:

7

You should not have any SQL Server performance issues with a few hundred records. Whether your web page/grid view can handle that is something which I don't know the answer to.

1 Like
8

And, btw, for clarity and to avoid potential future errors, always alias every column reference, viz:

AND **a.**Approved=1

1 Like