Click here to Skip to main content
12,077,357 members (50,583 online)
Rate this:
 
Please Sign up or sign in to vote.
BEGIN
    DECLARE @CAMP VARCHAR(2)
    DECLARE @TYPE VARCHAR(10)
    SET @TYPE=''
    SET @CAMP=''
    DECLARE @QRY NVARCHAR(300)
    SET @QRY='SELECT * FROM [dbo].[LIBMAST ] WHERE '
    IF(@CAMP<>'')
    BEGIN
        SET @QRY=@QRY+'CAMPUS='+@CAMP+'AND '
    END
    IF(@TYPE<>'')
    BEGIN
        SET @QRY=@QRY+'TYPE= '+@TYPE
    END
    EXEC(@QRY)
END

in this case when both parameter is empty then error showing "Incorrect syntax near 'WHERE'".
when @TYPE parameter is empty and @CAMP have values hsowing error "
Incorrect syntax near 'AND'".
please help me.
Posted 21-Feb-13 19:16pm
josh-jw15.1K
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

you can write it as follows
BEGIN
    DECLARE @CAMP VARCHAR(2)
    DECLARE @TYPE VARCHAR(10)
    SET @TYPE='';
    SET @CAMP=''
    DECLARE @QRY NVARCHAR(300)
    SET @QRY='SELECT * FROM [dbo].[LIBMAST ] WHERE (1=1)';
    IF(@CAMP<>'')
    BEGIN
        SET @QRY=@QRY+' AND CAMPUS='''+@CAMP + '''';
    END
    IF(@TYPE<>'')
    BEGIN
        SET @QRY=@QRY + ' AND TYPE= '''+@TYPE + '''';
    END
    EXEC SP_ExecuteSql @QRY;
END

Just use (1=1) always true trick.
  Permalink  
v8
Comments
josh-jw 22-Feb-13 0:44am
   
in this query if i give any value to @TYPE like
set @TYEP='ABC' .then error showing invalid column name 'ABC'
S. M. Ahasan Habib 22-Feb-13 0:52am
   
Pls check it again, I update the code and test it and found it is working.
BEGIN
DECLARE @CAMP VARCHAR(2)
DECLARE @TYPE VARCHAR(10)
SET @TYPE='sdfas';
SET @CAMP='asdfasd'
DECLARE @QRY NVARCHAR(300)
SET @QRY='SELECT * FROM [dbo].[Target] WHERE (1=1)';
IF(@CAMP<>'')
BEGIN
SET @QRY=@QRY+' AND FirstName='''+@CAMP + '''';
END
IF(@TYPE<>'')
BEGIN
SET @QRY=@QRY + ' AND LastName= '''+@TYPE + '''';
END
EXEC SP_ExecuteSql @QRY;
END
josh-jw 22-Feb-13 1:05am
   
THANKS
maxrockM 22-Feb-13 5:08am
   
(1=1) is a hack and you can avoid it in your case by using method in my updated solution
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

hi,

For Dynamic Query... Check the link
Execute Dynamic SQL commands in SQL Server[^]

Regards,
GVPrabu
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Change the below code

SET @QRY=@QRY+'CAMPUS='+@CAMP+'AND '
to
SET @QRY=@QRY+'CAMPUS='+@CAMP+' AND ' 
Space before "AND"

Thanks
--RA
  Permalink  
v3
Comments
josh-jw 22-Feb-13 0:22am
   
yes did.but same error showing like "Incorrect syntax near 'AND'".
here in this case when @camp have value and @type is empty query is like select * from libmast where campus='05' and .
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Please replace as below
DECLARE @CAMP VARCHAR(2)
    DECLARE @TYPE VARCHAR(10)
    SET @TYPE=''
    SET @CAMP='g'
    DECLARE @QRY NVARCHAR(300)
 
SET @QRY='SELECT * FROM [dbo].[LIBMAST ]'
IF(@CAMP <> '')
BEGIN
SET @QRY=@QRY+ ' WHERE CAMPUS='''+@CAMP+'''' 
IF (@TYPE <> '')
BEGIN
SET @QRY=@QRY+ ' AND TYPE= '''+@TYPE + ''''
END
END
ELSE IF  (@TYPE <> '')
BEGIN
SET @QRY=@QRY+ ' WHERE TYPE= '''+@TYPE + ''''
END
    exec(@QRY)
  Permalink  
v2
Comments
josh-jw 22-Feb-13 0:30am
   
in my query if @CAMP is '' ,no need where condition,means have to select all records.same in the case of @TYPE.
maxrockM 22-Feb-13 5:06am
   
Check My Update

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160212.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100