How can I use sp_executesql make this.

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

General SQL Server Forums

New to SQL Server Programming

How can I use sp_executesql make this.

Author

Topic

cajina
Starting Member

15 Posts

Posted - 2008-01-31 : 18:40:57

I have this code:

USE BDPrincipal
GO

IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO

CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
Return @var
end
GO

exec sp_executesql aquery;

I created that code to prove if it works.
But the result doesn´t appear, and the message is:

Command(s) completed successfully.

I need it to work.

Because I need to create a very dinamic query.

Please help me!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-31 : 20:46:01

[code]declare @sql nvarchar(4000)
select @sql = dbo.aquery()

exec sp_executesql @sql[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-31 : 23:07:59

quote:
Originally posted by cajina

I have this code:

USE BDPrincipal
GO

IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO

CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
exec sp_executesql (@var)
end
GO

I created that code to prove if it works.
But the result doesn´t appear, and the message is:

Command(s) completed successfully.

I need it to work.

Because I need to create a very dinamic query.

Please help me!

put sp_executesql inside the function itself

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 03:07:41

you can't have sp_executesql inside a function.

KH
[spoiler]Time is always against us[/spoiler]

cajina
Starting Member

15 Posts

Posted - 2008-02-01 : 14:35:38

Thank you It helps me a lot. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources