| Author |
Topic |
|
vivsriaus
Starting Member
8 Posts |
 Posted - 2007-11-08 : 19:57:43
|
All,
What is the escape sequence in a stored procedure?
Here is what I'm trying to achieve:
ALTER PROCEDURE Test
(
@Func VarChar(1000)
)
AS
DECLARE @SQL VarChar(8000)
SELECT @SQL = 'SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE ' + @Func
Now, my goal is to add single quote (') before @Func and another one after that. For eg, if @Func is "Test", I want my query to be
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE 'Test'
and NOT
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE Test
Any help?
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-08 : 19:59:52
|
Why on Earth are you using dynamic SQL for this!?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-09 : 01:41:15
|
quote:
Originally posted by vivsriaus
All,
What is the escape sequence in a stored procedure?
Here is what I'm trying to achieve:
ALTER PROCEDURE Test
(
@Func VarChar(1000)
)
AS
DECLARE @SQL VarChar(8000)
SELECT @SQL = 'SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE ' + @Func
Now, my goal is to add single quote (') before @Func and another one after that. For eg, if @Func is "Test", I want my query to be
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE 'Test'
and NOT
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE Test
Any help?
As you dont pass object names as parameter, you dont need to use Dynamic SQL. This would work fine
ALTER PROCEDURE Test
(
@Func VarChar(1000)
)
AS
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE @Func+'%'
Madhivanan
Failing to plan is Planning to fail |
|
|
|
vivsriaus
Starting Member
8 Posts |
Posted - 2007-11-09 : 18:07:15
|
quote:
Originally posted by tkizer
Why on Earth are you using dynamic SQL for this!?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
This is just a sample query. I need to use a different WHERE clause depending on the Func param, and in there comes a dynamic SQL. Get it?
If I can get this simple query to work, I figured I could do the rest. I didn't want to complicate the question by throwing in my original complex SQL query.
|
|
|
|
vivsriaus
Starting Member
8 Posts |
Posted - 2007-11-09 : 18:14:55
|
quote:
Originally posted by madhivanan
As you dont pass object names as parameter, you dont need to use Dynamic SQL. This would work fine
ALTER PROCEDURE Test
(
@Func VarChar(1000)
)
AS
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE @Func+'%'
Madhi,
Thanks for the reply. But this will not do for me. I'll have a different WHERE clause in my query depending on the func param. Is there any to get to that?
To be specific, this is what I want:
if Func=="test"
myQuery = "SELECT TNAME FROM TABLE WHERE Parm1 = 'somethin' AND FUNC LIKE 'test'"
if Func==null
myQuery = "SELECT TNAME FROM TABLE WHERE FUNC LIKE 'test'" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-09 : 19:44:22
|
You can use COALESCE to achieve a dynamic WHERE clause without the use of dynamic SQL. There is an article here on how to do it.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
simagopes
Starting Member
1 Post |
Posted - 2007-11-22 : 06:16:51
|
I have to build a similar query. Please let me know hwo did u solve this.
I am also searching for some escape character.
Gopi |
|
|
|
ajing
Starting Member
3 Posts |
Posted - 2007-11-25 : 18:16:06
|
Hi,
Have you tried using quotename function.
Something like this?
declare @sql as varchar(1000)
set @sql = 'Select ' + quotename('test',char(39))
select @sql
|
|
|
|
|
Escape sequences in Stored Procedures