Using sp_executesql in Scalar value Function

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 2005 Forums

Transact-SQL (2005)

Using sp_executesql in Scalar value Function

Author

Topic

rauof_thameem
Starting Member

31 Posts

Posted - 2007-05-07 : 04:10:28

Hi all,,

I am using sql server 2005.., i am using dynamic query in scalar value function..

i have my query in nvarchar variable.
i am trying to execute it in
EXEC sp_executesql @var

but i am getting the following error..

Only functions and extended stored procedures can be executed from within a function.
Invalid attempt to read when no data is present.

Plz help me..,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 04:13:18

Please post the content of @var variable.

Peter Larsson
Helsingborg, Sweden

rauof_thameem
Starting Member

31 Posts

Posted - 2007-05-07 : 04:18:27

SET @var = N'SELECT TOP 1 @signup_date = SignupDate FROM '+
dbo.get_server_addr + N'ClientSignupMaster WHERE (ContractCode = @contract_code)'

EXEC sp_executesql @var

NOTE:--
dbo.get_server_addr-- retrieves the server address..
this query retrieves the data from multiple servers so i had a need of specifying the server addr..,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 04:22:14

SELECT @var = N'SELECT TOP 1 @signup_date = SignupDate FROM ' +
dbo.get_server_addr + N'ClientSignupMaster WHERE (ContractCode = @contract_code)'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources