Can any one tell?? whats wrong in the below qry?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can any one tell?? whats wrong in the below qry??

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-10 : 04:58:01

DECLARE @SQL nvarchar(4000)
declare @params nvarchar(1000)
declare @params1 nvarchar(1000)
DECLARE @RATE varchar (10)
Declare @Age1 as int
DECLARE @i INT
SET @Age1=21
SET @Params = N' @Age as int'

SET @SQL='SELECT @i=CASE @Age
WHEN ''0''
THEN INSTPR
ELSE
INSPRM'+right( '00' + convert(varchar(2),@Age1),2) +
' END as [RATE] from dbo_T5658F'

EXEC sp_executesql @SQL, @params,@Age1=@Age1,N'@i INT OUTPUT',@i OUTPUT

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-10 : 05:12:12

have you try do a PRINT @SQL before the sp_executesql and inspect the query ?

KH
[spoiler]Time is always against us[/spoiler]

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-10 : 05:19:43

Yes it is working fine

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-10 : 05:21:12

SELECT @i=CASE @Age
WHEN '0'
THEN INSTPR
ELSE
INSPRM21 END as [RATE] from dbo_T5658F

This is wat i am getting in @SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-10 : 05:31:02


EXEC sp_executesql @SQL, N'@i INT OUTPUT, @Age INT', @i OUTPUT, @Age1

KH
[spoiler]Time is always against us[/spoiler]

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-10 : 05:47:28

Hi
i am getting the error

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'as'.

After executing

DECLARE @SQL nvarchar(4000)
declare @params nvarchar(1000)
declare @params1 nvarchar(1000)
DECLARE @RATE varchar (10)
DECLARE @Age INT
DECLARE @i INT

SET @Age=21
--SET @Params = N' @Age as int'

SET @SQL='SELECT @i=CASE @Age
WHEN ''0''
THEN INSTPR
ELSE
INSPRM'+right( '00' + convert(varchar(2),@Age),2) +
' END as [RATE] from dbo_T5658F'
PRINT @SQL
EXEC sp_executesql @SQL, N'@i INT OUTPUT, @Age INT', @i OUTPUT, @Age

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-10 : 05:50:30

remove the "as [RATE]" from the query

KH
[spoiler]Time is always against us[/spoiler]

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-10 : 05:53:02

Yup grt.. its working
thnx alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources