CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Parvathi Nair
Parvathi Nair

Reputation: 13

SQL Server stored procedure with vb.net

I am new to SQL Server stored procedure.

Please help to understand why the output is not coming.

I have a stored procedure sp_sample where am giving a select query with conditions

PROCEDURE [dbo].[sp_sample]
    @dep nvarchar(50)
AS
BEGIN
    select * FROM emp WHERE dep= '+ @dep +'
END

and the exceution code in vb.net is

lconn = New SqlConnection(DpConnectionString)
lconn.Open()

lcmd = New SqlCommand("sp_sample", lconn)
lcmd.CommandType = CommandType.StoredProcedure

lcmd.Parameters.Add(New SqlParameter("@dep", Data.SqlDbType.VarChar)).Value = TextBox1.Text.ToString
'  lcmd.Parameters.Add(New SqlParameter("@pnv_String", Data.SqlDbType.VarChar)).Value = ""
ladp = New SqlDataAdapter(lcmd)
lds = New DataSet
ladp.Fill(lds)

DataGridView1.DataSource = lds.Tables(0)
DataGridView1.Refresh()

Upvotes: 0

Views: 167

Answers (2)

Binary Worrier
Binary Worrier

Reputation: 51709

At a guess (without sample input and sample data all I can do is guess) your problem is here WHERE dep= '+ @dep +'

Lets say you set dep to a value e.g. select @dep = "Parvathi"

and in the table a value of dep is Parvathi, what you are actually looking to match is the string " + @dep + ", which will never match the string "Parvathi".

Your where clause should (probably) be WHERE dep= @dep

Upvotes: 5

gzaxx
gzaxx

Reputation: 17590

Change your procedure to this:

PROCEDURE [dbo].[sp_sample]    
   @dep nvarchar(50)    
AS 
BEGIN

   select * 
   FROM emp 
   WHERE dep = @dep

END

It seems like you created a stored procedure from inline query.

Upvotes: 0

Related Questions