Hi,
I need to to run an sql query that is stored in string variable, like this:
DECLARE @.wherestr nvarchar
DECLARE @.sqlstring nvarchar
SET @.wherestr = .... some more code to fill @.wherestr
SET @.sqlstring =
'SELECT @.tgid = tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr
But when I call this command using sp_executesql:
EXEC sp_executesql @.SQLString
I recieve following error: "Only functions and extended stored procedures can be executed from within a function."
Is there any possibility to run the command that is stored in some string (nvarchar) variable inside the function?
Note: I'm using SQL Server 2005 Express, Management Studio
thanks for any help
Jiri Matejka
Jiri,
I don't see where you are trying to declare any function.
You can use
EXEC( @.sqlstring)
I am also not sure what you get when you declare something as "nvarchar". Perhaps just "nvarchar(1)". So maybe you want "nvarchar(8000)" or something like that. I have seen others use "nvarchar(max)", which I think is related to some "max" value used when setting up the SQL Server software.
Here is what MS documentation says about "nvarchar" without the size declaration:
http://msdn2.microsoft.com/en-us/library/ms186939.aspx
When n is not specified in a data definition or variable declaration statement, the default length is 1.
The MS terminology for this is DYNAMIC SQL. You can search this forum for it, as well as Microsoft documentation. There are some excellent internet articles with caveats concerning the use of DYNAMIC SQL. You will find links to these in various posts in this forum.
HTH.
Dan
|||
The function I wrote about is something like this
CREATE FUNCTION [dbo].[GetTargetGroup]
(
@.sex int, @.age int
)
RETURNS int
AS
BEGIN
DECLARE @.wherestr nvarchar(255)
SET @.wherestr=@.sexstr+' AND '+@.agecatstr
DECLARE @.sqlstring nvarchar(255)
some more code here ...
SET @.sqlstring = 'SELECT @.tgid = tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr
EXEC sp_executesql @.SQLString
IF @.tgid IS NULL
SET @.tgid=-1
RETURN @.tgid
END
There is no problem with nvarchar, I use it like "nvarchar(255)", I've just shortened the code to be more readable.
Thanks for your help, but when I call EXEC @.SQLstring, that it causes error of "
Could not find stored procedure 'SELECT @.tgid = tgid FROM target_groups WHERE sex=2 AND agecat=32'". So it looks like if EXEC wants to run stored procedure, not given command. But your reference to "DYNAMIC SQL" is a good hint. I'll check it out.
Jiri Matejka
|||
You may not be able to use EXEC(@.SQLString) inside of a function. (But I seem to recall instances where I do just that.)
I think you also need some "N" in front of your strings you are placing in your NVARCHAR variables, as shown in http://msdn2.microsoft.com/en-us/library/ms188001.aspx .
I hope that will work for you. I don't see anything wrong with the SQL statement.
Dan
|||
You cant use dynamic SQL (sp_executesql or Exec ()) on function.
Change your logic to SP.
Possible Alternative,
Create Temp Table on calling proc
Insert data on Callable Proc on the Created Temp table
After the calling use the temp table on your query.
Create Temp table on calling proc
Insert the callable Proc output in Temp table
Use it on your rest of code.
|||i think its not possible cannot use sp_executesql inside a function
what you can do is try to convert your function into a procedure
Code Snippet
CREATE PROCEDURE [dbo].[GetTargetGroup]
(
@.sex int, @.age int
)
AS
DECLARE @.wherestr nvarchar(255)
SET @.wherestr=@.sexstr+' AND '+@.agecatstr
DECLARE @.sqlstring nvarchar(255)
some more code here ...
SET @.sqlstring = 'SELECT ISNULL(tgid,-1) AS tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr
EXEC sp_executesql @.SQLString
GO
you can get your result by
Code Snippet
INSERT
INTO #TGIDResult
EXEC GetTargetGroup @.theSex, @.theAge
SELECT @.tgid = tgid
FROM #TGIDResult
DROP TABLE #TGIDResult
|||
The issue with this technique is that the @.SQLString executes in a separate scope. Here's how you can pass results from the @.SQLString back to your code:
DECLARE @.wherestr varchar(8000) --in SQL 2005, you may use varchar(max)
DECLARE @.sqlstring varchar(8000)
declare @.tgid bigint
--Create a temp table to hold results: in 2005, you can use @.Table rather than #Table if you prefer
select @.tgid as TG_ID into #TGID where 1 = 2
SET @.wherestr = .... some more code to fill @.wherestr
SET @.sqlstring =
'SELECT @.tgid = tgid '+
' FROM target_groups '+
' WHERE ' + @.wherestr
insert into #TGID
exec(@.SQLString)
select * from #TGID
drop table #TGID
dedyandy: