Help with Stored Procedure - SQL Server Forums

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 Stored Procedure

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-09-23 : 14:03:13

Hello,
I have a table TABLE1.
It has the following fields:(NumID int,Name varchar(100), Fld1 text, Fld2 text, Fld3 text,Date smalldatetime,Rank int)

Now to generate a report the user can select one or more of the text fields and he will also select multiple rows from the above table.

Now I am trying to write a stored procedure so that I will only select the text fields the user is trying to view and not all teh text fields. And I will get comma seperated list of all the NumIDs.

Create Procedure getReport
( @Fld1On bit,
@Fld2On bit,
@Fld3On bit,
@numIDList varchar(1000),
@orderBy varchar(30) = 'Rank'
)
AS
set nocount on

-- This will hold the NumID in the loop
Declare @noID int

-- Declare a Table Variable
DECLARE @TestTable Table(
NumID int,
Name varchar(100),
Date smalldatetime,
Rank int,
Fld1 text,
Fld2 text,
Fld3 text)

-- Insert Values into the Table Var
if @numIDList <> ''
Begin
-- Now I will loop through the comma seperated list of numIDList
-- and in each iteration it will place the numID in teh noID field
-- I am not writing that logic just to make it simple
-- Loop Start
INSERT INTO @TestTable(NumID,Name,Date,Rank,Fld1,Fld2,Fld3)
select NumID,Name,Date,Rank,Fld1,Fld2,Fld3 from TABLE1 where NumID = @noID
-- Loop End
End

-- Then select teh values based on the Order By Field
if @orderBy = 'Rank'
select * from @TestTable order by Rank
else if @orderBy = 'Name'
select * from @TestTable order by Name
else if @orderBy = 'Date'
select * from @TestTable order by Date
else
select * from @TestTable order by Rank
set nocount off
GO

what is the best way to change this stored procedure so that I will select the text fields which user selected and not all the text fields.

Thanks
maximus

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-09-24 : 14:51:16

Any ideas? Is my question clear enough?

Thanks
maximus

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 05:39:16

This will give you three text columns, but only the ones requested will have data in them.


SELECT  NumID,
        Name,
        Date,
        Rank,
        CASE WHEN @Fld1On = 1 THEN Fld1 ELSE NULL END AS [Fld1],   -- Could use '' instead of NULL
        CASE WHEN @Fld2On = 1 THEN Fld2 ELSE NULL END AS [Fld2],
        CASE WHEN @Fld3On = 1 THEN Fld3 ELSE NULL END AS [Fld3]
FROM    @TestTable

maybe you want the "unwanted" columns supressed altogether? If so you will have to use dynamic SQL within the SProc, but that will only be operating on the temporary table, so it won't be too bad (and won't have any permissions issues AFIK)

I would use a SPLITTER function to JOIN @numIDList to TABLE1 (rather than a loop). [Plenty of examples on here if you search for SPLITTER or maybe CSV]

If I've got the wrong end of the stick ask a suplimental question!

Kristen

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-10-25 : 18:06:57

Hi Kristen,

Sorry for not getting back to you on this before.

quote:
Originally posted by Kristen

maybe you want the "unwanted" columns supressed altogether?

Kristen

Yes I wanted to supress the "unwanted" columns.

If I use dynamic SQL, if the query I am constructing is greater than 8000 characters my SP fails.

So can I do this..

Can I do ALTER Table after the table variable declaration based on whether or not that field is present or not(see below)?

DECLARE @TestTable Table(
NumID int,
Name varchar(100),
Date smalldatetime,
Rank int
)
if @Fld1On = 1
ALTER TABLE @TestTable ADD Fld1 text

And how will I insert only those fields into this table variable?

INSERT INTO @TestTable(NumID,Name,Date,Rank,Fld1,Fld2,Fld3)
select NumID,Name,Date,Rank,Fld1,Fld2,Fld3 from TABLE1 where NumID = @noID

Any ideas?

Thanks,
maximus

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 00:46:17

On answer would be:


IF @Fld1On = 1 
BEGIN
    SELECT  NumID,
            Name,
            Date,
            Rank,
            Fld1
    FROM    @TestTable
END
ELSE
IF @Fld1On = 2 
BEGIN
...

If you have dynamic SQL in @variables then to get past the 8000 character limit you need to do

EXEC (@var1 + @var2 + ... )

where @var1, @var2, ... are EACH varchar(8000)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources