Solved

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

Posted on 2006-07-03
5
994 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 168 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 166 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 166 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

691 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