Stored procedure jumping lines of code

[Clanger67]

In the following stored procedure everything works fine except when I want to do a search on the @reg variable. What it should do is get data from the avon2 table, but it skips this line and tries to get data from the avon1 table.

I get the following error messages

select * from tblprivatecar_avon1 where
select * from tblprivatecar_avon1 where
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'where'.


declare @reg varchar(10)
declare @dbname varchar(10)
declare @SqlStr varchar(300)
declare @polnum varchar(25)
declare @refno varchar(15)
declare @postcode varchar(10)
declare @lastname varchar(40)

-- ***** Get data from scheme tables *****
--set @dbname = 'scheme'
--set @polnum = '02N068508'
--set @refno = 'ABAJ04RF02'
--set @reg = 'KAC 224D'
--set @postcode = 'RH13 7SG'
--set @lastname = 'Abrams'--'Mr A Abrams'

-- ***** Get data from privatecar tables *****

set @dbname = 'private'
set @reg = 'ky02 znu'

--set @polnum = '02N214207'
--set @refno = 'ANHR01PC01'
--set @postcode = 'GU31 4ET'
--set @lastname = 'Annetts' --'Miss H R Annetts'

begin

if @dbname = 'private'

begin

Should run this line

if @reg is not null
select @sqlstr = 'select * from tblprivatecar_avon2 where Reg = ''' + @reg + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''''

else

Tries to run this line instead
select @sqlstr = 'select * from tblprivatecar_avon1 where '

if @postcode is not null
select @sqlstr = @sqlstr + ' PCode = ''' + @postcode + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''' and'

if @lastname is not null
select @sqlstr = @sqlstr + ' reverse(substring(reverse(name),1,charindex('' '',reverse(name))-1)) = ' + '''' + @lastname + ''''
end
end

--else
begin

if @dbname = 'scheme'

begin

if @reg is not null
select @sqlstr = 'select * from tblscheme_avon2 where Reg = ''' + @reg + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''''

else

select @sqlstr = 'select * from tblscheme_avon1 where '

if @postcode is not null
select @sqlstr = @sqlstr + ' PCode = ''' + @postcode + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''' and'

if @lastname is not null
select @sqlstr = @sqlstr + ' reverse(substring(reverse(name),1,charindex('' '',reverse(name))-1)) = ' + '''' + @lastname + ''''
end
end

print @sqlstr

--check to see if sql has and at the end of the string
if right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

print @sqlstr

exec (@sqlstr)

Have spent 2 days going through postings on here but not having much luck.

Any help would be much appreciated.

Thanks

Dave

 

[SmileyConspiracy]

Any codelonger that a single statement in an IF… ELSE block needs to be contained within BEGIN and END statements so you get

IF <condition>
BEGIN

<some code>

END
ELSE
BEGIN

<some other code>

END

Also the second IF statement (“if @dbname = 'scheme'”) will overwrite the correct string with it’s ELSE conditions, so I've merged your two main statements into one using “else if @dbname = 'scheme'”.

I think this should do what your wanting

declare @reg varchar(10)
declare @dbname varchar(10)
declare @SqlStr varchar(300)
declare @polnum varchar(25)
declare @refno varchar(15)
declare @postcode varchar(10)
declare @lastname varchar(40)

-- ***** Get data from scheme tables *****
--set @dbname = 'scheme'
--set @polnum = '02N068508'
--set @refno = 'ABAJ04RF02'
--set @reg = 'KAC 224D'
--set @postcode = 'RH13 7SG'
--set @lastname = 'Abrams'--'Mr A Abrams'

-- ***** Get data from privatecar tables *****

set @dbname = 'private'
set @reg = 'ky02 znu'

--set @polnum = '02N214207'
--set @refno = 'ANHR01PC01'
--set @postcode = 'GU31 4ET'
--set @lastname = 'Annetts' --'Miss H R Annetts'

if @dbname = 'private'
begin
--Should run this line
if @reg is not null
select @sqlstr = 'select * from tblprivatecar_avon2 where Reg = ''' + @reg + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''''
end
else if @dbname = 'scheme'
begin

if @reg is not null
select @sqlstr = 'select * from tblscheme_avon2 where Reg = ''' + @reg + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''''
end
else
begin
select @sqlstr = 'select * from tblscheme_avon1 where '

if @postcode is not null
select @sqlstr = @sqlstr + ' PCode = ''' + @postcode + ''' and'

if @polnum is not null
select @sqlstr = @sqlstr + ' Policynumber = ''' + @polnum + ''' and'

if @refno is not null
select @sqlstr = @sqlstr + ' Refno = ''' + @refno + ''' and'

if @lastname is not null
select @sqlstr = @sqlstr + ' reverse(substring(reverse(name),1,charindex('' '',reverse(name))-1)) = ' + '''' + @lastname + ''''
end

print @sqlstr

--check to see if sql has and at the end of the string
if right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

print @sqlstr

exec (@sqlstr)

hope this helps
Stephen

 

[Clanger67]

Stephen

Thanks for your reply.

It does do what I want up to a point. My fault for not explaining it properly.

I need it to do 1 of 4 things.

1 if the @dbname = 'private' and @reg is not null
get data from tblprivatecar_avon2

2 if the @dbname = 'private' and @reg is null
get data from tblprivatecar_avon1

3 if the @dbname = 'scheme' and @reg is not null
get data from tblscheme_avon2

4 if the @dbname = 'scheme' and @reg is null
get data from tblscheme_avon1

your solution sorts out part 1, 3 and 4 but not 2.

I will work with what you have sent me and if i find the answer i will let you know

Thanks

David