Solved

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

Posted on 2006-07-03
5
993 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 142

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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