sp problem - 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)

sp problem

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-06-05 : 12:54:22

I am having probelms with the last two sections of this sp...

CREATE PROCEDURE [dbo].[get_ftrTable_sales]
@spNO varchar(1000)
AS

DECLARE @skulist varchar(1000)
DECLARE @skulist2 varchar(1000)
SELECT @skulist = COALESCE(@skulist + '],[', '') +
CAST(sku AS varchar(20))
FROM data WHERE spec_no = + @spno

SELECT @skulist2 = '[' + @skulist + ']'

declare @quote char(1)
select @quote = ''''

DECLARE @ctgry varchar(1000)
select @ctgry = 'select ctgry from Logic where ss = ' + @quote + @spno + @quote
exec (@ctgry)

declare @itis varchar(1000)
select @itis = 'select theID, ftrdesc, '
select @itis = @itis + @skulist2 +' from dbo.features where ' + @ctgry + '=1 '
print (@itis)
GO

print (@itis) return: select theID, ftrdesc, [H34-1NCC],[H34-3NCC],[H34-4NCC],[H34-3NSS],[H34-4NSS],[H34-3NWW],[H34-4NWW] from dbo.features where select ctgry from Logic where ss = 'ss-h34-01'=1

instead of :select theID, ftrdesc, [H34-1NCC],[H34-3NCC],[H34-4NCC],[H34-3NSS],[H34-4NSS],[H34-3NWW],[H34-4NWW] from dbo.features where kitchen=1

I want the result of @ctgry to be used in @itis, not the SQL string itself. Waht am I doing wrong?

Thanks,
Lane

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-05 : 12:59:45

When you run:

quote:

DECLARE @ctgry varchar(1000)
select @ctgry = 'select ctgry from Logic where ss = ' + @quote + @spno + @quote
exec (@ctgry)

where is it storing ctgry? nowhere.

I suspect this subquery can be re-written to avoid using dynamic SQL, and will also clear up the problem.

setBasedIsTheTruepath
<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-05 : 13:00:07

you need to capture the results of exec (@ctgry) somehow and not use the @ctgry variable in you build-up of @itis.

you can either insert tablename exec (@ctgry) or exec sp_executesql

<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-05 : 13:00:44

sniped!

setBasedIsTheTruepath
<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-05 : 13:03:00

additionally, if the columns in your select list must be dynamically generated, that says to me that not all columns are attributes of the key and thus your schema isn't normalized

...and you don't need dynamicl sql to get the category anyway, since select @ctgry = ctgry from Logic where ss = @spNo will do it . . .

<O>

Edited by - Page47 on 06/05/2002 13:07:55

lane0618
Posting Yak Master

134 Posts

Posted - 2002-06-05 : 13:20:15

got rid of the dynamic SQL, works now!

Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources