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.
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 typesHowever 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 responsesDECLARE @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, endendselect * from account where (acct_num NOT IN (Select acct_num from @tempTable)) |
 |
|
|
|
|
|
|