Stored Procedure - Critical - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure - Critical

Author

Topic

tomjacob
Starting Member

8 Posts

Posted - 2002-02-02 : 20:36:15

Hi guys,

I am in the middle of a problem. Very critical.

I have a stored procedure.

Sometrhing like

select employeename from tab_employee where empid in (@empIdString)
( In Database sql7.0, this empid field is int)

This sp accepts a parameter empIdString adCHAR(1000). When I call this stored procedure in VB, I am preparing a string which will hold the empid seperarted by commas

For. Ex : 123,678,896

But, since the empid field in the table is a int, this is giving me problems. Any ideas to save my life?

Since Comma is ther in the string i cud not use Convert/Cast.

Please help.

Toms.

rknewbow
Starting Member

7 Posts

Posted - 2002-02-02 : 20:46:53

You'll have to use dynamic sql.

Something like this:

CREATE PROCEDURE GetEmployees
(
@empIdString char(1000)
)

AS

DECLARE @SQL nvarchar(4000)

SET @SQL = N'SELECT employeename from tab_employee where empid IN (' + @empIdString + ')'

EXEC sp_ExecuteSQL @SQL
GO

Edited by - rknewbow on 02/02/2002 20:47:59

Edited by - rknewbow on 02/02/2002 20:59:36

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-03 : 00:25:09

This question is also in our FAQ (http://www.sqlteam.com/FAQ.asp) with links to a couple of articles.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources