Store Procedure - SQL Server Forums

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

Store Procedure

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-05-24 : 02:04:33

iam using store procedure to return certain no. of columns frm table.the query is
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ")

i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records.
i tried this in query analyzer as well it runs normally returing rows.
only in store procedure it gives me blank record.
any suggestions what is wrong ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 02:08:36

quote:
i tried this in query analyzer as well it runs normally returing rows.
only in store procedure it gives me blank record.

You mean running your stored procedure in query analyzer will give you blank records ? Can you post your stored procedure here ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 02:09:26

Post the code of sp you used

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-05-24 : 02:11:39

here is the sotred procedure.i get blank records in my datagrid of .aspx page whenever i run this store procedure.otherwise if i use this sql query in my coding of .aspx page i get normal reults

CREATE PROCEDURE Employer_EmpAddress(@txtID varchar(100))
AS
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (@txtID)
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 02:12:58

if you are only passing single value to @txtID

WHERE empuser_id = @txtID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 02:14:27

How did you send parameter value? is it CSV?

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-05-24 : 02:15:32

it can be single as wellas multiple comma seperated ids depending on user input.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 02:17:07

refer to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6134

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 02:30:18

Also refer
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-05-24 : 02:31:24

this is how the values are passed
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN ('VIBA1','VIBA2')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 02:35:44

Read the links specified to make it work when sending as parameters to sp

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 02:41:24

Another reference here
Here is one implementation

CREATE PROCEDURE Employer_EmpAddress(@txtID varchar(100))
AS
BEGIN
    SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile 
    FROM   employee_master e inner join dbo.CSVTable(@txtID) c
    ON     e.empuser_id = c.stringval
END

you can obtain CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources