How to return error message from SQL Server to web page

Question

I am currently working on a project that need to connect with database. The syntax for querying the data in database comes from the user input on a textbox. If the user input for the query produce an error then I need to return the error message from the SQL Server, if there is no error then the result of the query (in this case a SELECT statement) will be displayed in a grdiview.

After looking through the web I found that I need to create stored procedure. I never used a stored procedure before so I don't know how to put the retrieving data.

Here is my code so far:

In SQL Server:

Create table tbl_dormproc
(
    [ID] int identity(1,1),
    [Name] varchar(150),
    [Date of Birth] date,
    [Address] varchar(100)
)

CREATE PROCEDURE dormproc(@syntax NVARCHAR(MAX))
AS
BEGIN
BEGIN TRY
    EXEC sp_executesql N'@syntax'
    SELECT NULL
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
END CATCH
END
GO

In VB.NET application

Protected Sub btnCheck_Click(sender As Object, e As EventArgs) Handles btnCheck.Click
    Try
        cmd = New SqlCommand("dormproc", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@syntax", txtQuery.Text)

        drDataReader = cmd.ExecuteReader
        dtDatatable.Load(drDataReader)
        Gridview1.DataSource = dtDatatable
        Gridview1.DataBind()
    Catch ex As SqlException
        lblMsg.Text = ex.Message         
    End Try
End Sub

but, it produce an error 'must declare the scalar variable @syntax' inside the gridview.

I have set the @syntax parameter in the code above.

So I think the code doesn't read the parameter.

Also why the error displayed in a gridview not in lblmsg.text ?

What I am doing is to return the error from SQL Server into a label.

For example:

select *, from tbl_dormproc

it produces a result :

Incorrect syntax near the keyword 'from'.

Is it possible to pass the whole query into stored procedure?

Through browsing, so far I only found how to pass a certain column name into stored procedure.

Thanks in advance.

Answer 1

You're not using sp_executesql correctly.

Try this

CREATE PROCEDURE dormproc(@syntax NVARCHAR(MAX))
AS
BEGIN
    EXEC sp_executesql @syntax 
    SELECT NULL
END
GO