Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-03
5
Medium Priority
?
995 Views
Last Modified: 2007-12-19
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. "
0
Comment
Question by:akshay_sumeru
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 672 total points
ID: 17030461
CREATE PROCEDURE [sample1]   @intreceiptno varchar(20),  @fromdate datetime, @todate  datetime, @icityid int,@istateid int,@icountryid int,@intpincode int
as
begin
declare  @strQuery varchar(2000);
set @strQuery=' where 1=1 '

if @intreceiptno is not null
   set @strQuery = @strQuery + ' and receiptNo = ''' + @intreceiptno + ''' '

if @icountryid is not null  
  set @strQuery = @strQuery + ' and  countryDetails.countryID = ' + cast(@icountryid  as varchar(20))

if @istateid is not null
   set @strQuery = @strQuery + ' and  stateDetails.stateID = ' + cast(@istateid as varchar(20))

if @icityid is not null  
   set @strQuery = @strQuery + ' and  cityDetails.cityID  = ' + cast(@icityid as varchar(20))

if @intpincode is not null
   set @strQuery = @strQuery + ' and  postalCode LIKE ''' + cast(@intpincode as varchar(20) )+ ''' '

if @fromdate is not null  AND @todate is not null
  set @strQuery = @strQuery + ' and subscriptionDate >= convert (datetime, ''' + convert(varchar(20), @fromdate , 120) + ''', 120) '
  set @strQuery = @strQuery + ' and subscriptionDate < convert (datetime, ''' + convert(varchar(20), @todate , 120) + ''', 120) '

declare @sql varchar (500)
set @sql = '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 '

exec ( @sql + @strQuery)
 
end
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 664 total points
ID: 17030467

I think the better option will be to change the datatype of the i/ parameters to varchar, so that we can avoid t'Casting'

CREATE PROCEDURE [sample1]  
 @intreceiptno varchar(20),  
 @fromdate varchar(18),
 @todate   varchar(18),
 @icityid varchar(8),
 @istateid  varchar(8),
 @icountryid  varchar(8)
,@intpincode  varchar(8)
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


0
 
LVL 10

Assisted Solution

by:RichardCorrie
RichardCorrie earned 664 total points
ID: 17030726
to avoid having to cast everything to a string and to avoid any SQL injection attacks you could try something like:

CREATE PROCEDURE [sample1]  
@intreceiptno varchar(20) = null,  
@fromdate datetime = null,
@todate  datetime = null,
@icityid int = null,
@istateid int = null,
@icountryid int = null,
@intpincode int =null
as
begin
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
and
         (
              @intreceiptno is null
         or
              subDetails.receiptNo = @intreceiptno
        )
and
       (
           @icountryid is null  
          or
          countryDetails.countryID = @icountryid
      )
and
      (
          @istateid is null
      or
         stateDetails.stateID = @istateid
      )
and
    (
        @icityid is null  
    or
       cityDetails.cityID = @icityid
   )
and
    (
      @intpincode is null
       or
          postalCode LIKE  @intpincode + '%'
    )
and
     (
        @fromdate is null  and @todate is null
     or
        (
            @fromdate is not null  and @todate is null
       and
            subscriptionDate >= @fromdate
        )
     or
       (
            @fromdate is null  and @todate is not null
       and
            subscriptionDate <= @todate
        )
     or
       (
            @fromdate is not null  and @todate is not null
       and
            subscriptionDate BETWEEN  @fromdate AND @todate
        )
)

end
GO
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question