Exceuting Dynamic Querries - 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)

Exceuting Dynamic Querries

Author

Topic

ismail_issac
Starting Member

22 Posts

Posted - 2006-02-11 : 06:32:03

declare @A1 varchar(100), @A2 varchar(100),@A3 varchar(100), @StrSal varchar(10) , StrWord varchar(500)

Set @A1 = 'One'
Set @A2 = 'Two'
Set @A3 = 'Three'
Set @A4 = 'Thousand'
set @StrSal = '3000'

Execute('Select @StrWords = @A' + Len(@StrSal))
print @StrWords

I want to store the text from variable @A4 in the Variable @StrWord
based on the length of the string in variable @StrSal.
with The above querry i should be able to print text "THOUSAND"

How do i achive this???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-11 : 07:20:05

you have to use sp_executesql for this. It allows passing of parameters into it.

----------------------------------
'KH'
everything that has a beginning has an end

ismail_issac
Starting Member

22 Posts

Posted - 2006-02-11 : 07:26:45

Can i have an example of this ..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-11 : 07:32:12

Sure. But this looks a bit ridiculous.
Here it goes

declare
	@A1		varchar(20),
	@A2		varchar(20),
	@A3		varchar(20),
	@A4		varchar(20),
	@StrSal		varchar(100),
	@StrWords	varchar(100),
	@sql		nvarchar(100)

Set @A1 	= 'One'
Set @A2 	= 'Two'
Set @A3 	= 'Three'
Set @A4 	= 'Thousand'
set @StrSal 	= '3000'

select	@sql	= 'select @StrWords = @A' + convert(varchar(10), len(@StrSal))
print @sql

exec sp_executesql @sql, N'@A1 varchar(20), @A2 varchar(20), @A3 varchar(20), @A4 varchar(20), @StrWords varchar(100) OUTPUT', 
			@A1, @A2, @A3, @A4, @StrWords OUTPUT
print @StrWords

----------------------------------
'KH'
everything that has a beginning has an end

ismail_issac
Starting Member

22 Posts

Posted - 2006-02-11 : 08:45:25

Thank You very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources