How to solve this error - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

How to solve this error

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-13 : 09:07:02

hi, following is my query

SET @MYQRY = 'select @inception_date = min(launch_date)
from t_schemes_master
where scheme_id in('+@SchemeIDs+') '

print @MYQRY
EXECUTE ( @MYQRY )

when i run this query in procedure it gives me following error

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@inception_date".

variable is already declared as datetime.

@SchemeIDs : varchar type parameter pass to procedure and

in t_schemes_master table's scheme_id is numeric type.

please giv me the solution.

Pankaj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 09:40:49

You don't need dynamic sql. Before you going into dynamic sql, read this http://www.sommarskog.se/dynamic_sql.html

I know why you are using Dynamic SQL, what you want can be easily done by a split function such as CSVTable


select  @inception_date = min(launch_date)
from    t_schemes_master
where   scheme_id in (select numval from CSVTABLE(@SchemeIDs))

use CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

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

kishore_pen
Starting Member

49 Posts

Posted - 2009-05-13 : 09:49:17

try like the following:

declare @Inception_Date datetime
, @SchemaIDs varchar (50)
, @SQLQuery varchar (1000)

set @Inception_Date = getdate ()
set @SchemaIDs = '1, 2, 3, 4'

select @SQLQuery = 'select @Inception_Date = min (launch_date) from t_schemes_master where scheme_id in (' + @SchemaIDs + ')'
print (@SQLQuery)

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-13 : 09:56:21

quote:
Originally posted by kishore_pen

try like the following:

declare @Inception_Date datetime
, @SchemaIDs varchar (50)
, @SQLQuery varchar (1000)

set @Inception_Date = getdate ()
set @SchemaIDs = '1, 2, 3, 4'

select @SQLQuery = 'select @Inception_Date = min (launch_date) from t_schemes_master where scheme_id in (' + @SchemaIDs + ')'
print (@SQLQuery)

yes but when u execute this stmt it will gives you above error

Pankaj

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-13 : 09:59:06

quote:
Originally posted by khtan

You don't need dynamic sql. Before you going into dynamic sql, read this http://www.sommarskog.se/dynamic_sql.html

I know why you are using Dynamic SQL, what you want can be easily done by a split function such as CSVTable

select @inception_date = min(launch_date)
from t_schemes_master
where scheme_id in (select numval from CSVTABLE(@SchemeIDs))

use CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

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

your solution is good but is there any other way ? because i want to avoid looping

( in oracle you can insert a value into variable eg.
@MYQRY = 'select min(launch_date)
from t_schemes_master
where scheme_id in('+@SchemeIDs+') '

EXECUTE @MYQRY into inception_date

i want same like .

Pankaj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 10:01:07

quote:
Originally posted by kishore_pen

try like the following:

declare @Inception_Date datetime
, @SchemaIDs varchar (50)
, @SQLQuery varchar (1000)

set @Inception_Date = getdate ()
set @SchemaIDs = '1, 2, 3, 4'

select @SQLQuery = 'select @Inception_Date = min (launch_date) from t_schemes_master where scheme_id in (' + @SchemaIDs + ')'
print (@SQLQuery)

if you have to use dynamic sql, in your case where you need to pass the parameter in / out, you can't use exec() but use sp_executesql. Read the link that i posted

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 10:02:58

i think you need only this no need of dynamic sql


select @inception_date = min(launch_date)
from t_schemes_master
where ','+@SchemeIDs+',' LIKE '%,'+ cast(scheme_id as varchar(100)) + ',%'

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-13 : 10:13:50

quote:
Originally posted by visakh16

i think you need only this no need of dynamic sql

select @inception_date = min(launch_date)
from t_schemes_master
where ','+@SchemeIDs+',' LIKE '%,'+ cast(scheme_id as varchar(100)) + ',%'

Thanks dear its working