Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11

    Question Unanswered: Return a value using EXEC

    OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:

    CREATE PROCEDURE dbo.ExecFunction ( @ScalarSELECTString varchar(250), @@ReturnVal sql_variant )

    set @@ReturnVal = ( exec @ScalarSELECTString )

    go


    So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First, Dynamic SQL should be a last resort option...

    Do a google on SQL injection (security problem) and not to mention performance problems...


    Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

    You say you know db's and SQL...what's you're background?


    OK, so now the answer to your question...is it depends on the SQL...


    And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row....if you get a set, you'll only store the last value...

    But here's a sample...


    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @SQL varchar(4000), @rs varchar(255) OUTPUT
    AS
    BEGIN
    	DECLARE @cmd varchar(255)
    
    	CREATE TABLE #myTable99 (col1 varchar(8000))
    
    	
    	SELECT @cmd = 'INSERT INTO #myTable99(col1) ' + @SQL
    	EXEC(@cmd)
    	
    	SELECT col1 FROM #myTable99
    END
    GO
    
    DECLARE @rs varchar(255)
    EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @rs
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually...I forgot the assignment...

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @SQL varchar(4000), @rs varchar(255) OUTPUT
    AS
    BEGIN
    	DECLARE @cmd varchar(255)
    
    	CREATE TABLE #myTable99 (col1 varchar(8000))
    
    	
    	SELECT @cmd = 'INSERT INTO #myTable99(col1) ' + @SQL
    	EXEC(@cmd)
    	
    	SELECT @rs=col1 FROM #myTable99
    END
    GO
    
    DECLARE @rs varchar(255)
    EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @rs OUTPUT
    SELECT RTRIM(@rs)
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11
    My DB background is mostly in Sybase SQLAnywhere... I never found a way to do dynamic SQL statements in that, so I usually ended up writing them into the front-end (VB.NET) rather than the DB itself; I just think this would be much more useful.

    Security shouldn't be a problem for now, since there is no external access and we are a very small operation. As for the performance issue, I figured that might come up since it obviously can't pre-compile, but I wanted to have this as an option anyway so I don't have to repeat code.

    I'm in control of the statements. Like I said, the idea is to create a generic tool that can be used repeatedly when a single value is needed.

    As for creating separate sprocs... I assume "sprocs" are system procedures. How is this a better solution?

    Originally posted by Brett Kaiser
    First, Dynamic SQL should be a last resort option...

    Do a google on SQL injection (security problem) and not to mention performance problems...

    Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

    You say you know db's and SQL...what's you're background?


    OK, so now the answer to your question...is it depends on the SQL...


    And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row....if you get a set, you'll only store the last value...

    But here's a sample...


    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @SQL varchar(4000), @rs varchar(255) OUTPUT
    AS
    BEGIN
    	DECLARE @cmd varchar(255)
    
    	CREATE TABLE #myTable99 (col1 varchar(8000))
    
    	
    	SELECT @cmd = 'INSERT INTO #myTable99(col1) ' + @SQL
    	EXEC(@cmd)
    	
    	SELECT col1 FROM #myTable99
    END
    GO
    
    DECLARE @rs varchar(255)
    EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @rs
    GO
    
    DROP PROC mySproc99
    GO
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

  5. #5
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11
    Also, thanks for the solution anyway. I was hoping there would be some way to do it without a temp table, but I guess I'll go with that if I have to.

    Originally posted by Brett Kaiser
    First, Dynamic SQL should be a last resort option...

    Do a google on SQL injection (security problem) and not to mention performance problems...


    Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

    You say you know db's and SQL...what's you're background?


    OK, so now the answer to your question...is it depends on the SQL...


    And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row....if you get a set, you'll only store the last value...

    But here's a sample...


    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @SQL varchar(4000), @rs varchar(255) OUTPUT
    AS
    BEGIN
    	DECLARE @cmd varchar(255)
    
    	CREATE TABLE #myTable99 (col1 varchar(8000))
    
    	
    	SELECT @cmd = 'INSERT INTO #myTable99(col1) ' + @SQL
    	EXEC(@cmd)
    	
    	SELECT col1 FROM #myTable99
    END
    GO
    
    DECLARE @rs varchar(255)
    EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @rs
    GO
    
    DROP PROC mySproc99
    GO
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SPROC just mean stored procedure....

    And you're SQL statement would have to match the number of columns in the temp table...so I guess you'd also need dynamic sql to build the temp table to match...

    seems like a lot of hoop kumping...

    and what would you do when you get n rows back?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11
    The idea is that the SQL statement selects a single value, so I don't need to worry about multiple columns or multiple rows.

    I will be creating a stored procedure to do this... that was the whole point from the beginning. However, I just discovered that I can't execute a stored procedure from within a function. It seems like the user-defined function capability is fairly limited in SQL Server as compared to SQLAnywhere. Is there any way to get around that?

    Originally posted by Brett Kaiser
    SPROC just mean stored procedure....

    And you're SQL statement would have to match the number of columns in the temp table...so I guess you'd also need dynamic sql to build the temp table to match...

    seems like a lot of hoop kumping...

    and what would you do when you get n rows back?
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

  8. #8
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11
    One other thing... in SQLAnywhere, to do performance tuning, all you have to do is turn on Profiling and then you can view the execution time of any procedure or function. I assume the equivalent is SQL Profiler, but how do I see the execution time of a specific function?
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

  9. #9
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    11
    Never mind, I found it in Query Analyzer.
    Mike

    "Border relations between Canada and Mexico have never been better."
    -George W. Bush

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •