SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored proc based on user input parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ktimov1
Starting Member

USA
6 Posts

Posted - 08/02/2010 :  08:34:45  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 08/02/2010 :  09:05:08  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  09:06:43  Show Profile  Reply with Quote
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.
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 08/02/2010 :  09:09:54  Show Profile  Reply with Quote
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'
Go to Top of Page

ktimov1
Starting Member

USA
6 Posts

Posted - 08/02/2010 :  09:39:22  Show Profile  Reply with Quote
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.


Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 08/02/2010 :  09:46:00  Show Profile  Reply with Quote
what is the datatype of strroadname in the table tblspeedstudy....?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  09:50:49  Show Profile  Reply with Quote
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.
Go to Top of Page

ktimov1
Starting Member

USA
6 Posts

Posted - 08/02/2010 :  10:29:59  Show Profile  Reply with Quote
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)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  11:42:28  Show Profile  Reply with Quote
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

Edited by - Kristen on 08/02/2010 11:46:10
Go to Top of Page

ktimov1
Starting Member

USA
6 Posts

Posted - 08/02/2010 :  12:24:40  Show Profile  Reply with Quote
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




Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  13:17:49  Show Profile  Reply with Quote
"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)

Edited by - Kristen on 08/02/2010 13:20:19
Go to Top of Page

ktimov1
Starting Member

USA
6 Posts

Posted - 08/02/2010 :  14:14:26  Show Profile  Reply with Quote
hm..Could you provide an example of how to display a recordset returned from a stored procedure?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 08/02/2010 :  14:29:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/03/2010 :  04:55:58  Show Profile  Reply with Quote
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?)
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/03/2010 :  09:36:41  Show Profile  Reply with Quote
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.
________________________________________________
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/03/2010 :  10:29:53  Show Profile  Reply with Quote
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 ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000