March 25, 2008 at 3:55 am
Hi everyone
My stored procedure accepts 2 parameters: names, number of names like this: (name1, name2, name3,3) Problem is: the number of names changes each time i want to run it. Some time it is: Name1,1 and other time it is: name1, name2,2. So every time i run it i need to change code. I thought "arrays" might solve that problem but i never met arrays in sql2005. Does it exist ? Case answer is "no": What is it to replace arrays and solve the above problem?
Thanks
March 25, 2008 at 4:46 am
No arrays. Some people use XML, but that can get messy.
If you go into a bit more detail on what you're doing, maybe we can suggest an alternative.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 5:48 am
No arrays, unless you think of a key value pair as a as a one dimensional array.
It looks to me like you are speaking about variable parameter lists.
There are quite a few ways.
The most common ones that I see use delimited lists (of names, in your case), as the parameter, then use a table udf to split it into a table variable. There are tons of tons of udfs on the net will do that, including one at SQLCentral.
Another way might be to use a preagreed table to store the names.
March 25, 2008 at 5:56 am
I agree with all of the above. I have used both XML and delimited strings to pass in variable-length lists. If you are not using XML anywhere else, then the delimited string will probably be easier to figure out and use.
In either case, to make it easier, you can drop the 'count' parameter because you will be able to figure out from the data how many 'names' you have. If you use a delimited string, choose a delimiter that won't be used in the string (this sounds obvious, but some people don't think about and find out the hard way).
Hope this helps!
If it was easy, everybody would be doing it!;)
March 25, 2008 at 6:43 am
One more for it depends. With a very simple comma delimited list, we've found that a UDF can perform well enough to turn that into data within a query. For anything more complex we're using XML.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 25, 2008 at 9:16 am
GilaMonster (3/25/2008)
No arrays. Some people use XML, but that can get messy.If you go into a bit more detail on what you're doing, maybe we can suggest an alternative.
Hi Gail, OK I understand there are no arrays.
I create a table and i want to split it into "sub lists" (or "sub tables") according to the number of users in a list i send to the procedure. For example, my table contains 10000 phone numbers and i want: 'aaa', 'bbb' and 'ccc' to make phone calls to all the phones in the list. I create a "sub table" and i name it 'aaa' and insert third of the records from the main table. Then i create another "sub table" calling it 'bbb' etc.. The number of users changes, it may contain 1 user or 1000.
Thanks for trying to help
March 25, 2008 at 9:25 am
I'd like to thank you people for helping me and i'd like to give "points" but i dont know that "technique" works:hehe:. Can anyone share with me that knowlede?
Thanks
March 25, 2008 at 11:58 pm
blahknow (3/25/2008)
Hi Gail, OK I understand there are no arrays.I create a table and i want to split it into "sub lists" (or "sub tables")
Hmmm. Ok. Why do you need the sub tables? What are they then used for?
You can't assign points to people on this forum
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2008 at 9:38 am
Another way you can go is making a temp table (#) and use that in the stored proc. As long as you create it in the outer session that's calling this stored proc you're creating, it'll be in scope. I generally go that way instead of a delimited string only because in my work I generally have to contend with table/field names that invariably contain a character you might use for delimiting, and it's just easier this way.
Is this an input to an app? If so, do you really need subtables? Or could you just create one table with the result set, and do a filter depending on the user requested? Just because if you have subtables, then you need to agree on a naming convention, figure out cleanup, etc...
March 26, 2008 at 11:17 am
Hi Gail,
You've got 3213 points, how did you get it, from another forum ? 😀
I need to give people lists of phone numbers.
Any time i do that the number of people to whom i deliver the lists changes. People names change too.
I derive the phones' list from a table on which i run a query such as:
SELECT phone FROM table WHERE city IN('aaa','bbb','ccc').
The result shoud be devided to 3 if the parameter contains 3 people and should be sent to them according to the names that parameter contains.
I thought i'd use an array as parameter such as :
Exec sp_phones {'name1', 'name2','name3'}
or even:
Exec sp_phones {'(name1', 'name1@mail.com'),('name2','name2@mail.com'),etc...}
I've been told i must send a string and create a function to change the string into a table and i guess thats what i'll do unless i hear a better idea.
Thanks
March 26, 2008 at 11:22 am
Hi afalso,
and thanks.
I didnt understand how to create a temp table without sending the sp thet is creating it a list of names to populate that table.
Thanks
March 26, 2008 at 11:39 am
blahknow (3/26/2008)
Hi afalso,and thanks.
I didnt understand how to create a temp table without sending the sp thet is creating it a list of names to populate that table.
Thanks
(dfalso, btw)
No prob, just make sure you create the temp table in a higher-level scope. I.e., this works:
create proc usp_inner as
begin
select * from #temp
end
create proc usp_outer as
begin
create table #temp ( thisfield int )
exec usp_inner
end
and this doesn't:
create proc usp_inner1 as
begin
create table #temp ( thisfield int )
end
create proc usp_inner2 as
begin
select * from #temp
end
create proc usp_outer as
begin
exec usp_inner1
exec usp_inner2
end
March 26, 2008 at 11:48 am
blahknow (3/25/2008)
GilaMonster (3/25/2008)
No arrays. Some people use XML, but that can get messy.If you go into a bit more detail on what you're doing, maybe we can suggest an alternative.
Hi Gail, OK I understand there are no arrays.
I create a table and i want to split it into "sub lists" (or "sub tables") according to the number of users in a list i send to the procedure. For example, my table contains 10000 phone numbers and i want: 'aaa', 'bbb' and 'ccc' to make phone calls to all the phones in the list. I create a "sub table" and i name it 'aaa' and insert third of the records from the main table. Then i create another "sub table" calling it 'bbb' etc.. The number of users changes, it may contain 1 user or 1000.
Thanks for trying to help
What you could do alternatively is accept as a parameter the number of names, then assign the records you're pulling into buckets based on that. Depending on your requirements (random assignment, semi-random, condition-driven), you'd do this in different ways. Then, take that result set and assign your users, one to a bucket. If your user list is a table, that's a simple join. If not, I'd build a CASE statement or maybe loop over the user list, depending on requirements there.
My two cents...
March 26, 2008 at 11:58 am
dfalso (3/26/2008)
and this doesn't:
create proc usp_inner1 as
begin
create table #temp ( thisfield int )
end
create proc usp_inner2 as
begin
select * from #temp
end
create proc usp_outer as
begin
exec usp_inner1
exec usp_inner2
end
This one won't work. inner1 will create (and dispose of) the table. It will NOT be visible to Inner2
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 26, 2008 at 12:01 pm
Right, that's what I wrote 🙂
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply