Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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-jw10.8K
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 at 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 at 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 at 22-Feb-13 1:05am
   
THANKS
maxrockM at 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 at 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 at 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 at 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
0 OriginalGriff 520
1 Maciej Los 290
2 Richard MacCutchan 265
3 BillWoodruff 265
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,764
1 Sergey Alexandrovich Kryukov 7,437
2 DamithSL 5,639
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411028.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2014
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