i am facing problem with "like operator with "and"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

i am facing problem with "like operator with "and"

Author

Topic

masood1970
Starting Member

1 Post

Posted - 2011-04-20 : 12:34:31

alter proc BuildMarkup(@Cls varchar(7), @SubCls varchar(7)) as
Declare @fldid varchar(3)
Declare @clsid varchar(2)
Declare @compid tinyint
DECLARE @sSQL nvarchar(2000)
Declare @Outputdef nvarchar(500)
DECLARE @ParmDefinition NVARCHAR(300)
Declare @defvalue nvarchar(10)
DECLARE @temp1 varchar(11)
DECLARE @compvalue varchar(11)
DECLARE @temp2 decimal (5,3)
Declare @CursorQry nvarchar(500)
Declare @InsertStmt nvarchar(500)
set @defvalue = 'default'
DECLARE fldid_cursor CURSOR FOR Select colid FROM SP_MARKUPPRICEBASE order by colid
select ClsId[Part Class],B.[Desc] into #myTempTable from
sp_retailprice A inner join Sp_PCm B on B.Code=A.Comp_Value where 1=2
alter table #myTempTable add [default] decimal (5,3)
--set @InsertStmt = N''
--set @InsertStmt = @InsertStmt + '
if lower(@Cls)<>'view all'
begin
--set @InsertStmt = @InsertStmt + ' AND clsid like '''+ @Cls% +''''
If lower(@subcls)<> 'view all' --''%' + @Color + '%'''
set @InsertStmt = N'insert into #mytemptable select ClsId[Part Class],B.[Desc], markupvalue from sp_retailprice A inner join Sp_PCm B on B.Code=A.Comp_Value where colname='''+ @defvalue + ''' AND clsid like ''' + @Cls + '%''' and clsid like '''%'+ @subcls +''''
--(iam getting the above line) Msg 156, Level 15, State 1, Procedure BuildMarkup, Line 25
--Incorrect syntax near the keyword 'and'.
else
set @InsertStmt = N'insert into #mytemptable select ClsId[Part Class],B.[Desc], markupvalue from sp_retailprice A inner join Sp_PCm B on B.Code=A.Comp_Value where colname='''+ @defvalue + ''' AND clsid like + @Cls +'%''

end
else
begin
If lower(@subcls)<> 'view all'
set @InsertStmt = N'insert into #mytemptable select ClsId[Part Class],B.[Desc], markupvalue from sp_retailprice A inner join Sp_PCm B on B.Code=A.Comp_Value where colname='''+ @defvalue + ''' and clsid like '%'+ @subcls +'''

else
set @InsertStmt = N'insert into #mytemptable select ClsId[Part Class],B.[Desc], markupvalue from sp_retailprice A inner join Sp_PCm B on B.Code=A.Comp_Value where colname='''+ @defvalue + ''''
end
print convert(varchar(400), @InsertStmt)
exec sp_executesql @InsertStmt
open fldid_cursor
Fetch next from fldid_cursor into @fldid
While @@fetch_status = 0
begin
SET @ParmDefinition = N'alter table #myTempTable add '+ @fldid + ' decimal (5,3)'
exec sp_executesql @ParmDefinition

--set @CursorQry = N'DECLARE fldid_inserts CURSOR FOR Select ClsId,Comp_Value FROM sp_retailprice where colname=''default'''

if lower(@Cls)<>'view all'
begin
--set @CursorQry = @CursorQry + ' AND clsid like '''+ @Cls% + ''''
If lower(@subcls)<> 'view all'
--set @CursorQry = @CursorQry + ' and clsid like '+ %@subcls + ''''
set @CursorQry = N'DECLARE fldid_inserts CURSOR FOR Select ClsId,Comp_Value FROM sp_retailprice where colname='+ @defvalue + ' AND clsid like '+ @Cls +' AND clsid like ' + @subcls
else
set @CursorQry = N'DECLARE fldid_inserts CURSOR FOR Select ClsId,Comp_Value FROM sp_retailprice where colname='+ @defvalue + ' AND clsid like '+ @Cls
end
else
begin
If lower(@subcls)<> 'view all'
--set @CursorQry = @CursorQry + ' and clsid like '+ %@subcls + ''''
set @CursorQry = N'DECLARE fldid_inserts CURSOR FOR Select ClsId,Comp_Value FROM sp_retailprice where colname='+ @defvalue + ' and clsid like ' + @subcls
end
exec sp_executesql @CursorQry
open fldid_inserts
Fetch next from fldid_inserts into @clsid, @compid
while @@fetch_status = 0
begin
SELECT @sSQL = N'select @tempOUT1=B.[Desc], @tempOUT2= markupvalue from '
SELECT @sSQL = @sSQL +'sp_retailprice A inner join Sp_PCm B on B.Code=A.Comp_Value '
SELECT @sSQL = @sSQL +'where clsid='''+ @clsid +''' and A.Comp_Value ='+ convert(nvarchar(3), @compid) +' and colname='''+ @fldid +''''
--select @ssql
select @temp1=null
select @temp2 = null
SET @Outputdef = N'@tempOUT1 varchar(11) OUTPUT,@tempOUT2 decimal(5,3) OUTPUT'
EXEC sp_executesql @sSQL, @Outputdef, @tempOUT1= @temp1 output, @tempOUT2= @temp2 output
if @temp1 is null And @temp2 is null
begin
select @compvalue=[desc] from sp_pcm where code=@compid
SET @ParmDefinition = N'update #myTempTable set '+ @fldid +'= 0.0 where [Part Class]='''+ @clsid +''' and [Desc] ='''+ @compvalue +''''
exec sp_executesql @ParmDefinition
end
else
begin
--print '@temp1 ' + @temp1 + '@temp2 ' + convert(varchar(6), @temp2) + '@fldid ' + @fldid
SET @ParmDefinition = N'update #myTempTable set '+ @fldid +'= '+ convert(nvarchar(6), @temp2) + ' where [Part Class]='''+ @clsid +''' and [Desc] ='''+ @temp1 +''''
exec sp_executesql @ParmDefinition
end
Fetch next from fldid_inserts into @clsid, @compid
end
close fldid_inserts
deallocate fldid_inserts

Fetch next from fldid_cursor into @fldid
end
close fldid_cursor
deallocate fldid_cursor
select * from #myTempTable --order by test

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-20 : 12:54:30

Wow, that's a lot of confusing code and very little information (no information) provided. Please re-read your post and notice how it isn't clear enough for us to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-04-21 : 05:55:32

1. what are you tryin to do in functional terms.
2. avoid cursors like the devil spawn
3. print out the contents of @InsertStmt
4. suspect you are missing a some quotes near AND clsid like ''' + @Cls + '%'''

5. most important of all - listen to TKizer. Goddess status is earned hard....and all her advice is VIP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources