none
How to build a query into string variable and run it

    Question

  • 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

Answers

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:

     

    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

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

     


     

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

    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
    )
    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




    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

    exec(@SQLString)

     

    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

    http://www.sommarskog.se/dyn-search-2008.html

    http://www.sommarskog.se/dynamic_sql.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    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
    Answerer