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.

return a TABLE

hello all

I need a function with a return value TABLE...

this is not the problem..

but i need the returned table dynamic..

example:

i call the function getTable(schema_name, TableName)

the 1st value is the schema. the 2nd is the table i need...

now i must have a return value like this

select * from schema_name.TableName

is this possible? can i build by return value dynamically?

i tried a lot but nothing worked..

thx

greg

Give a look to CREATE FUNCTION in books online and you will find that in functions two things that you are not allowed to do are:

EXEC ( ' (any SQL statement)' ) EXEC aStoredProceduresql

Retrieving values from dynamic SQL

Anyone know if it's possible to retrieve a parameterized value from dynamically executed SQL?

Code Example

Declare @.table varchar(25)
Declare @.somevalue varchar(3)
Set @.table = 'sometable'
Set @.somevalue = 'somevalue'
Declare @.sqlBuild varchar(2000)
Set @.sqlBuild = 'DECLARE @.return varchar(3); ' +
 ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
exec (@.sqlBuild)

i want to be able to extract the value of @.return for later use. this procdure works fine but I need to grab that value somehow.

Any suggestions?put a the end of your stored procedure select @.return ...
Use a ExecuteScalar in your code and it will be returned ...|||i'm not quite sure what you mean...

can you give me an example?|||You will need to use sp_executesql in order to capture the OUTPUT parameter from a dynamic SQL statement. Something like this (note that your @.sqlBuild needs to be of type nvarchar):

DECLARE @.table varchar(25), @.somevalue varchar(3), @.return integer, @.sqlBuild nvarchar(4000)
SELECT @.table = 'sometable', @.somevalue = 'somevalue'
SELECT @.sqlBuild = ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
EXEC sp_executesql @.sqlBuild, N'@.return integer OUTPUT', @.return OUTPUT

Terrisql