September 24, 2008 at 7:50 pm
I am trying to write a stored procedure that takes a number of integer values and runs a query on them using an "IN" clause in the Stored Procedure. A small example of the stored procedure is below.
CREATE PROCEDURE [dbo].[BuildResourceTree2]
@Parent_Res_List varchar(4000)
AS
BEGIN
SET NOCOUNT ON;
SELECT Parent_Res, Sequence, ResRcId, ResRc_Name
FROM Resource
Where Parent_Res In (@Parent_Res_List) and [Type] = 1
Order By Parent_Res, Sequence, ResRcId
END
GO
When I run the stored procedure by running "exec buildresourcetree2 '0,700'" i get the following error:
Msg 245, Level 16, State 1, Procedure BuildResourceTree2, Line 10
Conversion failed when converting the varchar value '0,700' to data type int.
My question is how can I pass a number of integer values to a stored procedure and use them in my query above?
Thanks
September 24, 2008 at 8:40 pm
Hi there,
Two solutions that I can quickly see for your problem:
1- Keep the varchar parameter and in your code, split it into integer using a separator and for each of them, convert it to an INT. Otherwise I believe SQL just thinks you're trying to send in one big string instead of a series of parameters as integers
2- pass a table as a parameter to your function, which would allow you to use JOINs and other operators that would not limit you in your number of integers you can pass in your list
Hope this helps,
Greg
September 24, 2008 at 8:54 pm
hi,
Nevermind my second solution, it's not possible in sp... my bad.
Maybe something like this: http://www.sqlservercentral.com/articles/News/3182/ would do? But I'm not sure it's a great idea neither. If you can't do without, the first solution, although not necessarily very pretty, would do.
Another idea otherwise is to use the varchar directly in a dynamic SQL statement that you can execute using EXEC
Greg
September 24, 2008 at 10:41 pm
Thanks for your reply Greg,
I think I over simplified my request in the hope of making it easier to understand.
What I ultimately need to do is add records to a temp table in a particular order, due to the database structure I can't think of a way to do this through a single query. So I need to run multiple queries, each query having a where parameter derrived from the previous query.
I'll try to step out exactly what I am trying to do (I'm fairly new to stored procedures and I know some of the things I'm doing is not recommended ie cursors but at this stage it's all I can think of)
1) Create a list of integers to pass to a query.
2) Assign this list to a local variable @Query_List varchar(4000)
3) Create a cursor which contains the query in my first post
4) Clear the variable @Query_List
5) for each record in the cursor write it to the temp table
6) for each record in the cursor add a field (ResRcId) to the variable @Query_List
7) Repeat steps 3 - 6 Until @Query_List is empty
8) Return all records from the TempTable
So the result set from the query in step 3 get added to the variable and then used for the next loop.
I have got around the issue now by creating a second temp table that just contains 1 Int Field, and instead of adding to the variable, just insert the ID into that temp table.
It seems to be working but not sure it's as efficient as possible (I still haven't cleaned up all the Declarations I made whilst testing different solutions)
CREATE PROCEDURE [dbo].[BuildResourceTree]
AS
Declare @Parent_Res Int
Declare @Sequence Int
Declare @ResRcId Int
Declare @ResRc_Name varchar(60)
Declare @Search_List nvarchar(max)
Declare @Temp_List varchar(4000)
Declare @SQL nvarchar(4000)
Declare @TempCount Int
BEGIN
SET NOCOUNT ON;
Create Table #ResourceTree(
ResourceTreeId Int IDENTITY(1,1) NOT NULL,
Parent_Res Int NOT NULL,
Sequence Int NOT NULL,
ResRcId Int NOT NULL,
ResRc_Name nvarchar(60) COLLATE Latin1_General_CI_AS )
Create Table #Parent_List(
Parent_Res int)
Insert Into #Parent_List (Parent_Res) Values(0)
Select @TempCount=Count(Parent_res) from #Parent_List
WHILE @TempCount <> 0
BEGIN
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT Parent_Res, Sequence, ResRcId, ResRc_Name
FROM Resource
Where Parent_Res In (Select Parent_Res From #Parent_List) and [Type] = 1
Order By Parent_Res, Sequence, ResRcId
OPEN c1
Truncate Table #Parent_List
FETCH NEXT FROM c1
INTO @Parent_Res, @Sequence, @ResRcId, @ResRc_Name
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into #ResourceTree
(Parent_Res, Sequence, ResRcId, ResRc_Name)
Values(@Parent_Res, @Sequence, @ResRcId, @ResRc_Name)
Insert Into #Parent_List (Parent_Res) Values(@ResRcId)
FETCH NEXT FROM c1
INTO @Parent_Res, @Sequence, @ResRcId, @ResRc_Name
END
CLOSE c1
DEALLOCATE c1
Select @TempCount=Count(Parent_res) from #Parent_List
End --while loop
SELECT * From #ResourceTree
Order By ResourceTreeId
END
GO
September 24, 2008 at 11:20 pm
I think it will be lot easier if you use the XML approach. You can return a rowset containing the values in your delimited string and use that directly in the IN() clause. Something like:
DECLARE @STR VARCHAR(100)
SELECT @STR = '1,2,3,4,5'
DECLARE @strXml XML
SELECT @strXml = CAST(' ' AS XML)
SELECT Parent_Res, Sequence, ResRcId, ResRc_Name
FROM Resource
Where Parent_Res In (
SELECT
x.i.value('.', 'VARCHAR(10)') AS Number
FROM @strXml.nodes('//i') x(i)
) and [Type] = 1
Order By Parent_Res, Sequence, ResRcId
I have explained this approach with examples here: http://www.sqlserverandxml.com/2008/08/xquery-lab-19-how-to-parse-delimited.html
.
September 25, 2008 at 6:05 pm
Thanks Jacob,
Will have a look at the XML approach.
David
September 26, 2008 at 8:04 am
Also search for Array+sql server in google for some more methods
Failing to plan is Planning to fail
September 26, 2008 at 8:13 am
Yep,
There are a number of articles to explain how to "simulate" arrays and other similar data structures that we wish we could use online. I don't have any favorites on top of my head but I think I've even seen that covered in msdn actually...
I think T-SQL is great and all but sometimes we just miss the ability to just do more with it, especially for those who are used to programming. But I guess that makes for innovative solutions 🙂
Greg
September 26, 2008 at 9:48 am
another technique would be to use a Table-Valued function to pass MultiValue param's values to a store procedure. That is widely used in SSRS reports.
something like
SELECT top 1 1
FROM [Catalog]
Where 1 In (SELECT splitStr FROM dbo.Split('1,700', ','))
--returns : 1
func dbo.Split splits your string into integers by a supplied delimiter ',' and returns the list:
1
700
to the SP.
September 27, 2008 at 7:04 pm
It's super simple to pass a single dimensional array to SQL Server as a comma delimited string and then to split it into a temp table or table variable using SQL Server. And, it's nasty fast... please read the following article...
http://www.sqlservercentral.com/articles/T-SQL/63003/
For more information on how the basis of the code works, please see the following article which also has a split function...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2008 at 7:05 pm
By the way... the methods shown in the articles are a bit faster than the XML methods and can also be used in SQL Server 2000. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply