Dynamic Sql Query to Dynamic Stored Proc

Transact-SQL
1

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.

2

It would be easier if you provided the query/proc and error message

3

Sorry, I posted in error, trying to get the formatting for the code correct.I hope you have all that you need now. Thank you. (It's one of those days for me)

4

The proc is missing an
END
statement, since you used a BEGIN.

Instead you could just get rid of the BEGIN since BEGIN/END is not required for a proc.

5

there are 3 things wrong with this. The first is as Scott mentioned, your proc needs an End. The second is that the proc doesn't match the DDL and sample data you created. The third is this line:

case when'+ @condition + ' =0 then null else

You need to add a space after the word when

6

Ah very sorry for the careless typos. The begin and end I don't think are the cause, creating or altering the stored proc works, the omission here was a typo. The error comes in running the actual stored proc, resulting in an Incorrect syntax near 'tclassrank' message. Yes, the table name given here was wrong - sorry - that has now been amended. I have added a space after the word 'when' as advised but still the same error.

7

can you post your code?? This worked for me

ALTER PROCEDURE [dbo].[TrainerRankingAll]

@EventId int,
@condition nvarchar(20),
     @conditionrank nvarchar(20)
AS
BEGIN
-- 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 
eventid
,' + @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  
eventid= '+ @eventidstring +' 


) 
update cte1
set ' + @conditionrank + '= rnk '
print  @sqlstring 
exec sp_executesql @sqlstring 

end
8

What an fool I've been. You're right, it does work and has worked since creation I guess. The error was mine in that I was surrounding the params tclass and tclassrank in single quotes, and of course, these single quotes were added to by MSSMS and thus the error. Thank you so much for yours and Scotts help, it helped me to see what I was refusing or unable to see. Thank you again.