December 5, 2011 at 9:14 am
Using dynamic sql is really like curse and blessing, in one of my practice I do need to create a stored procedure with parameters, and those parameters will be used to create a dynamic sql for execution inside the same stored procedure, the final version is like this and it is not accepted by sql:
ALTER Proc [dbo].[spGetStatisticByCategoryBLTimeLineConsultant](@Type varchar(50),@BL varchar(255), @timeline int, @Consultant int)
AS
declare @param datetime
SET @param =
CASE
WHEN @timeline = 0 THEN DATEADD(yy,-100,getdate())
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
declare @sql varchar(1000)
set @sql = 'SELECT count(1) as Num, ' + @type + ' FROM [Pergamum].[dbo].[fnGetAllClassificationRankingByCode]() ' +
'inner join OrgUnits org on org.OrgUnitID = i.OrgUnitID where ' +
'i.type <>' + '''' + 'component' + '''' + ' and org.Business_Line = case when @BL <> ' + '''' + '''' + ' then @BL' + ' else org.Business_Line end and ' +
'i.LastUpdatedOn >= ' + '''' + CONVERT(varchar(20), @param) + '''' + ' and ' +
'i.uid = case when @Consultant > 0 then ' + CONVERT(varchar(3), @Consultant) + ' else i.uid end group by ' + CONVERT(varchar(3), @type)
exec (@sql)
The error message says:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@BL".
Although it just points to @BL, I am sure if I remove @BL, then it will points to @Consultant, both are passed from stored procedure, it seems exec (@sql) just take whatever inside the @sql.
How to get over this? or is it possible to do this?
Thanks.
December 5, 2011 at 9:30 am
The error message says:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@BL".
Although it just points to @BL, I am sure if I remove @BL, then it will points to @Consultant, both are passed from stored procedure, it seems exec (@sql) just take whatever inside the @sql.
You should declare & initialise all these parameters in Dynamic Query itself. The variable value is not reaching to Dynamic SQL query.
Just to explain, @Consultant in red is different than in black.
'@Consultant > 0 then ' + CONVERT(varchar(3), @Consultant) + ''
December 5, 2011 at 9:42 am
You can use sp_executesql:
ALTER Proc [dbo].[spGetStatisticByCategoryBLTimeLineConsultant](@Type varchar(50),@BL varchar(255), @timeline int, @Consultant int)
AS
DECLARE @param datetime
SET @param =
CASE
WHEN @timeline = 0 THEN DATEADD(yy,-100,getdate())
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
DECLARE @component SomeDataType
SET @component = SomeValue
DECLARE @sql nvarchar(max)
SET @sql = '
SELECT count(1) as Num, ' + @type + '
FROM [Pergamum].[dbo].[fnGetAllClassificationRankingByCode]()
INNER JOIN OrgUnits org
ON org.OrgUnitID = i.OrgUnitID
WHERE i.type <> @component
AND org.Business_Line =
CASE
WHEN @BL <> '''' THEN @BL
ELSE org.Business_Line
END
AND i.LastUpdatedOn >= @param
AND i.uid =
CASE
WHEN @Consultant > 0 THEN @Consultant
ELSE i.uid
END
GROUP BY ' + CONVERT(varchar(3), @type)
EXEC sp_executesql
@sql,
N'@component whateverTypeComponentIs, @BL varchar(255), @param datetime, @Consultant int',
@component,
@BL,
@param,
@Consultant
-- Gianluca Sartori
December 5, 2011 at 10:01 am
Gianluca Sartori (12/5/2011)
You can use sp_executesql:
ALTER Proc [dbo].[spGetStatisticByCategoryBLTimeLineConsultant](@Type varchar(50),@BL varchar(255), @timeline int, @Consultant int)
AS
DECLARE @param datetime
SET @param =
CASE
WHEN @timeline = 0 THEN DATEADD(yy,-100,getdate())
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
DECLARE @component SomeDataType
SET @component = SomeValue
DECLARE @sql nvarchar(max)
SET @sql = '
SELECT count(1) as Num, ' + @type + '
FROM [Pergamum].[dbo].[fnGetAllClassificationRankingByCode]()
INNER JOIN OrgUnits org
ON org.OrgUnitID = i.OrgUnitID
WHERE i.type <> @component
AND org.Business_Line =
CASE
WHEN @BL <> '''' THEN @BL
ELSE org.Business_Line
END
AND i.LastUpdatedOn >= @param
AND i.uid =
CASE
WHEN @Consultant > 0 THEN @Consultant
ELSE i.uid
END
GROUP BY ' + CONVERT(varchar(3), @type)
EXEC sp_executesql
@sql,
N'@component whateverTypeComponentIs, @BL varchar(255), @param datetime, @Consultant int',
@component,
@BL,
@param,
@Consultant
Thanks. I slightly modified to:
.......
EXEC sp_executesql
@sql,
@Type,
@BL,
@Consultant
It throws out this error message:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
December 5, 2011 at 10:06 am
I suspect that you didn't change @sql to datatype nvarchar.
-- Gianluca Sartori
December 5, 2011 at 10:50 am
Gianluca Sartori (12/5/2011)
I suspect that you didn't change @sql to datatype nvarchar.
Actually I did.
ALTER Proc [dbo].[spGetStatisticByCategoryBLTimeLineConsultant](@Type varchar(50),@BL varchar(255), @timeline int, @Consultant int)
AS
declare @param datetime
SET @param =
CASE
WHEN @timeline = 0 THEN DATEADD(yy,-100,getdate())
WHEN @timeline = 1 THEN DATEADD(yy,-1,getdate())
WHEN @timeline = 2 THEN DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
WHEN @timeline = 3 THEN DATEADD(mm,10,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
END
print @param
DECLARE @sql nvarchar(max)
set @sql = 'SELECT count(1) as Num, ' + @type + ' FROM [Pergamum].[dbo].[fnGetAllClassificationRankingByCode]() ' +
'inner join OrgUnits org on org.OrgUnitID = i.OrgUnitID where ' +
'i.type <>' + '''' + 'component' + '''' + ' and org.Business_Line = case when @BL <> ' + '''' + '''' + ' then @BL' + ' else org.Business_Line end and ' +
'i.LastUpdatedOn >= ' + '''' + CONVERT(varchar(20), @param) + '''' + ' and ' +
'i.uid = case when @Consultant > 0 then ' + CONVERT(varchar(3), @Consultant) + ' else i.uid end group by ' + CONVERT(varchar(3), @type)
EXEC sp_executesql
@sql,
@Type,
@BL,
@Consultant
Still throws:
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
December 5, 2011 at 11:40 am
From the error message it seems that sp_executesql must take parameters with the type of 'ntext/nchar/nvarchar' only, so I modified the sp again as following:
......
declare @NewTimeLine nvarchar(3)
declare @NewConsultant nvarchar(3)
set @NewTimeLine = CONVERT(nvarchar(3), @timeline)
set @NewConsultant = CONVERT(nvarchar(3), @Consultant)
--print @sql
EXEC sp_executesql
@sql,
@Type,
@BL,
@NewTimeLine,
@NewConsultant
It is still not working! And gives the same error! but this time all the five parameters are all of type of nvarchar
December 5, 2011 at 11:50 am
I figured it out:
All I need to do is to define parameters for the sp_executesql
Here is the working code:
.........
EXEC sp_executesql
@sql,
N'@BL varchar(50),@Consultant int',
@BL,
@Consultant
Thanks for enlightening me
December 5, 2011 at 2:29 pm
Sorry for the late reply. Glad you sorted it out.
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply