Problem Sending user input as parameters to SQL

[ktimov1]

I created a stored procedure in SQL which will accept parameters from a vb.net textbox.
I want it to output the count of records.

stored proc:

USE [Traffic]
GO
/****** Object:  StoredProcedure [CENTRAL\TIMOVKP].[spReturnValue]    Script Date: 08/02/2010 08:57:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CENTRAL\TIMOVKP].[spReturnValue]
(@table varchar(100),@field_name varchar(100),@field_value varchar(100), @sql nvarchar(4000) OUTPUT)
as
SET nocount on
--DECLARE @sql varchar(8000)
SET @sql=N'SELECT COUNT(*) FROM '+@table+' WHERE '+@field_name+' = '+char(39)+@field_value+char(39)
--EXEC sp_executesql @sql OUTPUT
EXEC sp_executesql @sql OUTPUT
Return @sqL

The stored proc runs successfully. The problem now is calling it in VB.

Dim connectionString As String = "Data Source=D1SSQL1\DEVELOPMENT;Initial Catalog=Traffic;Integrated Security=True;"
        Dim connection As SqlConnection = New SqlConnection(connectionString)

        Dim command As SqlCommand = New SqlCommand("spReturnValue")
        Dim ReturnedVal As Integer

        command.CommandType = CommandType.StoredProcedure

        command.Parameters.AddWithValue("@table", "tblSpeedStudy")
        command.Parameters.AddWithValue("@field_name", field_name.Text)
        command.Parameters.AddWithValue("@field_value", field_value.Text)
        command.Parameters.AddWithValue("@sql", ReturnedVal)

        command.Connection = connection
        connection.Open()

        command.ExecuteNonQuery()

        ReturnedVal = CInt(command.Parameters("@sql").Value)

        txtResult.Text = ReturnedVal

        connection.Close()

I get the following error after I input my values (strRoadName and US45):

Syntax error converting the nvarchar value 'SELECT COUNT(*) FROM tblSpeedStudy WHERE strRoadName = 'US45'' to a column of data type int.

[Coder Smurf]

The last line of your stored procedure is actually returning your SQL statement, not your count..

You need to define and output variable in your stored procedure.

I found this page a big help: http://www.sqlteam.com/article/stored-procedures-returning-data

About 2/3 of the way down it also shows how to get the parameter back and put the value into a ASP page. A few minor changes and it will work in .NET.

Hope this helps.