Escape sequences in Stored Procedures

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

.NET Inside SQL Server (2005)

Escape sequences in Stored Procedures

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

Posted - 2007-11-12 : 01:59:41

http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources