Link to home
Start Free TrialLog in
Avatar of akshay_sumeru
akshay_sumeru

asked on

how to concat select query and varchar variable in stored procedure?

CREATE PROCEDURE [sample1]   @intreceiptno varchar(20),  @fromdate datetime, @todate  datetime, @icityid int,@istateid int,@icountryid int,@intpincode int
as
begin
declare  @strQuery varchar(2000);
declare @flagSearch bit;

set @strQuery=""
set @flagSearch = 1

if @intreceiptno is not null
begin
      if @flagSearch = 0
      begin
            set @strQuery = @strQuery + "where receiptNo = @intreceiptno"
            set @flagSearch = 1
      end
      else
            set @strQuery = @strQuery + "and receiptNo = @intreceiptno"
      
end



if @icountryid is not null  
begin

      if @flagSearch = 0
      begin
            set @strQuery = @strQuery + "where countryDetails.countryID = @icountryid"
            set @flagSearch = 1
      end
      else
            set @strQuery = @strQuery + "and  countryDetails.countryID = @icountryid"
end



if @istateid is not null
begin
      if @flagSearch = 0
      begin
            set @strQuery = @strQuery + "where stateDetails.stateID = @istateid"
            set @flagSearch = 1
      end
      else
            set @strQuery = @strQuery + "and  stateDetails.stateID = @istateid"
end



if @icityid is not null  
begin
      if @flagSearch = 0
      begin
            set @strQuery = @strQuery + "where cityDetails.cityID = @icityid"
            set @flagSearch = 1
      end
      else
            set @strQuery = @strQuery + "and  cityDetails.cityID = @icityid"
end



if @intpincode is not null
begin
      if @flagSearch = 0
      begin
            set @strQuery = @strQuery + "where postalCode LIKE  @intpincode"
            set @flagSearch = 1
      end
      else
            set @strQuery = @strQuery + "and  postalCode LIKE  @intpincode"
end



if @fromdate is not null  or @todate is not null
begin
      if @flagSearch = 0
      begin
            set @strQuery = @strQuery + " where (subscriptionDate BETWEEN  @fromdate AND @todate)"
            set @flagSearch = 1
      end
      else
            set @strQuery = @strQuery + " and (subscriptionDate BETWEEN  @fromdate AND @todate)"

end

select subDetails.receiptNo,subscriberName,address1,cityName,stateName,countryName,teleOff,teleResi,expiryDate from subDetails,cityDetails,stateDetails,countryDetails where subDetails.cityID=cityDetails.cityID and subDetails.stateID=stateDetails.stateID and subDetails.countryID=countryDetails.countryID and subDetails.obsolete = 0 + @strQuery

end
GO


this is my stored procedure in which i've to concat select query and varchar variable @strQuery (showed in last statement). when i run this procedure with any one parameter in query like @icountryid = 16, it is given error as  "Syntax error converting the varchar value ' and  countryDetails.countryID = @icountryid' to a column of data type int. "
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial