dynamic subquery - 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)

dynamic subquery

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-16 : 08:50:22

Amrita writes "hi
i have 2 tables and table 1 in dependant on table 2 ,well basically the problem is i want to give an advanced search on it so creaing a dynamic query but need a subquery also
so not sure how to use a subquery in a dynamic query

My code is
--**************************88
SET @OUTERSqlString='
select AM.NAME,hotel_Services,AM.ADDRESS,WEBSITE,PHONE1,EMAIL FROM
ACCOMODATION_MASTER AM JOIN
ACCOMODATION_TYPE ATP ON
AM.ACCOMODATION_TYPE_TAG =ATP.ACCOMODATION_TYPE_TAG
AND AM.LANG_CULTURE=ATP.LANG_CULTURE
where
accomodation_id in
(
declare @SqlString nvarchar(4000)

set @SqlString = @SqlString +
select
ACCOMODATION_ID
FROM
HOTEL_PACKAGE HP
WHERE
cast(CONVERT(VARCHAR(10),VALID_TO,101) as datetime) > cast(CONVERT(VARCHAR(10),GETDATE(),101) as datetime)

IF NOT @pnsPackageName IS NULL
BEGIN
set @SqlString=@SqlString + AND PACKAGE_NAME LIKE + quotename(''%'' + ' + @pnsPackageName + '+ ''%'','''')
END

exec(@SqlString)
'
Set @OUTERSqlString=@OUTERSqlString + ')'
exec(@OUTERSqlString)
---END CODE************
Thnx in advance
Amrita"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 09:21:19

i don't think you need dynamic sql for this...
i guessing this does the same, test it.


select 	AM.NAME,hotel_Services,AM.ADDRESS,WEBSITE,PHONE1,EMAIL 
FROM 	ACCOMODATION_MASTER AM 
	JOIN	ACCOMODATION_TYPE ATP ON AM.ACCOMODATION_TYPE_TAG =ATP.ACCOMODATION_TYPE_TAG
		AND AM.LANG_CULTURE=ATP.LANG_CULTURE
	INNER join HOTEL_PACKAGE HP on AM.ACCOMODATION_ID = HP.ACCOMODATION_ID 
		AND datediff(d, VALID_TO, getdate()) > 1
		AND PACKAGE_NAME LIKE coalesce('%'+@pnsPackageName+'%', '%')

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources