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.

Retrieving image from SQL database

Ok, again, I'm reasonably new to this. I've been trying to display an image stored in SQL in a ASP.NET page. Pretty simple stuff I would have thought. I've read countless examples of how to do this online, and many of them use the same method of displaying the image, but none seem to work for me. The problem seems to lie in the following line of code:

Dim imageDataAsByte() =CByte(command.ExecuteScalar())

Which always returns the error: Value of type 'Byte' cannot be converted to '1-dimensional array of Byte'.

Here's the rest of my code, hope someone can help. It's doing my head in!

Imports System.Data.SqlClient

Imports System.Data

Imports System.Drawing

Imports System.IO

PartialClass _ProfileEditor

Inherits System.Web.UI.Page

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

'Get the UserID of the currently logged on user

Dim NTUserIDAsString = HttpContext.Current.User.Identity.Name.ToString

Session("UserID") = NTUserID

Dim PhotoAs Image =Nothing

Dim connectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)

Dim commandAs SqlCommand = connection.CreateCommand()

command.CommandText ="SELECT Photograph, ImageType FROM Users WHERE UserID = @.UserID"

command.Parameters.AddWithValue("@.UserID", NTUserID)

connection.Open()

Dim imageDataAsByte() =CByte(command.ExecuteScalar())Dim memStreamAsNew MemoryStream(Buffer)

Photo = Image.FromStream(memStream)

EndSub

EndClass

Onwww.SingingEels.com we use images (and other files like zip files, source code etc) in our database, and we show them through an ASP.NET page just like you're trying to do.

There are a few things though with the above that are an issue:

scottishfruit:

Dim imageDataAsByte() =CByte(command.ExecuteScalar())

The problem here (as your compiler is trying to tell you) is that you are trying to assign a BYTE to a BYTE_ARRAY object... to put it in human terms... a BYTE is a pair of shoes... and a BYTE_ARRAY is a shoe store... so when your friend asks you where the nearest shoe store is, and you pointed at your shoes, he yells at you :)

That's what the compiler is doing... so the long and the short of it is... you need toCAST the results from the ExecuteScalar function to a BYTE_ARRAY... like this:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte()) <-- (I haven't done VB in a very long time, but I think that's right).

Ok, to "read" that in human speak you would say: "Create a variable named 'imageData' which happens to be an array of bytes and assign it the value of whatever comes from the fuction 'command.ExecuteScalar()' which I know is also a byte array."

I'm sure 100 people have probably posted quick answers already, but if not... let me know if this solves your problem. (or if I lost you all together)

|||

'hey buddy chk out these link>>>

http://aspalliance.com/articleViewer.aspx?aId=140

http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp

i hope it will help u>>>

have a great day!

mark the post as answer if it helped u>>

|||

Awesome! That did the trick! (and your shoe analogy was pretty cool too)

Alas I now have another problem. This line:

Photo = Image.FromStream(memStream)

Is giving me this error: System.ArgumentException: Parameter is not valid.

Once again, I've googled this to pieces and there are heaps of solutions, but none that actually work!

Here's my code again:

Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System.IO

Partial Class _ProfileEditor

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim NTUserID As String = HttpContext.Current.User.Identity.Name.ToString

Session("UserID") = NTUserID

Dim Photo As Image = Nothing

Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("PeopleConnectionString").ConnectionString)

Dim command As SqlCommand = connection.CreateCommand()

command.CommandText = "SELECT Photograph, ImageType FROM Users WHERE UserID = @.UserID"

command.Parameters.AddWithValue("@.UserID", NTUserID)

connection.Open()

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Dim memStream As New MemoryStream(imageData)

Photo = Image.FromStream(memStream)

End Sub

End Class

|||

Well, this isn't really an answer to your "what's with the argument exception" error... but I think you're almost done... there's no need to create a MemoryStream, or an Image... at this point, you already have the image data (as you so nicely named your variable)... so all that remains is for you to send that data out to the user!

scottishfruit:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Dim memStream As New MemoryStream(imageData)

Photo = Image.FromStream(memStream)

End Sub

End Class

Change the above... to this:

Dim imageData As Byte() = CType(command.ExecuteScalar(), Byte())

Response.OutputStream.Write(imageData, 0, imageData.Length)

Response.AddHeader("content-type", "image/jpeg")

Response.End()

End Sub

End Class

I know the image might not always be a JPEG... so you can leave that part out, but it's fine to use even if the image is a GIF, BMP or whatever... but if you choose to leave it out, some browsers may not appreciate that you are trying to send a picture of some kind :)

Enjoy (and when you're done... mark one of these posts as the "answer" so the thread is closed)