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

    Sunday, April 22, 2007 5:18 PM


All replies

  • 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:



    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.





    Sunday, April 22, 2007 7:21 PM
  • The function I wrote about is something like this


    CREATE FUNCTION [dbo].[GetTargetGroup]

     @sex int, @age int
    RETURNS int
        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


    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



    Sunday, April 22, 2007 8:13 PM
  • 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 .


    I hope that will work for you.  I don't see anything wrong with the SQL statement.



    Sunday, April 22, 2007 8:28 PM
  • 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.

    Monday, April 23, 2007 11:58 AM
  • 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

    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

    you can get your result by

    Code Snippet


    INTO   #TGIDResult

    EXEC   GetTargetGroup @theSex, @theAge

    SELECT   @tgid = tgid

    FROM   #TGIDResult


    Wednesday, April 25, 2007 9:04 PM
  • 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



    select * from #TGID


    drop table #TGID



    Thursday, April 26, 2007 12:11 AM
  • First of all don't create SQL function for this requirement, you wont be able to execute it. What you can do is

    Instead of creating a function you can create an other stored procedure which accepts same input parameters and keep all your code as is in stored procedure, it should work fine

    You can declare @SQLString  as varchar(8000), no need to declare it as nvarchar

    Mark this post as answer if this resolves your issue.

    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

    Monday, January 21, 2013 5:40 AM
  • Do not use this technique, read Erland's articles

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 6:02 AM