Dynamic Execution - 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 Execution

Author

Topic

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2001-07-07 : 04:52:05

In my stored procedure i am executing the querry dynamically.e.g.


declare @SQL nvarchar(4000)

	set @SQL = N'select tbRestaurant.*,tbZIP.ZIP, tbCity.City 
		from tbRestaurant,tbZIP, tbCity 
		where tbRestaurant.CityId = '+cast(@CityId as varchar(4)) +' 
		and tbRestaurant.ZIPId = tbZIP.ZIPId 
		and tbRestaurant.CityId = tbCity.CityId	 
		and tbRestaurant.RestaurantId in 
			(Select RestaurantId from tbRestaurantSpecialNeeds 
			where SpecialNeedId in ('+@SpecialNeedId+')) order by RestaurantName'

I am passing @SpecialNeedId as varchar and its value is say '1,2,3'

But when i use

 
Select @SQL

Then the result shows the concatenated string but not the full one, just truncated one.
WHY?
Interestingly, when i use


exec sp_executesql @SQL

It runs perfectly.I am getting the result but Select statement fails.
WHY?

Edited by - sandesh_moghe on 07/07/2001 04:54:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources