Improve statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Improve statement

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-23 : 11:37:57

There are two ways to insert data but 1) way is much fast than 2) way.
How to improve 2) way?
1)-----------------
CREATE TABLE [#T](
[CLAIMNO] [nvarchar](20) NULL
) ON [PRIMARY]

INSERT INTO #T ( CLAIMNO )
SELECT CLAIM.CLAIMNO
FROM CLAIM
where CLAIM.CLAIMNO like ((select (convert(varchar(20), getdate()-7, 112) + '148' + '%')))
2)------------------
Declare @operator nvarchar(50)
Declare @myoperator nvarchar(50)
Set @operator = ‘148’
select @myoperator =
case
when len(@operator) = 1 then '00' + @operator
when len(@operator) = 2 then '0' + @operator
when len(@operator) = 3 then @operator
end

CREATE TABLE [#T](
[CLAIMNO] [nvarchar](20) NULL
) ON [PRIMARY]

INSERT INTO #T ( CLAIMNO )
SELECT CLAIM.CLAIMNO
FROM CLAIM
where CLAIM.CLAIMNO like ((select (convert(varchar(20), getdate()-7, 112) + @myoperator + '%')))

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-23 : 11:50:39

by following the suggestion give in

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=134809

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 11:51:16

Try this first:


declare @claimno varchar(50)
       ,@operator varchar(3)
set @operator = '148'

set @claimno = convert(varchar(20), getdate()-7, 112) + right('000' + @operator, 3) + '%'

INSERT INTO #T ( CLAIMNO )
SELECT CLAIM.CLAIMNO
FROM   CLAIM
where  CLAIM.CLAIMNO like @claimno

If that doesn't work then do this:


declare @operator varchar(3)
set @operator = '148'

declare @sql nvarchar(2000)
set @sql = N'SELECT CLAIM.CLAIMNO
FROM   CLAIM
where  CLAIM.CLAIMNO like ' + convert(varchar(20), getdate()-7, 112) + right('000' + @operator, 3) + '%'

INSERT INTO #T ( CLAIMNO )
exec sp_executesql @sql

Be One with the Optimizer
TG

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-23 : 14:17:32

I ran both ways.
First is slow also but second got error:
Incorrect syntax near '%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:23:11

the where clause should be like this i guess

where CLAIM.CLAIMNO like ''' + convert(varchar(20), getdate()-7, 112) + right('000' + @operator, 3) + '%'''

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 14:29:02

Ah - yes. Thanks, Visakh :)

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:30:51