More 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

SQL Server Development (2000)

More Dynamic SQL

Author

Topic

Gsuttie
Starting Member

14 Posts

Posted - 2001-02-27 : 04:04:59

I have an asp page where you can filter say 8 fields. Depending on the input of the user in those 8 fields i need to create a stored proc using dynamic sql to create the query.

If the user for instance misses out the first 6 text boxes then i just want to create a query on the last 2 fields. I need a stored proc not a sql string on the page.

My Query is below -
Where am i going wrong?

declare @strSQL as varchar(255)
declare @trade_cpty_sender_sub_id as int
declare @trade_security_isin as varchar(50)
declare @trade_settlement_date as datetime
declare @trade_portfolio as varchar

set @strSQL = "select top 100 *
FROM t_live_trades l
INNER JOIN t_user u ON l.trade_user_id = u.user_id
INNER JOIN t_counterparty c ON l.trade_cpty_id = c.cpty_id and where "

if 1 is not null
begin
strsql = strsql & " l.trade_cpty_id = @trade_cpty_id
end
end if

if @trade_cpty_sender_sub_id is not null
begin
strsql = strsql & " l.trade_cpty_sender_sub_id = @trade_cpty_sender_sub_id
end
end if

if @trade_timestamp is not null
begin
strsql = strsql & " AND l.trade_timestamp like @trade_timestamp
end
end if

if @trade_security_isin is not null
begin
strsql = strsql & " AND l.trade_security_isin = @trade_security_isin
end
end if

if @trade_security_zip_code is not null
begin
strsql = strsql & " AND l.trade_security_zip_code = @trade_security_zip_code
end
end if

if @trade_settlement_date is not null
begin
strsql = strsql & " AND l.trade_settlement_date = @trade_settlement_date
end
end if

if @trade_nominal is not null
begin
strsql = strsql & " AND l.trade_nominal = @trade_nominal
end
end if

if @trade_portfolio is not null
begin
strsql = strsql & " AND l.trade_portfolio = @trade_portfolio
end
end if

print (strSQL)
exec (strSQL)

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources