August 22, 2007 at 9:13 pm
I have a sp shown as below, I will pass parameter @criteria = 'MajorDeliveryChannel'
Both
EXEC (@sql1 + @criteria + @sql2 + @sql3 + @criteria + @sql4)
and
set @sql = @sql1 + @criteria + @sql2 + @sql3 + @criteria + @sql4
EXECUTE sp_executesql @sql
are not working, but as you can see from the sp, I printed out the generated sql query, it works.
What's the problem? something wrong in the dynamic query?
Any suggestion would be appreciated. Thanks.
CREATE PROCEDURE dbo.spListDataStatisticByCriteria(@criteria Varchar(50))
AS
SET NOCOUNT ON
DECLARE @Err INT, @RCnt INT
DECLARE @rc int
CREATE TABLE #t
(
numID INTEGER IDENTITY(1,1),
Criteria varchar(50),
NextLevel Varchar(50),
Total Int
)
declare @sql1 varchar(255)
declare @sql2 varchar(255)
declare @sql3 varchar(255)
declare @sql4 varchar(255)
set @sql1 = 'INSERT #t (Criteria, NextLevel, Count) ' +
'SELECT distinct ['
set @sql2 = ']' + ', NextLevel = case @criteria '
set @sql3 = ' when ' + char(39) + 'MajorDeliveryChannel' + char(39) + ' then ' + char(39) + 'SubChannel' + char(39) + ' when ' + char(39) + 'SubChannel' + char(39) + ' then ' + char(39) + 'Area' + char(39)
+ ' end, Count(1) FROM dbo.data'
+ ' group by ['
set @sql4 = ']'
--print @sql1 + @criteria + @sql2 + @sql3 + @criteria + @sql4
--SELECT distinct [MajorDeliveryChannel], NextLevel = case @criteria when 'MajorDeliveryChannel' then 'SubChannel' when 'SubChannel' then 'Area' end, Count(1) FROM dbo.data group by [MajorDeliveryChannel]
EXEC (@sql1 + @criteria + @sql2 + @sql3 + @criteria + @sql4)
declare @sql varchar(1000)
set @sql = @sql1 + @criteria + @sql2 + @sql3 + @criteria + @sql4
EXECUTE sp_executesql @sql
select * from #t
DROP TABLE #t
GO
August 22, 2007 at 9:51 pm
When setting @sql2, append @criteria... set @sql2 = ']' + ', NextLevel = case ' + @criteria.
You also have Count in your insert statement. I think you want Total.
Regards,
Rubes
August 23, 2007 at 1:37 am
Hi,
it would be a lot easier to find problems if you post the structure of all objects (here: table "Data") and a few rows of sample data.
So far, I've only been able to find the same things rubes already posted.
You need to take out the @criteria from within quoted text, so that it can be replaced with a value. Dynamic SQL has no access to variables defined outside of the dynamic SQL, so you need to pass a value, not variable name.
August 23, 2007 at 3:20 am
Can you post details of the error?
I suspect that the problem is due to the scope of the temp table #t. Effectively, the sp_executesql is a nested stored procedure and #t does not exist within its scope.
The way round this is:
insert #t
exec sp_executesql @sql
and to take out the insert #t line from your @sql1 variable.
Jez
August 23, 2007 at 7:20 am
Hi Jez,
I followed your suggestion, it gave me error:
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 54
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
To others: the generated sql query(the commented select query) has no problem at all, it returns me exact what I want. I am sure I can work around this by writting some cumbersome code, but this got to be working somehow.
Thanks.
August 23, 2007 at 7:48 am
If I run the exec like:
insert #t
exec (@sql)
It gave me:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@criteria'.
Stored Procedure: SSER.dbo.spListDataStatisticByCriteria
Return Code = 0
The @sql is as below, it works fine if I just run it alone:
SELECT distinct [MajorDeliveryChannel], NextLevel = case @criteria when 'MajorDeliveryChannel' then 'SubChannel' when 'SubChannel' then 'Area' end, Count(1) FROM dbo.data group by [MajorDeliveryChannel]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply