how to write a dynamic query in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

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 18:16pm

josh-jw

Add a Solution

4 solutions

Solution 1

Change the below code

SQL

SET @QRY=@QRY+'CAMPUS='+@CAMP+'AND '

to

SQL

SET @QRY=@QRY+'CAMPUS='+@CAMP+' AND '

Space before "AND"

Thanks
--RA

Posted 21-Feb-13 18:18pm

Rajesh Anuhya

Updated 21-Feb-13 18:49pm

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 .

Solution 2

Please replace as below

SQL

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)

Posted 21-Feb-13 18:26pm

maxrockM

Updated 21-Feb-13 23:05pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

S. M. Ahasan Habib · Accepted Answer · 2013-02-21T18:34:00

Solution 3

you can write it as follows

SQL

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.

Posted 21-Feb-13 18:34pm

S. M. Ahasan Habib

Updated 21-Feb-13 18:51pm

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

gvprabu · Accepted Answer · 2013-02-21T18:50:00

Solution 4

hi,

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

Regards,
GVPrabu

Posted 21-Feb-13 18:50pm

gvprabu