SQL Server 2008 R2: Cursor with condition

Question

I have the table which contains the table's id and name.

Now I want to retrieve the table names by passing table ID to the stored procedure.

Example:

create procedure spGetAllTables
@TableIDs varchar(max)

AS

DECLARE @Tables varchar(max)

    DECLARE Cur1 CURSOR FAST_FORWARD FOR
    SELECT TableName FROM TablesContainer
    WHERE TableID IN (@TableIDs)

    OPEN Cur1
    FETCH NEXT FROM Cur1 INTO @Tables
    WHILE(@@FETCH_STATUS=0)
    BEGIN
        PRINT(@Tables)
        FETCH NEXT FROM Cur1 INTO @Tables

    END

    CLOSE Cur1;

    DEALLOCATE Cur1;

GO

Explanation: The table TablesContainer contains all table's ID's and table's names. I just want to print all those table names which I have passed their table ID's to the stored procedure.

The procedure working fine if I pass single value to the variable @TableIDs. But If I pass multiple values like 1,2,3 to @TableIDs then its not getting entered into the cursor.

You have to use a split function or dynamic sql to handle comma-separated IDs. — Tab Alleman, Oct 27 '15 at 14:03
That's because variables are scalar. They do not magically make your query run as dynamic sql. The bigger question is why do you need a cursor here? — Sean Lange, Oct 27 '15 at 14:04
@TabAlleman, Okay. I am trying to solve this by using Dynamic sql. — MAK, Oct 27 '15 at 14:05
This seems like homework or just for learning because the real world implications of this code are totally elusive. You need to stop thinking row by row. There is no need for a cursor here. You just to split the string. Here is an article with a number of ways to split strings. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Oct 27 '15 at 14:08
The better way is to use a separate table instead of storing a comma-delimited list of values. A separate table can have indexes, be used in relations and queries while a CSV field can't. In any case, there are a lot of ways to split a string and the link posted by SeanLange covers them all. — Panagiotis Kanavos, Oct 27 '15 at 14:21
What is the schema of `TableContainer`? If it's a many-to-many table, splitting isn't required at all. You could simply write an `IN (...)` query or pass a table-valued parameter with the list of IDs, then join the TVP to `TableContainer` — Panagiotis Kanavos, Oct 27 '15 at 14:26

score 2 · Accepted Answer · edited May 23 '17 at 12:23

2

i think you don't need cursor, there is multiple way to separate string you can pass table value parameter and split the string but as per the Q requirement Reference

DECLARE @temp TABLE (Id INT)
INSERT INTO @temp VALUES (1),(2),(6)

DECLARE @TableIDs  varchar(max),
        @xml xml

SELECT @TableIDs = '1,2,3,4,5'
SELECT @xml = CONVERT(xml,' <root> <x>' + REPLACE(@TableIDs,',','</x> <x>') + '</x>   </root> ')


SELECT id FROM @temp
WHERE id IN (
SELECT  T.c.value('.','INT')
        FROM @xml.nodes('/root/x') T(c)     
)

edited May 23 '17 at 12:23

Community

1
1

answered Oct 27 '15 at 14:08

wiretext

3,302
14
19

Still getting nothing in output. – MAK Oct 27 '15 at 14:20
@MAK what is the error it is working for me see my updated answer – wiretext Oct 27 '15 at 14:27
The ID is in GUID format. Is that is the problem for this? – MAK Oct 27 '15 at 14:30
@then convert into `INT` to `UNIQUEIDENTIFIER` – wiretext Oct 27 '15 at 14:31
Yup! Got it. Thank you so much. – MAK Oct 27 '15 at 14:35
There's no need to split the string at all, the OP can pass a table valued parameter with the IDs – Panagiotis Kanavos Oct 27 '15 at 14:38
@PanagiotisKanavos yes agree sir ! – wiretext Oct 27 '15 at 14:39

Panagiotis Kanavos · Answer 2 · 2015-10-27T14:37:20.333

You don't neet to pass the IDs as a CSV list. SQL Server has table-valued parameters since the SQL Server 2008 version at least. A TVP is a table parameter to which you insert data with plain INSERT statements in SQL, or as an collection (a DataTable or DataReader) in ADO.NET.

You can JOIN with the TVP as you would with any table or table variable.

First, you need to define the TVP type in the database:

CREATE TYPE IdTableType AS TABLE ( ID uniqueidentifier);

Then you can write a stored procedure to use it:

create procedure spGetAllTables(@TableIDs IdTableType READONLY)
AS
    SELECT TableName 
    FROM TablesContainer
    inner join @TableIDs on @TableIDs.ID=TablesContainer.TableID

To call the stored procedure from T-SQL you can create a table parameter and fill it:

declare @tableIds IdTableType;

insert into @tableIds 
VALUES
('....'),
('....');

exec @spGetAllTables(@tableIds);

SQL Server 2008 R2: Cursor with condition

2 Answers2