SQL Query - 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 2000 Forums

Transact-SQL (2000)

SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-14 : 08:05:22

Kobus writes "I have a stored procedure that gives me the same problem as the following query:

Declare @SQL varchar(1000),
@pCode nvarchar(10)

Set @pCode = 'T2F 8M4'
Set @SQL = 'Select * From NorthWind..Customers Where PostalCode Like '+CAST(@pCode As nvarchar(10))+''
Execute (@SQL)

Why does this query only accept numeric values - replacing 'T2F 8M4' with '12209' gives the correct result?

Regards"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-14 : 08:16:28

Try this

Declare @SQL varchar(1000),
@pCode nvarchar(10)

Set @pCode = 'T2F 8M4'
Set @SQL = 'Select * From NorthWind..Customers Where PostalCode Like '''+CAST(@pCode As nvarchar(10))+''''
Execute (@SQL)

Madhivanan

kobus
Starting Member

1 Post

Posted - 2005-02-15 : 13:12:40

Thank you Madhivanan

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-15 : 15:38:50

Why are you casting @pCode from an nvarchar(10) to an nvarchar(10)?

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-16 : 08:41:47

quote:
Originally posted by Bustaz Kool

Why are you casting @pCode from an nvarchar(10) to an nvarchar(10)?

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

You are correct. It can be done without cast

Madhivanan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-16 : 23:38:07

If you had looked at the query you were generating, you would have seen that it is missing the required single quotes. Also, there is really no point in using a like withour wildcards.

Select * From NorthWind..Customers Where PostalCode Like T2F 8M4

What you need is something like this:

Select * From NorthWind..Customers Where PostalCode Like '%T2F 8M4%'

Try this code:



Declare @SQL varchar(1000), 
	@pCode nvarchar(10)

Set @pCode = 'T2F 8M4'
Set @SQL = 'Select * From NorthWind..Customers Where PostalCode Like ''%'+CAST(@pCode As nvarchar(10))+'%'''

Execute (@SQL)

quote:
Originally posted by AskSQLTeam

Kobus writes "I have a stored procedure that gives me the same problem as the following query:

Declare @SQL varchar(1000),
@pCode nvarchar(10)

Set @pCode = 'T2F 8M4'
Set @SQL = 'Select * From NorthWind..Customers Where PostalCode Like '+CAST(@pCode As nvarchar(10))+''
Execute (@SQL)

Why does this query only accept numeric values - replacing 'T2F 8M4' with '12209' gives the correct result?

Regards"

Codo Ergo Sum

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-02-17 : 00:49:30

Why are you using Dynamic SQL Here??
It is not neccessary and its ugly

Try CharIndex or Patindex

Something Like This:

Declare @pCode nvarchar(10)
Set @pCode = 'T2F 8M4'
select * from Northwind..Customers where CharIndex(@pCode, PostalCode) > 0

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources