June 27, 2005 at 9:27 am
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?
June 27, 2005 at 9:29 am
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
June 27, 2005 at 9:38 am
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.
June 27, 2005 at 9:47 am
What????!! Can you tell us which problem you are trying to solve with this technic??
June 27, 2005 at 10:31 am
It's a big one in T-SQL section =)
June 27, 2005 at 10:33 am
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
June 27, 2005 at 10:34 am
BTW, from what I read it seems you are using the worst approach possible... but I don't know for sure.
June 27, 2005 at 10:43 am
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.
June 27, 2005 at 11:26 am
Do you need more help or you're ok with the code I sent??
June 27, 2005 at 11:38 am
It would be nice if you can tell me where/how to integrate it into that script from T-SQL section.
June 27, 2005 at 11:39 am
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