procedure in sql server (getting problem )?

Question

hi,

In my application i am implementing search, it is like when user enter text separated with comma in a text box, search result will be displayed. This is my requirement and for this i write a procedure for this it is like this.......

create procedure [dbo].[videos_getSearch](@searchstring AS VARCHAR(1000)) 
AS 
BEGIN  

DECLARE @CurNumber INT, @CommaIndex INT, @strSearch varchar(3000),@str varchar(50) 
declare @strQuery varchar(1000),@result varchar(5000) 
declare @sql varchar(2000) 
DECLARE @CurNumStr VARCHAR(20) 

  set @strSearch = '' 

 WHILE LEN(@searchstring) > 0 
 BEGIN 

     SET @CommaIndex = CHARINDEX(',', @searchstring) 
     IF @CommaIndex = 0 SET @CommaIndex = LEN(@searchstring)+1 
     SET @CurNumStr = SUBSTRING(@searchstring, 1, @CommaIndex-1) 
     SET @searchstring = SUBSTRING(@searchstring, @CommaIndex+1, LEN(@searchstring)) 

    BEGIN 

        set @str = ltrim(rtrim(@CurNumStr)) 
        if LEN(@searchstring)> 0 
          begin 
            set @strSearch = @strSearch + '''%' + @str +'%'''+'or tags like'  
          end 
        else 
          begin 
            set @strSearch = @strSearch + '''%'+ @str +'%''' 
          end 
    END 
 END 



    set @sql='SELECT  phot_album.albumid,phot_album.tags,phot_album.albumtitle,phot_album.coverphoto,trailor_creation.trailorid,trailor_creation.tags,trailor_creation.movie,trailor_creation.images,video_upload.videoid,video_upload.videotitle,video_upload.videofile,video_upload.tags FROM   phot_album   INNER JOIN   trailor_creation ON phot_album.tags = trailor_creation.tags INNER JOIN   video_upload  ON phot_album.tags = video_upload.tags where (phot_album.tags)  like  '+@strSearch  +' or  (trailor_creation.tags)  like '+@strSearch  +' or  (video_upload.tags) like '+@strSearch  
     execute (@sql)

 END

when i run this procedure it is giving error like ambigious 'tags' in this procedure i am joining 3 tables . can u help me

Ambiguous column name 'tags'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'tags'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'tags'.

Answer 1

The error is this line:

set @strSearch = @strSearch + '''%' + @str +'%'''+'or tags like' 

You need to specify which table for the or tags like

Answer 2

The problem is this line:

set @strSearch = @strSearch + '''%' + @str +'%'''+'or tags like'

From what I can tell, all of the tables you are joining have a tags column and in this where clause you don't specify what table you are filtering by

Answer 3

Your current approach using dynamic SQL based on user input is vulnerable to SQL injection. I have altered it so the search terms get put into a table variable that is then joined on. This is safer.

Additionally I'm not sure about the desired semantics. Your dynamic SQL WHERE clause is where (phot_album.tags) like '+@strSearch +' or (trailor_creation.tags) like '+@strSearch +' or (video_upload.tags) like '+@strSearch

but your JOIN clause brings back records joined on tag. In which case the tag value in all of the tables will be the same and it is only necessary to check one of them.

CREATE PROCEDURE [dbo].[videos_getSearch](@searchstring AS VARCHAR(1000)) 
AS 
BEGIN  

DECLARE @CurNumber INT, @CommaIndex INT, @strSearch VARCHAR(3000),@STR VARCHAR(50) 
DECLARE @strQuery VARCHAR(1000),@RESULT VARCHAR(5000) 
DECLARE @SQL VARCHAR(2000) 
DECLARE @CurNumStr VARCHAR(20) 


 WHILE LEN(@searchstring) > 0 
 BEGIN 

     SET @CommaIndex = CHARINDEX(',', @searchstring) 
     IF @CommaIndex = 0 SET @CommaIndex = LEN(@searchstring)+1 
     SET @CurNumStr = SUBSTRING(@searchstring, 1, @CommaIndex-1) 
     SET @searchstring = SUBSTRING(@searchstring, @CommaIndex+1, LEN(@searchstring)) 

     DECLARE @SearchTerms TABLE
     (
     Term VARCHAR(50)
     )

    BEGIN 

        SET @STR = LTRIM(RTRIM(@CurNumStr)) 
       INSERT INTO @SearchTerms VALUES (@STR)
    END 
 END 


SELECT phot_album.albumid        ,
       phot_album.tags           ,
       phot_album.albumtitle     ,
       phot_album.coverphoto     ,
       trailor_creation.trailorid,
       trailor_creation.tags     ,
       trailor_creation.movie    ,
       trailor_creation.images   ,
       video_upload.videoid      ,
       video_upload.videotitle   ,
       video_upload.videofile    ,
       video_upload.tags
FROM   phot_album
       INNER JOIN trailor_creation
       ON     phot_album.tags = trailor_creation.tags
       INNER JOIN video_upload
       ON     phot_album.tags = video_upload.tags
       INNER JOIN @SearchTerms ST
       ON     phot_album.tags LIKE '%' + ST.Term + '%'



 END

Actually I'm going to guess that something like this might be more what you need. Does the tags column in the tables contain a comma delimited list of tags? If so putting this into first normal form will allow this query to be simpler and more efficient.

CREATE PROCEDURE [dbo].[videos_getSearch]
(
@searchstring AS VARCHAR(1000)
)
AS
    BEGIN

        DECLARE @SearchTerms TABLE 
        ( 
        Term VARCHAR(50) 
        )

        DECLARE @CommaIndex INT
        DECLARE @CurNumStr  VARCHAR(20)

        WHILE LEN(@searchstring) > 0
        BEGIN
            SET @CommaIndex   = CHARINDEX(',', @searchstring)

            IF @CommaIndex    = 0
                SET @CommaIndex   = LEN(@searchstring)+1

            SET @CurNumStr    = SUBSTRING(@searchstring, 1, @CommaIndex-1)
            SET @searchstring = SUBSTRING(@searchstring, @CommaIndex+1, LEN(@searchstring))

            BEGIN
                INSERT
                INTO @SearchTerms VALUES
                    ('%' + LTRIM(RTRIM(@CurNumStr)) + '%')
            END
        END


        SELECT 'phot_album'    AS Source   ,
            phot_album.albumid AS entityId ,
            phot_album.tags                ,
            phot_album.albumtitle AS title ,
            phot_album.coverphoto AS image
        FROM phot_album
            INNER JOIN @SearchTerms ST
            ON  phot_album.tags LIKE ST.Term

        UNION ALL

        SELECT 'trailor_creation' AS Source ,
            trailor_creation.trailorid      ,
            trailor_creation.tags           ,
            trailor_creation.movie          ,
            trailor_creation.images
        FROM trailor_creation
            INNER JOIN @SearchTerms ST
            ON  trailor_creation.tags LIKE ST.Term

        UNION ALL

        SELECT 'video_upload' AS Source ,
            video_upload.videoid        ,
            video_upload.tags           ,
            video_upload.videotitle     ,
            video_upload.videofile
        FROM video_upload
            INNER JOIN @SearchTerms ST
            ON  video_upload.tags LIKE ST.Term
    END