I have an issue in selecting a value into variable

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)

I have an issue in selecting a value into variable

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-21 : 07:55:48

VIJAY writes "I have an issue in selecting a value into variable in dynamic SQl

This query was like this...

Set @resultset='Select @abc=Sum(amount) from @testtable'

Exec @resultset
(Here the table name is varialble ,So i am using dynamic SQl.)

Insert into XYZ values(@abc)
I want to insert the value in variable @abc into XYZ table.
I know if i keep this insert statement in the same string that can be printed.But i have 80 selects and the string size was getting more than 8000 characters and it was no allowing to do like that.
Exec @resultset is getting executed,but the value in variale @abc was not getting outside that string.

Thanks,
Vijay"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-21 : 08:22:30

Try this out


DECLARE @SQLString nvarchar(1000),
	@ParamDef nvarchar(1000),
	@rtnVal nvarchar(1000)

SELECT @SQLString = 'SELECT @rtnVal = convert(varchar,au_id) From pubs.dbo.authors Where au_lname=''Blotchet-Halls'''
Select @ParamDef = '@rtnVal nvarchar(1000) OUTPUT'

EXEC dbo.sp_executesql @SQLString, @ParamDef, @rtnVal OUTPUT 

Select @rtnVal

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources