I have created a dynamic query that works, but in trying to turn it into a dynamic stored procedure I keep getting an incorrect syntax message, but I can't figure out where that error is .
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TClassTest](
[eventid] [float] NULL,
[tclass] [float] NULL,
[tclassrank] [int] NULL,
[selectionid] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526, 0,
NULL, 28663441)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
1.15952227300871, 1, 22117943)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
1.01611216313152, 2, 30152380)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
1.01611216313152, 3, 35467164)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
0.998811711630396, 4, 32049358)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
0.918873779034185, 5, 28618281)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
0.871205005784805, 6, 35886755)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
0.868808542018581, 7, 28610519)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526, 0.77,
8, 28583488)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
0.705097992133484, 9, 35482055)
GO
INSERT [dbo].[TClassTest] ([eventid], [tclass], [tclassrank], [selectionid]) VALUES (175091526,
0.499807334963594, 10, 7208977)
This is the query that works
declare @EventId int
declare @sqlstring nvarchar(max)
declare @eventidstring nvarchar(10)
declare @condition nvarchar(20)
declare @conditionrank nvarchar(20)
set @EventId=175091526
set @eventidstring= cast(@EventId as nvarchar(100))
set @condition='tclass'
set @conditionrank='tclassrank'
set @sqlstring=N'
with cte1 as
(
select
event_id
,' + @condition +'
,'+ @conditionrank +',
case when '+ @condition +' =0 then null else
rank() over(order by case when '+ @condition +' is null then 0 else 1 end,'+ @condition +' desc)end
as rnk
from TClassTest
where
event_id= '+ @eventidstring +'
)
update cte1
set '+ @conditionrank +'= rnk '
print @sqlstring
exec sp_executesql @sqlstring
And this is the failed attempt at a stored proc.
ALTER PROCEDURE [dbo].[TrainerRankingAll]
@EventId int,
@condition nvarchar(20),
@conditionrank nvarchar(20)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sqlstring nvarchar(max)
declare @eventidstring nvarchar (20)
set @eventidstring= cast(@EventId as nvarchar(20))
set @sqlstring=N'
with cte1 as
(
select
event_id
,' + @condition +'
,'+ @conditionrank +',
case when'+ @condition + ' =0 then null else
rank() over(order by case when '+ @condition +' is null then 0 else 1 end,'+ @condition +' desc)end
as rnk
from TClassTest
where
event_id= '+ @eventidstring +'
)
update cte1
set ' + @conditionrank + '= rnk '
print @sqlstring
exec sp_executesql @sqlstring
It produces the error message 'Incorrect syntax near 'tclassrank'.
I should also add that the values to be passed into the stored proc are the same as are passed to the query.
Can someone please spot my error or tell me what I am doing wrong. Many thanks.