• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • 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 RadConsultant, TrainerCommented:
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 RadConsultant, TrainerCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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