Stored proc based on user input parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored proc based on user input parameters

Author

Topic

ktimov1
Starting Member

6 Posts

Posted - 2010-08-02 : 08:34:45

I created a SQL stored procedure which picks up values from a VB.NET application.


USE [Traffic]
GO
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))
as
SET nocount on
DECLARE @sql varchar(8000)
set @sql='SELECT COUNT(*) FROM '+@table+' WHERE '+@field_name+' = '+char(39)+@field_value+char(39)
EXEC (@sql)

From the VB.NET app, the user will enter the tablename, field, and value. Then a record count will be displayed to the user. However I only get a 0 count. It must be the syntax used in my SP. Any ideas?

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 09:05:08

Any non-alphanumeric (plus underscore) characters in @table or @field_name? or any embedded single quotes in @field_value?

FWIW this is a very bad way to accomplish this, is (relatively speaking) slow on performance and high on server resources, plus it is open to hacking by SQL injection

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 09:06:43

P.S. You need to display the value of @sql so you can see what it contains, that will enable you to try running that statement and seeing what error you get.

You could make @sql an OUTPUT parameter of your Sproc, and then it would be available to your application - which could display it back to you for debugging.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-02 : 09:09:54

procedure works fine. check your field_value!! and table definition.
try this:


create table emp
(e_id int
,date datetime)

insert into emp
select 10210, '2010/03/05' union all
select 10210, '2010/03/08' union all
select 10211, '2010/03/02' union all
select 10213, '2010/03/04' 


CREATE PROCEDURE [dbo].[sp_ReturnValue]
(@table varchar(100),@field_name varchar(100),@field_value varchar(100))
as
SET nocount on
DECLARE @sql varchar(8000)
set @sql='SELECT COUNT(*) FROM '+@table+' WHERE '+@field_name+' = '+char(39)+@field_value+char(39)
EXEC (@sql)

exec [dbo].[sp_ReturnValue] @table = 'emp', @field_name = 'e_id', @field_value = '10210'

ktimov1
Starting Member

6 Posts

Posted - 2010-08-02 : 09:39:22

My field_value and table definition are ok. If I test a query in sql:

select count(*) from tblSpeed where strRoadName = 'US45'

SQL will output 1 record, which is correct. However in my VB.NET app, with the parameters, 0 is the output. I tried displaying @sql as an output, but I think im doing something wrong, i'm new to this.


USE [Traffic]
GO
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 varchar(8000) OUTPUT)
as
SET nocount on

set @sql='SELECT COUNT(*) FROM '+@table+' WHERE '+@field_name+' = '+char(39)+@field_value+char(39)
EXEC (@sql)
Return @sql

the SP gets created successfully, however now in the VB.NET app, after I input the param values, I get a syxtax error saying:

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-08-02 : 09:46:00

what is the datatype of strroadname in the table tblspeedstudy....?

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 09:50:49

You don;t need to return @sql as the RETURN value = just defining it in the parameter list as an OUTPUT parameter is fine.

Return 0 for no error, non-zero for some indication of error, is the normal practice.

"If I test a query in sql:

select count(*) from tblSpeed where strRoadName = 'US45'

SQL will output 1 record, which is correct"

What about if you also test it using


DECLARE @strSql varchar(8000)
EXEC [CENTRAL\TIMOVKP].[spReturnValue] @table='tblSpeed', @field_name='strRoadName', @field_value='US45', @sql=@strSql OUTPUT
PRINT 'SQL=' + @sql

??? If that works then its a calling issue from your application.

ktimov1
Starting Member

6 Posts

Posted - 2010-08-02 : 10:29:59

strRoadName is varchar(100).

DECLARE @strSql varchar(8000)
EXEC [CENTRAL\TIMOVKP].[spReturnValue] @table='tblSpeed', @field_name='strRoadName', @field_value='US45', @sql=@strSql OUTPUT
PRINT 'SQL=' + @sql

The above worked. In my VB app I am calling my stored proc through the following code:


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 assumet the problem is when I declare ReturnedVal As Integer. However if I make it a String, ReturnedVal in the following line gets underlined saying that it is used before assigned a value...hmm so many problems..

command.Parameters.AddWithValue("@sql", ReturnedVal)

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 11:42:28

Your Sproc is created in the Schema [CENTRAL\TIMOVKP]

Not sure your APP is using that Schema? so it might be running a different variant, owned by another Schema (possibly "dbo")

ReturnedVal should expect an INT, not the value of @sql

You get the value of an OUTPUT parameter using something like:

command.Parameters.Add("@sql", SqlDbType.VarChar, 8000)
command.Parameters("@sql").Direction = ParameterDirection.Output
...
command.ExecuteNonQuery()
...
txtResult.Text = command.Parameters("@sql").Value

ktimov1
Starting Member

6 Posts

Posted - 2010-08-02 : 12:24:40

Ah we're getting closer..after adding your output parameter value:

command.Parameters.Add("@sql", SqlDbType.VarChar, 8000)
command.Parameters("@sql").Direction = ParameterDirection.Output
...
command.ExecuteNonQuery()
...
txtResult.Text = command.Parameters("@sql").Value

instead of an error, textbox just shows my sql query:

SELECT COUNT(*) FROM tblSpeedStudy WHERE strRoadName = 'US45'

txtResult shows that rather than the count output..hmm

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 13:17:49

"txtResult shows that rather than the count output..hmm"

Have VB.NET display the recordset that the Stored Procedure is returning (well, it probably isn't given that you are using ExecuteNonQuery, but the Stored procedure is returning a RecordSet that you could display)

ktimov1
Starting Member

6 Posts

Posted - 2010-08-02 : 14:14:26

hm..Could you provide an example of how to display a recordset returned from a stored procedure?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-02 : 14:29:30

There's no point in using a stored procedure for this. You're supplying almost all the syntax and executing it without any safety or validation. Just construct the command in your application and execute it as CommandText.

Kristen
Test

22859 Posts

Posted - 2010-08-03 : 04:55:58

quote:
Originally posted by ktimov1

hm..Could you provide an example of how to display a recordset returned from a stored procedure?

Sorry, but I have never programmed in .Net so I have no idea; you probably need a dot net forum for that sort of information (or a course / book perhaps?)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-08-03 : 09:36:41

Cross-poster. :(
http://www.dbforums.com/microsoft-sql-server/1658961-stored-procedure-based-user-input-parameters.html#post6471370

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Kristen
Test

22859 Posts

Posted - 2010-08-03 : 10:29:53

Cross forum posting doesn't bother me - largely different audiences.

Not telling us does - we're just wasting our time, or the dbForum folk are, repeating what other people have already taken the trouble to type ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources