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

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-06-05 : 09:22:58

Hi,
Instead of building a sql query dynamically, how is it possible to prevent using set @sql = @sql + ...

For example, this is what I have:

set @sql = 'select'
set @sql = @sql + ' *'
set @sql = @sql + ' from'
set @sql = @sql + ' tbl_Management'
set @sql = @sql + ' where'

if (@Year > 0)
begin
set @sql = @sql + ' [Year] = ' + convert(varchar(4), @Year)
set @sql = @sql + ' AND'
end
if (@YearPeriod > 0)
begin
set @sql = @sql + ' YearPeriod = ' + convert(varchar(2), @YearPeriod)
set @sql = @sql + ' AND'
end
if (@ProgrammeAreaCode is not null)
begin
set @sql = @sql + ' AreaCode = ''' + convert(varchar(3), @AreaCode) + ''''
set @sql = @sql + ' AND'
end

--trim off the last AND...
set @sql = left(@sql, len(@sql) - 3)

exec sp_sqlexec @sql

------------------------------------------------------
I am looking for something like:

select
*
from
tbl_Management
where

([Year] is null or [Year] = @Year)
AND
(YearPeriod is null or YearPeriod = @YearPeriod)
AND
(AreaCode is null or AreaCode = @AreaCode)
------------------------------------------------------
Please remember that the user may not pass any of the parameters or only one of them or may pass all of them.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-05 : 09:27:29

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 4

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 09:29:44

use sp_executesql. It allows you to pass in paramter

declare @sql nvarchar(4000)

set @sql = 'select' 			+ char(13)
set @sql = @sql + '*' 			+ char(13)
set @sql = @sql + 'from'		+ char(13)
set @sql = @sql + 'tbl_Management'	+ char(13)
set @sql = @sql + 'where [Year] 	= coalesce(@Year, [Year])'		+ char(13)
set @sql = @sql + 'and 	 [YearPeriod] 	= coalesce(@YearPeriod, [YearPeriod])'	+ char(13)
set @sql = @sql + 'and 	 [AreaCode] 	= coalesce(@ProgrammeAreaCode, [AreaCode])'	+ char(13)

print @sql

exec sp_executesql @sql, N'@Year int, @YearPeriod int, @ProgrammeAreaCode int',
			@Year, @YearPeriod, @ProgrammeAreaCode

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 12:49:07

Here's everything you've ever wanted to know about this kind of 'dynamic' searching...

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

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-06-06 : 03:20:32

quote:
Originally posted by spirit1

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 4

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

This is what I am doing now:

create procedure usp_ManagementGet
@Year int = 0,
@YearPeriod int = 0,
@AreaCode varchar(3) = null

as

select
*
from
tbl_Management
where

([Year] is null or [Year] = @Year)
AND
(YearPeriod is null or YearPeriod = @YearPeriod)
AND
(AreaCode is null or AreaCode = @AreaCode)

/*test the sp
exec usp_ManagementGet @Year = 0, @YearPeriod = 0, @AreaCode = null
*/

IS this correct? If so then I do not see why the above exec does not return data (there are data for these passed parameters)
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 03:24:28

did you tried this ? Any data returned ?

select
*
from
tbl_Management
where [Year] = 0
and   YearPeriod = 0

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-06-06 : 08:27:39

Hi,
Obviously there are no records for year=0 and YearPeriod = 0
So nothing is returned.
Assuming no parametr is passed. i.e. year and yearperiod are not passed (not selected)
Then how can the sql recognise that?
For example, the way it get recognised is because I am building the sql dynamically in the first post BUT I am trying to avoid building the sql this way.
Any thoughts?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 08:31:18

"Obviously there are no records for year=0 and YearPeriod = 0
So nothing is returned."
Your statement :
exec usp_ManagementGet @Year = 0, @YearPeriod = 0, @AreaCode = null
is actually the same as
select * from tbl_Management where [Year] = 0 and YearPeriod = 0

"year and yearperiod are not passed"
pass in as NULL

exec usp_ManagementGet @Year = NULL, @YearPeriod = NULL, @AreaCode = null

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-06-06 : 09:19:16

Hi,
I am not sure what I am doing wrong but I thought I should step back abit and created a simple stored procedure as below and tried to execute it. But it still returned no data.

--------this sp should return data
exec usp_test
--------------------------
alter procedure usp_test
@Year int = null,
@YearPeriod int = null,
@AreaCode varchar(3) = null
as
select
*
from
tbl_Management
where

([Year] is null or [Year] = @Year)
AND
(YearPeriod is null or YearPeriod = @YearPeriod)
AND
(AreaCode is null or AreaCode = @AreaCode)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources