[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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.
0
dotnet0824
Asked:
dotnet0824
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
Reza RadCommented:
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

0
 
Reza RadCommented:
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

0
 
dotnet0824Author Commented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ralmadaCommented:
you don't need dynamic SQL there. try 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)
	)

Open in new window

0
 
ralmadaCommented:
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

0
 
dotnet0824Author Commented:
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
0
 
dotnet0824Author Commented:
in the above posting @fromDate is DateTime dataType.
0
 
SharathData EngineerCommented:
Try 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)


If you are looking for dynamic sql, then try this.

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) + '
   and ((' +  cast(@custtype as nvarchar) + ' = 0 and salary > 0) or ( + cast(@custtype as nvarchar) + ' = 1 and salary > 0 and revenue > 0))
   and (datediff(dd,' + convert(nvarchar,@FromDate) + ',DateSent) >= 0 or ' + convert(nvarchar,@FromDate) + ' is null)'

exec(@str)Try 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)


If you are looking for dynamic sql, then try this.

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) + '
   and ((' +  cast(@custtype as nvarchar) + ' = 0 and salary > 0) or ( + cast(@custtype as nvarchar) + ' = 1 and salary > 0 and revenue > 0))
   and (datediff(dd,' + convert(nvarchar,@FromDate) + ',DateSent) >= 0 or ' + convert(nvarchar,@FromDate) + ' is null)'

exec(@str)
0
 
ralmadaCommented:
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

0
 
ralmadaCommented:
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

0
 
dotnet0824Author Commented:
Thanks ralmada & sharath.
Ralmada your query was great.
0
 
dotnet0824Author Commented:
Thanks guys.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now