Dynamic SQL - 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)

Dynamic SQL

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2007-02-01 : 14:18:08

How do I quote the string in a dynamic SQL. For example,
set WhereClause = ' WHERE IssueTitle like %' + @IssueTitle + '%'

How do I add quotes befor and after the %s?

Thanks.
DanYeung

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 14:29:12

[code]declare @WhereClause varchar(1000),
@issuetitle varchar(15)

select @issuetitle = 'mark'

set @WhereClause = ' WHERE IssueTitle like ''%' + @IssueTitle + '%'''

print @WhereClause[/code]

Peter Larsson
Helsingborg, Sweden

danyeung
Posting Yak Master

102 Posts

Posted - 2007-02-01 : 16:53:21

Thanks Peter,

It works. But I applied it to the date and it didn't work.
For example:
declare @WhereClause as varchar(1000),
SubmittedDate as datetime

set SubmittedDate = '01/01/2007'
set @WhereClause = ' WHERE SubmittedDate = ''' + cast(@SubmittedDate as char(10)) + ''''

Please advise. Thanks.

DanYeung

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 17:04:53

Don't use cast. use Convert


declare @WhereClause as varchar(1000),
@SubmittedDate as datetime

set @SubmittedDate = '01/01/2007'
set @WhereClause = ' WHERE SubmittedDate = ''' + cast(@SubmittedDate as char(10)) convert(varchar(10), @SubmittedDate, 112) + ''''

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 17:06:18

are you using exec() or sp_executesql ?

danyeung
Posting Yak Master

102 Posts

Posted - 2007-02-01 : 17:21:59

Thanks KH,

Actually, cast works too if I increase the cast size to char(12). One number was cut off if char(10). Thanks.

DanYeung

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-01 : 17:41:18

Why do you need dynamic SQL for this?

Why can't you just do this:

SELECT ...
FROM YourTable
WHERE SubmittedDate LIKE '%' + @SubmittedDate + '%'

Tara Kizer

danyeung
Posting Yak Master

102 Posts

Posted - 2007-02-01 : 19:04:39

The stored procedure has 14 pass-in parameters. The where clause considers only the paremeters that have data passing in. So it looks like this:

if @SubmittedDate <> ''
begin
@WhereClause = @WhereClause + ' AND SubmittedDate = ''' + cast(@SubmittedDate as char(10)) + ''''
end

if @IssueTitle <> ''
begin
@WhereClause = @WhereClause + ' AND IssueTitle like ''%' + @IssueTitle + ''''
end

.... and 12 more.

DanYeung

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-01 : 19:09:14

You should be using COALESCE then instead.

http://www.sqlteam.com/item.asp?ItemID=2077

COALESCE allows you to have a dynamic WHERE clause without using dynamic SQL.

Tara Kizer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 19:52:50

quote:
Originally posted by danyeung

Thanks KH,

Actually, cast works too if I increase the cast size to char(12). One number was cut off if char(10). Thanks.

DanYeung

yes but with cast() you can't specify the style and the resulting date string format is depending on the system settings. If you need to convert date into string for comparison with a datatime datatype (as in your case), you should format it as YYYYMMDD. That's why i use convert() with style 112.

Another question I ask is if you are using exec() or sp_executesql. With sp_executesql, you can pass the @SubmittedDate in as a parameter and hence you do not need to convert to string to form the sql string.

you sql string can be like this


set @WhereClause = ' WHERE SubmittedDate = @SubmittedDate'
-- complete sql string
set @sql = . . . .  + @WhereClause
exec sp_executesql @sql, N'@SubmittedDate datetime', @SubmittedDate

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-01 : 23:47:38

This is a good article about dynamic search conditions:

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

and another one you should fully understand before using dynamic sql for anything:

http://www.sommarskog.se/dynamic_sql.html

www.elsasoft.org

danyeung
Posting Yak Master

102 Posts

Posted - 2007-02-02 : 16:35:23

quote:
Originally posted by tkizer

You should be using COALESCE then instead.

http://www.sqlteam.com/item.asp?ItemID=2077

COALESCE allows you to have a dynamic WHERE clause without using dynamic SQL.

Tara Kizer

It works if I the parameters are string. But it works differently when the parameters are date, integer, and other types. I will figure it out. Thanks.

DanYeung

danyeung
Posting Yak Master

102 Posts

Posted - 2007-02-02 : 16:36:37

quote:
Originally posted by khtan

quote:
Originally posted by danyeung

Thanks KH,

Actually, cast works too if I increase the cast size to char(12). One number was cut off if char(10). Thanks.

DanYeung

yes but with cast() you can't specify the style and the resulting date string format is depending on the system settings. If you need to convert date into string for comparison with a datatime datatype (as in your case), you should format it as YYYYMMDD. That's why i use convert() with style 112.

Another question I ask is if you are using exec() or sp_executesql. With sp_executesql, you can pass the @SubmittedDate in as a parameter and hence you do not need to convert to string to form the sql string.

you sql string can be like this

set @WhereClause = ' WHERE SubmittedDate = @SubmittedDate'
-- complete sql string
set @sql = . . . . + @WhereClause
exec sp_executesql @sql, N'@SubmittedDate datetime', @SubmittedDate

KH

You are right. Thanks.

DanYeung

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources