Help with this Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with this Stored Procedure

Author

Topic

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-13 : 09:56:55

- Hi, I want to add to the select of this stored procedure, another select who puts only the names of the fields that the second select (the one in the stored procedure) shows. I suppose that I have to use an UNION, but I also have to format all those names in order to appear as a string (not the value). Can anyone help me please?

- This is the code of the stored procedure:

CREATE PROCEDURE dbo.OR_CampaignContactsPerDay_Dynamic
(
@DateStart AS VARCHAR(10),
@DateEnd AS VARCHAR(10),
@cp_name AS VARCHAR(50)
)

AS

DECLARE @ct_table AS VARCHAR(30),
@querytext AS VARCHAR(8000),
@campaign_id AS VARCHAR(10),
@columnname AS VARCHAR(50),
@strsql AS VARCHAR(7700)

SELECT @ct_table=RTRIM(contacttable), @campaign_id=c.code FROM cp_general_cfg cp_cfg, campaign c
WHERE
c.code=cp_cfg.campaign
AND c.shortname=@cp_name

SET NOCOUNT ON

DECLARE c1 CURSOR FOR
SELECT column_name FROM table_schema
WHERE table_name = @ct_table
ORDER BY code
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql='ISNULL(CAST(ct.'+RTRIM(LTRIM(@columnname))+' AS VARCHAR), '''')'
END
ELSE
BEGIN
SET @strsql = @strsql + ' + ' + '''|''' + ' + ' + 'ISNULL(CAST(ct.'+RTRIM(LTRIM(@columnname))+' AS VARCHAR), '''')'
END

FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1

SET @querytext = 'SELECT ' + @strsql + ' AS contact_table,
(CASE c.status WHEN 0 THEN ''STARTED'' WHEN 1 THEN ''EXECUTING'' WHEN 3 THEN ''DONE'' WHEN 17 THEN ''ABORTED'' ELSE ''OTHER'' END) AS status,
u.usr_name, dt.start_time, dt.duration, out.name AS outcome
FROM contact c, '+ @ct_table+' ct, data_transaction dt, data_context dc,call_type out, e_user u
WHERE c.code=ct.easycode AND dc.contact=c.code AND dt.data_context=dc.code AND dt.call_type*=out.code AND dt.e_user=u.code
AND dt.start_time BETWEEN '''+@DateStart+''' AND '''+@DateEnd+''' AND c.campaign='+@campaign_id +'
ORDER BY easycode, dt.start_time'

EXEC (@querytext)

SET NOCOUNT OFF
GO

- And I want that the @querytext has another select first, which only shows the names of the fields that the actual select shows.-

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-13 : 10:10:30

The reason because I need to do that, is because this sp is gonna generate a lot of | separated files, and that's why I need that the first record be the one with the column names.

So I need the fields within @strsql (that are a lot), status, usrname, start_time, duration, outcome (as a string) first of all, and then the actual result of the @querytext query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 10:13:13

[code]EXEC (@querytext)[/code]
After, add this text

[code]SET @querytext = REPLACE(@querytext, ' where ', ' where 1 = 0 AND ')
EXEC (@querytext)[/code]

Then you get all the fields for previous select.

Peter Larsson
Helsingborg, Sweden

transcom_bcn
Starting Member

16 Posts

Posted - 2006-06-13 : 11:08:28

Thank you!

quote:
Originally posted by Peso

EXEC (@querytext)

After, add this text

SET @querytext = REPLACE(@querytext, ' where ', ' where 1 = 0 AND ')
EXEC (@querytext)

Then you get all the fields for previous select.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources