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,
@TimeLine,
@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: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,
@TimeLine,
@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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy