How to build a query into string variable and run it

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