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,subsc riberName, address1,c ityName,st ateName,co untryName, teleOff,te leResi,exp iryDate from subDetails,cityDetails,sta teDetails, countryDet ails where subDetails.cityID=cityDeta ils.cityID and subDetails.stateID=stateDe tails.stat eID and subDetails.countryID=count ryDetails. 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. "
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,subsc
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.