Create a dataset through loop

  • I have a table in sql. 2 rows out of many are EventID and FormID.

    When I execute stored procedure with EventID and FormID I need to set them for all possible EventIDs and FormIDs in that table.

    How can I loop them?

  • Can you show us some sample data along with the expected results... There's not enough information to give an accurate answer.

    Here's a guess but I doubt it's gonna do what you want :

    Select DISTINCT EventID, FormID from dbo.YourTable

  • Well,

    I need to put values of EventID into a comma-separated string. Then split() and declare as new variable.

    Then it will be passed to a procedure.

  • What????!! Can you tell us which problem you are trying to solve with this technic??

  • It's a big one in T-SQL section =)

  • Well if don't want real help, here's the solution you requested :

    IF Object_id('ListTableColumns') > 0

    DROP FUNCTION ListTableColumns

    GO

    CREATE FUNCTION dbo.ListTableColumns (@TableID as int)

    RETURNS varchar(8000)

    AS

    BEGIN

    Declare @Items as varchar(8000)

    SET @Items = ''

    SELECT

    @Items = @Items + C.Name + ', '

    FROMdbo.SysColumns C

    WHEREC.id = @TableID

    AND OBJECTPROPERTY(@TableID, 'IsTable') = 1

    ORDER BYC.Name

    SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))

    RETURN @Items

    END

    GO

    Select dbo.ListTableColumns(Object_id('SysObjects'))

    --base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype

    DROP FUNCTION ListTableColumns

  • BTW, from what I read it seems you are using the worst approach possible... but I don't know for sure.

  • I needed to list something like:

    | EventID

    |  1        |  <-

    |  2        |  <-

    |  3        |  <-

    |  4        |  <-

    |  5        |  <-

    I know approach is terrible - someone wrote the code already for an entire system - and I have to add stuff to it the way they want it... I hate if myself.

  • Do you need more help or you're ok with the code I sent??

  • It would be nice if you can tell me where/how to integrate it into that script from T-SQL section.

  • Use the function whereever you need it like so :

    Select id, dbo.ListTableColumns(id) as LinearColumns from dbo.SysObjects where XType = 'U'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply