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

allendehl
Starting Member

1 Post

Posted - 2008-01-22 : 15:02:29

Hi friends...this is my first post to the forum, so Hello!!!

Well..I created a SP in which I build the final statement dynamically.
This is it:

*******************
CREATE PROCEDURE [spGetLeadsByTemplate]
@LeadType int,
@TemplateId int

AS

Declare @Filter varchar(2000)
Declare @Field varchar(100)
Declare @Oper varchar(100)
Declare @Crit varchar(1000)
Declare @SQL varchar(2000)
Declare @TableName varchar(20)
Declare @FieldHeader varchar(5)

set @Filter = ''
set @Field = ''
set @Oper = ''
set @Crit = ''

set nocount on
--RETREIVING TABLE NAME BY LEAD TYPE
select @TableName = LDT_TableName, @FieldHeader = LDT_FieldHeader from smeLeadTypes where LDT_LeadTypeId = @LeadType

--PREPARING THE WHERE CLAUSE(FILTERS)
select @Field = ETD_Field, @Oper = ETD_Operator, @Crit = ETD_Criteria from smeExportTemplateDetails where ETD_TemplateId = @TemplateId and ETD_Sequence = 1
if @Field <> ''
begin
set @Filter = @Filter + ' where ' + ltrim(rtrim(@Field)) + ' ' + ltrim(rtrim(@Oper)) + ' ' + ltrim(rtrim(@Crit))
end

select @Field = ETD_Field, @Oper = ETD_Operator, @Crit = ETD_Criteria from smeExportTemplateDetails where ETD_TemplateId = @TemplateId and ETD_Sequence = 2
if @Field <> ''
begin
set @Filter = @Filter + ' and ' + ltrim(rtrim(@Field)) + ' ' + ltrim(rtrim(@Oper)) + ' ' + ltrim(rtrim(@Crit))
end

select @Field = ETD_Field, @Oper = ETD_Operator, @Crit = ETD_Criteria from smeExportTemplateDetails where ETD_TemplateId = @TemplateId and ETD_Sequence = 3
if @Field <> ''
begin
set @Filter = @Filter + ' and ' + ltrim(rtrim(@Field)) + ' ' + ltrim(rtrim(@Oper)) + ' ' + ltrim(rtrim(@Crit))
end

select @Field = ETD_Field, @Oper = ETD_Operator, @Crit = ETD_Criteria from smeExportTemplateDetails where ETD_TemplateId = @TemplateId and ETD_Sequence = 4
if @Field <> ''
begin
set @Filter = @Filter + ' and ' + ltrim(rtrim(@Field)) + ' ' + ltrim(rtrim(@Oper)) + ' ' + ltrim(rtrim(@Crit))
end

select @Field = ETD_Field, @Oper = ETD_Operator, @Crit = ETD_Criteria from smeExportTemplateDetails where ETD_TemplateId = @TemplateId and ETD_Sequence = 5
if @Field <> ''
begin
set @Filter = @Filter + ' and ' + ltrim(rtrim(@Field)) + ' ' + ltrim(rtrim(@Oper)) + ' ' + ltrim(rtrim(@Crit))
end

set @Filter = @Filter + ' and MRT_Sold = 0'

--BUILD THE SQL SENTENCE
set @SQL ='SELECT MRT_CREATED, COUNT(*) AS AMOUNT
FROM ' + @TableName + ' inner join smeCustomers on (CUS_CustomerId = ' + @FieldHeader + '_CustomerId)' +
@Filter + ' group by MRT_CREATED order by MRT_Created DESC'

set nocount off

--EXECUTE THE SQL SENENCE
exec(@SQL)
GO
**************************************

To try it in the Query analyzer I use:

exec spGetLeadsByTemplate 173,2

and I dont get any results...only this:

"The command(s) completed successfully."

What should I do to retrieve the results?

thx

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-22 : 17:18:56

Put a PRINT @SQL at 3-4 places and see how its building up. You might also want to check for NULLs in any of the parameters.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources