asked on

sql server query

i want to build a sql dynamic statement in stored procedure

here is the following proc

create procedure bal_Customer
@customerID int,
@custType int = 0
as

declare @str varchar(100)

if(@custType = 0)
SET @str = 'AND SALARY' > 0 (i want salary > 0.00)
else if(@custType = 1)
SET @str = 'AND salary' > 0 + 'ANd revenue >' + 0

select * from customer where customerId = @CustomeID + @str (I want to pass the dynamic string here)

I am getting error
Conversion failed when converting the varchar value 'AND salary >' to data type int.

Reza Rad

try this

create procedure bal_Customer
@customerID int,
@custType int = 0
as

declare @str varchar(100)

   if(@custType = 0)
     SET @str = 'AND SALARY > 0'   (i want salary > 0.00)
    else if(@custType = 1)
    SET @str = 'AND salary > 0'  +  'ANd revenue >' + '0'

Open in new window

Reza Rad

this one is complete

create procedure bal_Customer
@customerID int,
@custType int = 0
as

declare @str varchar(100)

   if(@custType = 0)
     SET @str = 'AND SALARY > 0'  
    else if(@custType = 1)
    SET @str = 'AND salary > 0'  +  'ANd revenue >' + '0'


select * from customer where  customerId = @CustomeID  +  @str

Open in new window

dotnet0824

ASKER

when executing the stored proc I am getting the same error

Conversion failed when converting the varchar value 'AND Revenue > 0' to data type int.

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

If you still want dynamic SQL, here's the corrected version:

create procedure bal_Customer 
@customerID int, 
@custType int = 0 
as 

declare @str varchar(100) 
set @str = 'select * from customer where  customerId = ' + cast(@CustomeID as varchar)

   if(@custType = 0) 
     SET @str = @str + ' AND SALARY > 0'   --(i want salary > 0.00) 
    else if(@custType = 1) 
    SET @str = @str + ' AND salary > 0 ANd revenue > 0' 

exec(@str)

Open in new window

dotnet0824

ASKER

Hi Thanks..

I want to put in this one too

AND (DATEDIFF(dd,@FromDate,DateSent) >= 0 OR @FromDate IS NULL)

Can that be integrated in above statement and shown please

dotnet0824

ASKER

in the above posting @fromDate is DateTime dataType.

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

Like I said you don't need dynamic SQL so just add your code at the end like this:

create procedure bal_Customer  
@customerID int,  
@custType int = 0  
as  
 
select * from customer where  customerId = @CustomeID and 
        ((@custtype = 0 and salary > 0) or 
         (@custtype = 1 and salary > 0 and revenue > 0) 
        ) and
        (DATEDIFF(dd,@FromDate,DateSent) >= 0 OR @FromDate IS NULL)

Open in new window

ralmada

Using dynamic SQL here's your updated query:

create procedure bal_Customer  
@customerID int,  
@custType int = 0  
as  
 
declare @str varchar(100)  
set @str = 'select * from customer where  customerId = ' + cast(@CustomeID as varchar) 
 
   if(@custType = 0)  
     SET @str = @str + ' AND SALARY > 0'   --(i want salary > 0.00)  
    else if(@custType = 1)  
    SET @str = @str + ' AND salary > 0 ANd revenue > 0'  

    set @str = @str + ' and (DATEDIFF(dd,''' + cast(@FromDate as varchar) + ''',DateSent) >= 0 OR 1=' + case when @FromDate is null then '1' else '0' end + ')'


 
exec(@str)

Open in new window

dotnet0824

ASKER

Thanks ralmada & sharath.
Ralmada your query was great.

dotnet0824

ASKER

Thanks guys.