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

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|||Actually...I forgot the assignment...

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

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

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|||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?|||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?|||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?|||Never mind, I found it in Query Analyzer.