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

Author

Topic

sbrazier
Starting Member

19 Posts

Posted - 2008-01-28 : 16:06:00

I've create a stored procedure where I want to pass in three parameters (ssn1, ssn1, acctNums)
These are all varchar types

However the acct number field in the db table is a numeric.

The procedure just is a select statement ex:
@acctNums is a string of accounts number '12345,12346,12347'

select * from account where ((ssn1 = @ssn1 or ssn2 = @ssn1) or (ssn1 = @ssn2 or ssn2 = @ssn2) AND acct_num not in (@acctNums)

Can this be done? and if so, how?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 16:38:24

See if the solution to this post will help point you in the right direction:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96296

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-28 : 16:41:20

You either need to parse out the numbers and put them into a table variable and join it with Accounts table, OR use dynamic SQL.
Search for parsing functions on these forums and you will find plenty of code sampls..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sbrazier
Starting Member

19 Posts

Posted - 2008-02-07 : 08:06:47

Here's my solution.. Thanks for your responses

DECLARE @tempTable table (acct_num int NOT NULL)

declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(500)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

if(@array_value <> '')
begin
Insert into @tempTable (acct_num) Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position,
end
end

select * from account where (acct_num NOT IN (Select acct_num from @tempTable))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources